Escaping is not done to put lovely backslashes in the database. Escaping is just to make sure the data gets in the database correctly. SQL uses quotes to delimit text, so they need to be escaped when the data contains them, and you of course need to escape the character you use to escape other things with.

With INSERT INTO foo VALUES ('bar\'baz') the string bar'baz is stored, without the backslash. It's like saying print "bar\"baz", which prints only bar"baz, again without the backslash itself.

Many, many PHP programmers are confused about escaping and quoting, because PHP quotes strings for you by default. That is, if you have the user input bar'baz, the actual string content will be bar\'baz, which is terribly wrong and confusing. (Blame PHP.) Some then read mysql can't handle single quotes, because they're used as delimiter and start escaping the quotes and backslashes, turning the string into bar\\\'baz, and with 'bar\\\'baz' in an SQL query, bar\'baz is stored in the database. This is where a lot of web programmers using PHP go wrong. They have escaped data in their database, which is very confusing, and a waste of space.

DBI has a nice escaping mechanism. Instead of escaping everything that could be dangerous, only that what is dangerous is escaped.

If you have the user input bar'baz in Perl, your string will have those 7 characters, and no disgusting automatic escaping. That means you will have to arrange for the backslash to be there yourself. You could do it the hard and potentially dangerous way, by using quotemeta or some s///-construct, but DBI has a database-specific method called quote, which is handled internally if you use placeholders. If you use that, the data is turned into bar\'baz for usage in an SQL query. In the query, there will be 'bar\'baz', so the data stored in the database is bar'baz. That also means you do not have to un-escape what you get out of the database. (Again PHP confuses people by having a function that un-escapes automatically. If with SQL everthing is done the way it's supposed to be done, you never have to unescape, because there are no escapes in the database!)

The following script will create a test2 table in the test database, and insert and immediately select the data that follows __DATA__ (through the magic DATA filehandle). It will then report whether the data has changed or not:

#!/usr/bin/perl -w
use strict;
use DBI;

my $dbh = DBI->connect('DBI:mysql:database=test', 'root', '');

$dbh->do('drop table if exists test2');
$dbh->do('create table test2 (xyzzy blob)');

undef $/;               # Slurp mode
my $before = <DATA>;

$dbh
    -> prepare('insert into test2 values( ? )')
    -> execute($before);

my $sth = $dbh
    -> prepare('select * from test2');

$sth
    -> execute;

my ($after) = $sth
    -> fetchrow_array;

print $before eq $after
    ? "Data has not changed\n"
    : "Data has changed!!\n";

__DATA__
\ = backslash
\\ = two backslashes
\\\ = three backslashes
' = a single quote
'' = two single quotes
\' = a backslash and a single quote
" = a double quote
\" = a backslash and a double quote
'"' = a double quote within 2 single quotes
[download]

(I experimented a little with style in here. When you read everything that is indented by one level, you read only the program flow. I haven't used this style before, but I kind of like it. Please let me know what you think about having the methods indented like this.)

HTH

++ vs lbh qrpbqrq guvf hfvat n ge va Crey :)
Nabgure bar vs lbh qvq fb jvgubhg ernqvat n znahny svefg.
-- vs lbh hfrq OFQ pnrfne ;)
    - Whreq

---

---

• Are you posting in the right place? Check out Where do I post X? to know for sure.
• Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:

  <code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>

• Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
• Want more info? How to link or How to display code and escape characters are good places to start.