7.10 Retrieving Form Data Safely

It's possible to use placeholders with SELECT statements just as you do with INSERT, UPDATE, or DELETE statements. The getAll( ), getRow( ), and getOne( ) functions each accept a second argument of an array of values that are substituted for placeholders in a query.

However, when you use submitted form data or other external input in the WHERE clause of a SELECT, UPDATE, or DELETE statement, you must take extra care to ensure that any SQL wildcards are appropriately escaped. Consider a search form with a text element called dish_search into which the user can type a name of a dish he's looking for. The call to getAll( ) in Example 7-48 uses placeholders guard against confounding single-quotes in the submitted value.

Example 7-48. Using a placeholder in a SELECT statement

$matches = $db->getAll('SELECT dish_name, price FROM dishes
                        WHERE dish_name LIKE ?',
                       array($_POST['dish_search']));

Whether dish_search is Fried Bean Curd or General Tso's Chicken, the placeholder interpolates the value into the query appropriately. However, what if dish_search is %chicken%? Then, the query becomes SELECT dish_name, price FROM dishes WHERE dish_name LIKE '%chicken%'. This matches all rows that contain the string chicken, not just rows in which dish_name is exactly %chicken%.

To prevent SQL wildcards in form data from taking effect in queries, you must forgo the comfort and ease of the placeholder and rely on two other functions:

SQL Lesson: Wildcards Wildcards are useful for matching text inexactly, such as finding strings that end with .edu or that contain @. SQL has two wildcards. The underscore (_) matches one character and the percent sign (%) matches any number of characters (including zero characters). The wildcards are active inside strings used with the LIKE operator in a WHERE clause. Example 7-49 shows two SELECT queries that use LIKE and wildcards. Example 7-49. Using wildcards with SELECT ; Retrieve all rows in which dish name begins with D SELECT * FROM dishes WHERE dish_name LIKE 'D%' ; Retrieve rows in which dish name is Fried Cod, Fried Bod, ; Fried Nod, and so on. SELECT * FROM dishes WHERE dish_name LIKE 'Fried _od' Wildcards are active in the WHERE clauses of UPDATE and DELETE statements, too. The query in Example 7-50 doubles the price of all dishes that have chili in their names. Example 7-50. Using wildcards with UPDATE UPDATE dishes SET price = price * 2 WHERE dish_name LIKE '%chili%' The query in Example 7-51 deletes all rows whose dish_name ends with Shrimp. Example 7-51. Using wildcards with DELETE DELETE FROM dishes WHERE dish_name LIKE '%Shrimp' To match against a literal % or _ when using the LIKE operator, put a backslash before the % or _. The query in Example 7-52 finds all rows whose dish_name contains 50% off. Example 7-52. Escaping wildcards SELECT * FROM dishes WHERE dish_name LIKE '%50\% off%' Without the backslash, the query in Example 7-52 would match rows whose dish_name contains 50 and then has a space and off somewhere later in the name, such as Spicy 50 shrimp with shells off salad or Famous 500 offer duck.

quoteSmart( ) function in DB and PHP's built-in strtr( ) function. First, call quoteSmart( ) on the submitted value.^[3] This does the same quoting operation that a the placeholder does. For example, it turns General Tso's Chicken into 'General Tso\'s Chicken'. The next step is to use strtr( ) to backslash-escape the SQL wildcards % and _. The quoted and wildcard-escaped value can then be used safely in a query.

^[3] The quoteSmart( ) function was introduced in DB 1.6.0. If you are using an earlier version of DB and get an error when trying to use quoteSmart( ), use quote( ) instead.

Example 7-53 shows how to use quoteSmart( ) and strtr( ) to make a submitted value safe for a WHERE clause.

Example 7-53. Not using a placeholder in a SELECT statement

// First, do normal quoting of the value
$dish = $db->quoteSmart($_POST['dish_search']);
// Then, put backslashes before underscores and percent signs
$dish = strtr($dish, array('_' => '\_', '%' => '\%'));
// Now, $dish is sanitized and can be interpolated right into the query
$matches = $db->getAll("SELECT dish_name, price FROM dishes
                        WHERE dish_name LIKE $dish");

You can't use a placeholder in this situation because the escaping of the SQL wildcards has to happen after the regular quoting. The regular quoting puts a backslash before single quotes, but also before backslashes. If strtr( ) processes the string first, a submitted value such as %chicken% becomes \%chicken\%. Then, the quoting (whether by quoteSmart( ) or the placeholder processing) turns \%chicken\% into '\\%chicken\\%'. This is interpreted by the database to mean a literal backslash, followed by the "match any characters" wildcard, followed by chicken, followed by another literal backslash, followed by another "match any characters" wildcard. However, if quoteSmart( ) goes first, %chicken% is turned into '%chicken%'. Then, strtr( ) turns it into '\%chicken\%'. This is interpreted by the database as a literal percent sign, followed by chicken, followed by another percent sign, which is what the user entered.

Not quoting wildcard characters has an even more drastic effect in the WHERE clause of an UPDATE or DELETE statement. Example 7-54 shows a query incorrectly using placeholders to allow a user-entered value to control which dishes have their prices set to $1.

Example 7-54. Incorrect use of placeholders in an UPDATE statement

$db->query('UPDATE dishes SET price = 1 WHERE dish_name LIKE ?',
           array($_POST['dish_name']));

If the submitted value for dish_name in Example 7-54 is Fried Bean Curd, then the query works as expected: the price of that dish only is set to 1. But if $_POST['dish_name'] is %, then all dishes have their price set to 1! The quoteSmart( ) and strtr( ) technique prevents this problem. The right way to do the update is in Example 7-55.

Example 7-55. Correct use of quoteSmart( ) and strtr( ) with an UPDATE statement

// First, do normal quoting of the value
$dish = $db->quoteSmart($_POST['dish_name']);
// Then, put backslashes before underscores and percent signs
$dish = strtr($dish, array('_' => '\_', '%' => '\%'));
// Now, $dish is sanitized and can be interpolated right into the query
$db->query("UPDATE dishes SET price = 1 WHERE dish_name LIKE $dish");