|< Day Day Up >|
7.5 Inserting Form Data Safely
As Section 6.4.6 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 their new dish. The call to query( ) in Example 7-26 inserts the new dish into the dishes table but is vulnerable to an SQL injection attack.
Example 7-26. Unsafe insertion of form data
$db->query("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 that 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 query( ). On those databases, the dishes table is 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 is able to inject arbitrary SQL statements into your database program. To prevent this, you need to escape special characters (most importantly, the apostrophe) in SQL queries. PEAR DB provides a helpful feature called placeholders that makes this a snap.
To use a placeholder in a query, put a ? in the query in each place where you want a value to go. Then, pass query( ) a second argument梐n array of values to be substituted for the placeholders. The values are appropriately quoted before they are put into the query, protecting you from any SQL injection attacks. Example 7-27 shows the safe version of the query from Example 7-26.
Example 7-27. Safe insertion of form data
$db->query('INSERT INTO dishes (dish_name) VALUES (?)', array($_POST['new_dish_name']));
You don't need to put quotes around the placeholder in the query. DB 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 7-28 shows a query with three placeholders.
Example 7-28. Using multiple placeholders
$db->query('INSERT INTO dishes (dish_name,price,is_spicy) VALUES (?,?,?)', array($_POST['new_dish_name'], $_POST['new_price'], $_POST['is_spicy']));
|< Day Day Up >|