Previous Section  < Day Day Up >  Next Section

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.

PHP has an unfortunate feature called "Magic Quotes." If this is turned on, submitted form data has quotes and backslashes escaped before it is put into $_GET or $_POST. If someone submits a form with Sauteed Pig's Stomach typed into the a text field named entree, then $_POST['entree'] is not Sauteed Pig's Stomach, but Sauteed Pig\'s Stomach instead. This is conceivably handy if all you're going to do with $_POST['entree'] is use it in a database query, but it is very inconvenient if you want to use $_POST['entree'] in other contexts (such as simply printing it) where the extra backslash is not welcome.

The "Magic Quotes" feature is enabled when the PHP configuration directive magic_quotes_gpc is turned on. For increased efficiency and more straightforward handling of submitted form parameters, turn magic_quotes_gpc off and use placeholders or a quoting function when you need to prepare external input for use in a database query.

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 (?)',


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'],


    Previous Section  < Day Day Up >  Next Section