Backslash Character Escaping SQL Query

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View

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 line

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!


Re: Backslash Character Escaping SQL Query

Regan wrote:
Quoted text here. Click to load it

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

$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.

Bill K.

Re: Backslash Character Escaping SQL Query

Quoted text here. Click to load it

Escape your data.

Quoted text here. Click to load it

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:

Quoted text here. Click to load it

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:

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
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)");

Hope this helps,


Site Timeline