10.10. Escaping Quotes10.10.2. SolutionWrite 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. DiscussionThe 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)); 10.10.4. See AlsoDocumentation on DB::quote( ) at http://pear.php.net/manual/en/core.db.quote.php and magic quotes at http://www.php.net/manual/en/ref.info.php#ini.magic-quotes-gpc. Copyright © 2003 O'Reilly & Associates. All rights reserved. |
|