home | O'Reilly's CD bookshelfs | FreeBSD | Linux | Cisco | Cisco Exam  


Book HomePHP CookbookSearch this book

10.10. Escaping Quotes

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));


Library Navigation Links

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