Understanding Quoting and Placeholders - PHP

Frequently, database queries contain user input and other dynamic values. Before putting these data into queries, however, you need to escape any characters that have special meaning to your database. These characters are as follows:

• ': The single quote character delimits string literals in queries. This helps the database tell the difference between string values and field and table names.

• %: The percent character is an SQL wildcard that means “match any number of characters,” like * in the shell or in a regular expression.

• _: The underscore character is an SQL wildcard that means “match one character,” like ? in the shell or . in a regular expression.

• \: The backslash character is used by some databases as an escape character, so it itself has to be escaped.

The proper way to escape these characters in many databases is to put a backslash before them: SELECT * FROM songs WHERE title LIKE 'Don't Be Cruel' becomes SELECT * FROM songs WHERE title LIKE 'Don\'t Be Cruel'. The single quote in Don't is escaped to tell the database the song title is the entire string Don't Be Cruel, not just Don. Some databases escape the single quote character by turning it into two single quote characters: SELECT * FROM songs WHERE title LIKE 'Don''t Be Cruel'.

PEAR DB makes it easy for you to escape single quotes by providing query placeholders. These are special characters in the query that are replaced by actual data when the query is sent to the database. When the data replaces the placeholder in the query, it is appropriately quoted. To use a placeholder, put a ? character where you want the data to end up and pass a second argument to query() that contains an array holding the data:

