As “HTML and JavaScript” on page 138 explained, printing unsanitized form data can leave you and your users vulnerable to a cross-site scripting attack. Using unsanitized form data in SQL queries can cause a similar problem, called an “SQL injection attack.” Consider a form that lets a user suggest a new dish. The form contains a text element called new_dish_name into which the user can type the name of a new dish. The call to exec() in Example 8-25 inserts the new dish into the dishes table, but is vulnerable to an SQL injection attack.
Example 8-25. Unsafe insertion of form data
$db->exec(“INSERT INTO dishes (dish_name)
VALUES (‘$_POST[new_dish_name]’)”);
If the submitted value for new_dish_name is reasonable, such as Fried Bean Curd, then the query succeeds. PHP’s regular double-quoted string interpolation rules make the query INSERT INTO dishes (dish_name) VALUES (‘Fried Bean Curd’), which is valid and respectable. A query with an apostrophe in it causes a problem, though. If the submitted value for new_dish_name is General Tso’s Chicken, then the query becomes INSERT INTO dishes (dish_name) VALUES (‘General Tso’s Chicken’). This makes the database program confused. It thinks that the apostrophe between Tso and s ends the string, so the s Chicken’ after the second single quote is an unwanted syntax error.
What’s worse, a user who really wants to cause problems can type in specially constructed input to wreak havoc. Consider this unappetizing input:
x’); DELETE FROM dishes; INSERT INTO dishes (dish_name) VALUES (‘y.
When that gets interpolated, the query becomes:
INSERT INTO DISHES (dish_name) VALUES (‘x’);
DELETE FROM dishes; INSERT INTO dishes (dish_name) VALUES (‘y’)
Some databases let you pass multiple queries separated by semicolons in one call of exec(). On those databases, the previous input will cause the dishes table to be demolished: a dish named x is inserted, all dishes are deleted, and a dish named y is inserted.
By submitting a carefully built form input value, a malicious user can inject arbitrary SQL statements into your database program. To prevent this, you need to escape special characters (most importantly, the apostrophe) in SQL queries. PDO provides a helpful feature called prepared statements that makes this a snap.
With prepared statements, you separate your query execution into two steps. First, you give PDO’s prepare() method a version of your query with a ? in the SQL in each place you want a value to go. This method returns a PDOStatement object. Then, you call execute() on your PDOStatement object, passing it an array of values to be substituted for the placeholding ? characters. The values are appropriately quoted before they are put into the query, protecting you from SQL injection attacks. Example 8-26 shows the safe version of the query from Example 8-25.
Example 8-26. Safe insertion of form data
$stmt = $db->prepare(‘INSERT INTO dishes (dish_name) VALUES (?)’);
$stmt->execute(array($_POST[‘new_dish_name’]));
You don’t need to put quotes around the placeholder in the query. PDO takes care of that for you, too. If you want to use multiple values in a query, put multiple placeholders in the query and in the value array. Example 8-27 shows a query with three placeholders.
Example 8-27. Using multiple placeholders
$stmt = $db->prepare(‘INSERT INTO dishes (dish_name,price,is_spicy) VALUES (?,?,?)’);
$stmt->execute(array($_POST[‘new_dish_name’], $_POST[‘new_price’],
Source: Sklar David (2016), Learning PHP: A Gentle Introduction to the Web’s Most Popular Language, O’Reilly Media; 1st edition.