PHP Database Access Escaping Quotes

PHP Database Access

Escaping Quotes

Problem

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

Solution

Write all your queries with placeholders so that prepare() and execute() can escape strings for you. 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.

Example  Manual quoting

         $safe = $db->quote($_GET['searchTerm']);

         $safe = strtr($safe,array('_' => '\_', '%' => '\%'));

         $st = $db->query("SELECT * FROM zodiac WHERE planet LIKE $safe");

Discussion

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 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 isthe 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  Checking for magic quotes

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

Note : 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.