Escaping Quotes (Perl Cookbook, 2nd Edition)

## 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

Copyright © 2003 O'Reilly & Associates. All rights reserved.