Recipe 14.10 Escaping Quotes

14.10.1 Problem

You want to put Perl values into queries as literal strings, but you're not sure how your database wants strings to be quoted.

14.10.2 Solution

Use the database handle's quote method:

$quoted = $dbh->quote($unquoted);

This $quoted value is now suitable for interpolation into queries:

$sth->prepare("SELECT id,login FROM People WHERE name = $quoted");

Or simply use placeholders in your query and DBI automatically quotes strings for you:

$sth->prepare("SELECT id,login FROM People WHERE name = ?");
$sth->execute($unquoted);

14.10.3 Discussion

Each database has its own quoting idiosyncrasies, so leave the quoting to the quote method or placeholders rather than trying to roll your own quoting function. Not only is hardcoding quotes into your SQL non-portable, it doesn't take into account the possibility that the strings you're interpolating might have quotes in them. For example, take this:

$sth = $dbh->prepare(qq{SELECT id,login FROM People WHERE name="$name"});

If $name is Jon "maddog" Orwant, then you are effectively preparing this query, which is invalid SQL:

SELECT id,login FROM People WHERE name="Jon "maddog" Orwant"

The only strange quoting behavior from quote is this: because the DBI represents NULL values as undef, if you pass undef to quote, it returns NULL without quotes.

14.10.4 See Also

The documentation with the DBI module from CPAN; http://dbi.perl.org; Programming the Perl DBI