10.9.1. ProblemYou need to make text or binary data safe for queries. 10.9.2. SolutionWrite all your queries with placeholders so that prepare( ) and execute( ) can escape strings for you. Recipe 10.7 details the different ways to use placeholders. 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. Manual quoting
10.9.3. DiscussionThe 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['searchTerm'] 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 quotessupplied backslashes before you use a query with placeholders or call PDO::quote( ). Example 10-25 shows this check. Checking for magic quotes
If you have any control over your server, turn magic quotes off and make your life a lot easier. However, if you're trying to write maximally portable code that could run in an environment you don't control, you need to look out for this problem. 10.9.4. See AlsoDocumentation on PDO::quote( ) at http://www.php.net/PDO::quote and on magic quotes at http://www.php.net/manual/en/ref.info.php#ini.magic-quotes-gpc. |