Backslash Character Escaping SQL Query

Hello,

I have done tons of searching on this topic but have yet to find
something relavent to the problem I am experiencing so I am hoping
someone can help me.

The problem I am having is that using Perl to insert some rows into a
MySQL database, some entries that are being inserted include
backslashes. For example, here is one error I am receiving:

DBD::mysql::st execute failed: You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the
right syntax to use near ''A:\')' at line 1 at snmp_collector.pl line
97.

I am wondering how to deal with the backslashes, which I don't always
know if and when they will appear. Is there some type of replace
statement I can run on the SQL query before it is sent to mysql?
Thanks for your help!

-Regan

Re: Backslash Character Escaping SQL Query

Regan wrote:
> I am wondering how to deal with the backslashes, which I don't always
> know if and when they will appear. Is there some type of replace
> statement I can run on the SQL query before it is sent to mysql?

The most general purpose solution I have found is to use parameterized
queries.

$sth = $dbh->prepare("INSERT INTO mytable VALUES (?, ?, ?)");
$sth->execute('123', 'foo', $scalar1);

Where $scalar1 contains the string you want to insert, including special
characters. It's not actually parsed at the time the INSERT statement
is parsed; the SQL has already been parsed into an internal
representation. So the conflict between the special characters in your
string and SQL syntax never causes a problem.

Regards,
Bill K.

Re: Backslash Character Escaping SQL Query

>I have done tons of searching on this topic but have yet to find
>something relavent to the problem I am experiencing so I am hoping
>someone can help me.
>
>The problem I am having is that using Perl to insert some rows into a
>MySQL database, some entries that are being inserted include
>backslashes. For example, here is one error I am receiving:

Escape your data.

>DBD::mysql::st execute failed: You have an error in your SQL syntax;
>check the manual that corresponds to your MySQL server version for the
>right syntax to use near ''A:\')' at line 1 at snmp_collector.pl line
>97.
>
>I am wondering how to deal with the backslashes, which I don't always
>know if and when they will appear. Is there some type of replace
>statement I can run on the SQL query before it is sent to mysql?

No. You run the replacement (e.g. mysql_escape_string()) on the
*DATA* before putting it in the SQL statement. After you put it
in the SQL statement it's very difficult to tell where the string
ends and the SQL continues, and there might be more than one
legal possibility.

"SELECT * FROM disks WHERE drive = 'A:\\'"

Another possibility is parameter substitution, using ? in the query.

Gordon L. Burditt

Re: Backslash Character Escaping SQL Query

Regan wrote:

> I have done tons of searching on this topic but have yet to find
> something relavent to the problem I am experiencing so I am hoping
> someone can help me.
>
> The problem I am having is that using Perl to insert some rows into a
> MySQL database, some entries that are being inserted include
> backslashes. For example, here is one error I am receiving:
>
> DBD::mysql::st execute failed: You have an error in your SQL syntax;
> check the manual that corresponds to your MySQL server version for the
> right syntax to use near ''A:\')' at line 1 at snmp_collector.pl line
> 97.
>
> I am wondering how to deal with the backslashes, which I don't always
> know if and when they will appear. Is there some type of replace
> statement I can run on the SQL query before it is sent to mysql?

I think your problem can be solved in two steps:

(1) Make sure your Perl variables handle backslashes exactly the way
you want
(2) Use DBI's built-in function to make sure you pass the exact string

Here is an example:

#!/usr/bin/perl
use strict;
use warnings;
use DBI;
# AFAIK, following here-doc is the only notation that
# guarantees no interpolation:
my $data = <<'EOS';
Don't call me "James" \$@% \\A \\\B
EOS
chop $data;
my $db = DBI->connect("DBI:mysql:DBname:localhost",'DBuser','Dbpass') ;
my $quoted = $db->quote($data);
my $query = $db->prepare("INSERT INTO mytable VALUES ('',$quoted)");
$query->execute;
$query->finish;
$db->disconnect;

Hope this helps,

--
Bart