You need to make text or binary data safe for queries.
Write all your queries with placeholders so that prepare() and execute() can escape strings for you.
If you need to apply escaping yourself, use the PDO::quote() method. The rare circumstance you might need to do this could be if you want to escape SQL wildcards coming from user input, as shown in Example 10-24.
Example 10-24. Manual quoting
|
1 2 3 |
$safe = $db->quote($_GET['searchTerm']); $safe = strtr($safe,array('_' => '\_', '%' => '\%')); $st = $db->query("SELECT * FROM zodiac WHERE planet LIKE $safe"); |
The PDO::quote() method makes sure that text or binary data is appropriately quoted, but you may also need to quote the SQL wildcard characters % and _ to ensure that SELECT statements using the LIKE operator return the right results. If $_GET[‘search Term’] is set to Melm% and Example 10-24 doesn’t call strtr(), its query returns rows with planet set to Melmac, Melmacko, Melmacedonia, or anything else beginning with Melm.
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.
strtr() must be called after PDO::quote(). Otherwise, PDO::quote() would backslash-escape the backslashes strtr() adds. With PDO::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 PDO::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.”
This is the same thing that would happen if we escaped the SQL wildcards and then used the resulting value as a bound parameter.
Quoting of placeholder values happens even if magic_quotes_gpc or magic_quotes_runtime is turned on. Similarly, if you call PDO::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 PDO::quote(). Example 10-25 shows this check.
Example 10-25. Checking for magic quotes
|
1 2 3 4 5 6 7 8 |
// The behavior of magic_quotes_sybase can also affect things if (get_magic_quotes_gpc() && (! ini_get('magic_quotes_sybase'))) { $fruit = stripslashes($_GET['fruit']); } else { $fruit = $_GET['fruit']; } $st = $db->prepare('UPDATE orchard SET trees = trees - 1 WHERE fruit = ?'); $st->execute(array($fruit)); |
