DBI quote alternative

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Leif Wessman - 29 Aug 2004 08:31 GMT

Is there an alternative to the DBI quote function?

I don't have any database access at runtime, so I'm unable to create a
new DBI object.

However, I'm creating INSERT statements that should (eventually) be
used in a MySQL database. I'm writing them to a file for later use.
Thanks for any input!

Leif

Reply to this Message

Brian McCauley - 29 Aug 2004 12:43 GMT

> Is there an alternative to the DBI quote function?
>
> I don't have any database access at runtime, so I'm unable to create a
> new DBI object.

Have you looked at the source of the default quote function?

If you are only considering strings then all you need do is:

sub my_quote {
my $str = shift;
$str =~ s/'/''/g;
return "'$str'";
}

Reply to this Message

Bill Karwin - 29 Aug 2004 21:20 GMT

> sub my_quote {
> my $str = shift;
> $str =~ s/'/''/g;
> return "'$str'";
> }

MySQL also permits backslash to escape single-quotes.
MySQL has a function QUOTE() that does a similar treatment to string you
fetch from the database (for instance, if you then want to use an
escaped string for another SQL operation), and their QUOTE function also
escapes ASCII null and control-Z. So I recommend if you are preparing
strings for MySQL that you match this functionality.

Bill K.

Reply to this Message

Brian McCauley - 31 Aug 2004 09:55 GMT

>> sub my_quote {
>> my $str = shift;
[quoted text clipped - 8 lines]
> escapes ASCII null and control-Z. So I recommend if you are preparing
> strings for MySQL that you match this functionality.

Not that this is on-topic or anything like that but doesn't treating a
backslash as anything other than a literal backslash violate proposed
SQL standards? (WG3:HBA-003 H2-2003-305) That document is a year old so
perhaps I'm missing something subsequent.

Reply to this Message

Bill Karwin - 31 Aug 2004 20:17 GMT

> Not that this is on-topic or anything like that but doesn't treating a
> backslash as anything other than a literal backslash violate proposed
> SQL standards? (WG3:HBA-003 H2-2003-305) That document is a year old so
> perhaps I'm missing something subsequent.

I agree, Brian. MySQL (and almost every other RDBMS product out there)
implements some features that don't match the ANSI/ISO standard exactly.

They do support '' for literal quotes, but they also support backslash
for that and other special characters (it would seem weird to escape a
Ctrl-Z by making it Ctrl-Z Ctrl-Z).

Bill

Reply to this Message

Leif Wessman - 31 Aug 2004 09:28 GMT

> > Is there an alternative to the DBI quote function?
> >
[quoted text clipped - 10 lines]
> return "'$str'";
> }

How can I rewrite the function to include newlines?

Leif

Reply to this Message

Webmaster Forum / Perl / Modules / August 2004

DBI quote alternative