$sth = $dbh->query('SELECT * FROM ice_cream WHERE flavor
       LIKE ?', array($_REQUEST['flavor']));

The query method replaces the ? with the value of $_REQUEST['flavor'], surrounded by single quotes. Any single quotes in $_REQUEST['flavor'] are escaped in a method appropriate for the database you’re using. For example, if the value of $_REQUEST['flavor'] is Straw's Berry, then the query that’s sent to MySQL is this:

SELECT * FROM ice_cream WHERE flavor LIKE 'Straw\'s Berry'

The query that’s sent to PostgreSQL is this:

SELECT * FROM ice_cream WHERE flavor LIKE 'Straw''s Berry'

The ? placeholder only quotes strings. Integers and doubles are put into the query as is, and nulls are replaced with NULL (without quotes).

The ! placeholder is replaced by a value without any quoting or modification. This is useful for interpolating table names or column names. In this example, the table name to work with comes from $_REQUEST['dessert'], and the value to insert comes from $_REQUEST['flavor']:

$desserts = array('ice_cream' => 1, 'frozen_yogurt' => 1, 'sorbet' => 1);
if ($desserts[$_REQUEST['dessert']]) {
     $dbh->query('INSERT INTO ! (flavor) VALUES (?)',
          array($_REQUEST['dessert'], $_REQUEST['flavor']));
} else {
     print "No such dessert.";
}

Checking to see whether the specified table is defined in the $desserts array is necessary to prevent a malicious user from inserting data into an arbitrary table in your database. If you use the ! placeholder to specify table or field names, always check to see whether the value that is going to be substituted for the placeholder is an acceptable one before running the query.

In addition to ? and !, there is a third placeholder: &. This is replaced with the contents of a file. Include the name of the file in the array of values passed to query() as a second argument:

$dbh->query("INSERT INTO dessert_pictures (flavor,image) 
     VALUES (?,&)", array('Rum Raisin','rum-raisin.jpeg'));

To quote values outside of query(), use the DB::quote() method. When passed a string, it returns the string quoted with single quotes escaped. It returns integers and doubles unmodified and returns the string NULL when passed a null value. For example:

$flavor = $dbh->quote($_REQUEST['flavor']);
$dbh->query("SELECT * FROM ice_cream WHERE flavor LIKE
             $flavor");

Placeholder quoting and the quote() method make strings safe for inclusion in queries. They don’t escape the SQL wildcard characters % and _. You have to escape those characters yourself with str_replace():

$safe_for_select = str_replace(array('%','_'),
                   array('\%','\_'),$_REQUEST['flavor']);

Replacing % with \% and _ with \_ tells the database to treat those characters as a literal percent sign and underscore and not as SQL wildcards. If the value of $_REQUEST['flavor'] is Chocolate%, $dbh->query("SELECT * FROM ice_cream WHERE flavor LIKE '$safe_for_select'") only finds rows where flavor exactly matches the string Chocolate%, not all rows where flavor begins with Chocolate.

Using str_replace() to escape SQL wildcards can present a problem when used with query placeholders. The string quoting must happen before the wildcard escaping. Otherwise, the backslashes that the wildcard escaping inserts are themselves escaped by the string quoting. A single % character becomes \% by the wildcard escaping. The \% is turned into '\\%' by string quoting. The expression '\\%' matches any string that begins with a literal backslash character because \\ is interpreted by the database as a literal backslash and then % is interpreted as “match any number of characters.” If the string quoting happens first, % is turned into '%', and then the wildcard escaping makes it '\%'. This matches only a one-character string containing a percent sign.

What does all this mean in practice? If you want to protect against errant SQL wildcards in user input, use quote() to quote strings before escaping wild-cards. Put these quoted and escaped values into queries explicitly instead of using placeholders:

$flavor = str_replace(array('%','_'),array('\%','\_'), 
          $dbh->quote($_REQUEST['flavor']));
$sth = $dbh->query("SELECT * FROM ice_cream WHERE flavor 
                    LIKE $flavor");

PHP has some configuration settings that affect string quoting. These settings don’t change PEAR DB’s behavior, so improper configuration may result in values getting quoted twice.

If the magic_quotes_gpc configuration setting is on, single quotes and backslashes in incoming GET, POST, and cookie data are automatically backslash-escaped. If the magic_quotes_runtime configuration setting is on, the same quoting happens automatically for data read from a file or database. If magic_quotes_sybase is on, then magic_quotes_gpc and magic_quotes_runtime escape a single quote with another single quote and don’t escape backslashes at all.

Using query placeholder quoting with GET, POST, or cookie data when magic quotes_gpc is on or with data read from a file or database when magic_quotes runtime is on results in data being quoted twice. If the submitted POST form variable flavor contains Straw's Berry and magic_quotes_gpc is on, then the value of $_REQUEST['flavor'] is Straw\'s Berry. A problem results if that value is substituted for the placeholder in this query:

$sth = $dbh->query('SELECT * FROM ice_cream WHERE flavor 
       LIKE ?', array($_REQUEST['flavor']));

The query sent to the database is SELECT * FROM ice_cream WHERE flavor LIKE 'Straw\\\'s Berry'. Because the backslash inserted by magic_quotes_gpc is itself escaped by the placeholder substitution, this query doesn’t find the appropriate rows.

The easiest way to avoid this double escaping is to turn off magic_quotes_gpc and magic_quotes_runtime in your php.ini or Web server configuration file. If you can’t do that, use stripslashes() to remove the escaping that was added:

$_REQUEST['flavor'] = stripslashes($_REQUEST['flavor']);
$sth = $dbh->query('SELECT * FROM ice_cream WHERE flavor
         LIKE ?', array($_REQUEST['flavor']));

To make your code more portable, call stripslashes() only when necessary. The get_magic_quotes_gpc() function returns 1 if magic_quotes_gpc is on. Use get magic_quotes_gpc() to determine whether to call stripslashes():

if (get_magic_quotes_gpc()) {
$_REQUEST['flavor'] = stripslashes($_REQUEST['flavor']);
}
$sth = $dbh->query('SELECT * FROM ice_cream WHERE flavor
         LIKE ?', array($_REQUEST['flavor']));

 

Note: The stripslashes() function turns \\ into \ and \' into ' It doesn't unquote Sybase-style quoted strings. To do that, use str_replace() : $str = str_replace("''","'",$str); .