PHP : Database Access - [10.10] Escaping Quotes

10.10.1 Problem

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

10.10.2 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'.

10.10.3 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);
$planet = strtr($planet,array('_' => '\_', '%' => '\%'));
$dbh->query("SELECT * FROM zodiac WHERE planet LIKE $planet");

strtr( ) must be called after DB::quote( ). Otherwise, DB::quote( ) would backslash-escape the backslashes strtr( ) adds. With DB::quote( ) first, Melm_ is turned into Melm\_, which is interpreted by the database to mean "the string M e l m followed by a literal underscore character." With DB::quote( ) after strtr( ), Melm_ is turned into Melm\\_, which is interpreted by the database to mean "the string Melm followed by a literal backslash character, followed by the underscore wildcard."

A quote method is defined in the DB base class, but some of the database-specific subclasses override that method to provide appropriate quoting behavior for the particular database in use. By using DB::quote( ) instead of replacing specific characters, your program is more portable.

Quoting of placeholder values happens even if magic_quotes_gpc or magic_quotes_runtime is turned on. Similarly, if you call DB:quote( ) on a value when magic quotes are active, the value gets quoted anyway. For maximum portability, remove the magic quotes-supplied backslashes before you use a query with placeholders or call DB::quote( ):

$fruit = ini_get('magic_quotes_gpc') ? stripslashes($_REQUEST['fruit']) : 
    $_REQUEST['fruit'];

$dbh->query('UPDATE orchard SET trees = trees - 1 WHERE fruit LIKE ?',
            array($fruit));