Backslash Character Escaping SQL Query

[Regan]

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

[Bill Karwin]

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("INSER T 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.

[Gordon Burditt]

>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_st ring()) 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

[Bart Van der Donck]

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:m ysql:DBname:loc alhost",'DBuser ','Dbpass');
my $quoted = $db->quote($data) ;
my $query = $db->prepare("INSER T INTO mytable VALUES ('',$quoted)");
$query->execute;
$query->finish;
$db->disconnect;

Hope this helps,

--
Bart