|< Day Day Up >|
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:
quoteSmart( ) function in DB and PHP's built-in strtr( ) function. First, call quoteSmart( ) on the submitted value. 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.
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");
|< Day Day Up >|