PHP Cookbook

10.9. Escaping Quotes

Problem

You need to make text or binary data safe for queries.

Solution

Write all your queries with placeholders and pass values to fill the placeholders in an array:

$sth = $dbh->query('UPDATE zodiac SET planet = ? WHERE id = 2',
                   array('Melmac'));

$rows = $dbh->getAll('SELECT * FROM zodiac WHERE planet LIKE ?',
                     array('M%'));

You can also use PEAR DB’s DB::quote( ) to escape special characters and make sure strings are appropriately marked (usually with single quotes around them):

$planet = $dbh->quote($planet);
$dbh->query("UPDATE zodiac SET planet = $planet WHERE id = 2");

If $planet is Melmac, $dbh->quote($planet) if you are using MySQL returns 'Melmac'. If $planet is Ork's Moon, $dbh->quote($planet) returns 'Ork\'s Moon‘.

Discussion

The DB::quote( ) method makes sure that text or binary data is appropriately quoted, but you also need to quote the SQL wildcard characters % and _ to ensure that SELECT statements return the right results. If $planet is set to Melm%, this query returns rows with planet set to Melmac, Melmacko, Melmacedonia, or anything else beginning with Melm:

$planet = $dbh->quote($planet);
$dbh->query("SELECT * FROM zodiac WHERE planet LIKE $planet");

Because % is the SQL wildcard meaning “match any number of characters” (like * in shell globbing) and _ is the SQL wildcard meaning “match one character” (like ? in shell globbing), those need to be backslash-escaped as well. Use strtr( ) to escape them:

$planet = $dbh->quote($planet); ...