It’s possible to use placeholders with SELECT statements just as you do with INSERT, UPDATE, or DELETE statements. Instead of using query() directly, use prepare() and execute(), but give prepare() a SELECT statement.
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 the name of a dish she’s looking for. The call to execute() in Example 8-45 uses placeholders to guard against confounding single quotes in the submitted value.
Example 8-45. Using a placeholder in a SELECT statement
$stmt = $db->prepare(‘SELECT dish_name, price FROM dishes
WHERE dish_name LIKE ?’);
$stmt->execute(array($_POST[‘dish_search’]));
while ($row = $stmt->fetch()) {
// … do something with $row …
}
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%.
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 8-46 shows two SELECT queries that use LIKE and wildcards.
Example 8-46. 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 8-47 doubles the prices of all dishes that have chili in their names.
Example 8-47. Using wildcards with UPDATE
UPDATE dishes SET price = price * 2 WHERE dish_name LIKE ‘%chili%’
The query in Example 8-48 deletes all rows whose dish_name ends with Shrimp. Example 8-48. 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 8-49 finds all rows whose dish_name contains 50% off.
Example 8-49. Escaping wildcards
SELECT * FROM dishes WHERE dish_name LIKE ‘%50\% off%’
Without the backslash, the query in Example 8-49 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.
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: quote() in PDO and PHP’s built-in strtr() function. First, call quote() on the submitted value.
This does the same quoting operation that 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 8-50 shows how to use quote() and strtr() to make a submitted value safe for a WHERE clause.
Example 8-50. Not using a placeholder in a SELECT statement
// First, do normal quoting of the value
$dish = $db->quote($_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
$stmt = $db->query(“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 quote() 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 quote() 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 8-51 shows a query incorrectly using placeholders to allow a user-entered value to control which dishes have their prices set to $1.
Example 8-51. Incorrect use of placeholders in an UPDATE statement
$stmt = $db->prepare(‘UPDATE dishes SET price = 1 WHERE dish_name LIKE ?’);
$stmt->execute(array($_POST[‘dish_name’]));
If the submitted value for dish_name in Example 8-51 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 quote() and strtr() technique prevents this problem. The right way to do the update is in Example 8-52.
Example 8-52. Correct use of quote() and strtr() with an UPDATE statement
// First, do normal quoting of the value
$dish = $db->quote($_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->exec(“UPDATE dishes SET price = 1 WHERE dish_name LIKE $dish”);
Source: Sklar David (2016), Learning PHP: A Gentle Introduction to the Web’s Most Popular Language, O’Reilly Media; 1st edition.