Last updated January 6, 2013. Created by oadaeh on December 5, 2006.
Edited by rjacobs, Kartagis, HongPong, drumm. Log in to edit this page.

Input, whether it comes from visitors or servers, should be handled with care.

Consider the following example, modified from W. J. Gilmore's A Programmer's Introduction to PHP 4.0. It takes an argument supplied by the user and stuffs it into a query.

<?php
/** Example 1 - Insecure
  * SQL injection via $keyword
  */
$keyword = $_REQUEST['keyword'];
$query = "SELECT cust_id, cust_name, cust_email FROM customers WHERE category = '$keyword'";
$result = db_query($query);
?>

This enables the user to inject SQL statements into the query. What would happen if a visitor used the keyword '; DROP TABLE customers; --? The query would be:

SELECT cust_id, cust_name, cust_email FROM customers WHERE category = ''; DROP TABLE customers; --'

The Drupal philosophy - Escape or filter when appropriate

You may have found the following expert advice in text dealing with security problems: always validate input. The problem with a content management system like Drupal is that the input is not well defined. A lot of senseless advice is available too. One sometimes encounters recommendations to strip quotes from for example name fields (') to prevent SQL injection. Let's hope none of the users of such a system have the name O'Reilly.

Another frequently touted 'solution' is filtering on keywords. To prevent SQL injection one would prohibit SQL keywords as SELECT or DROP in content. Unfortunately, no user would be able to mention 'insert', 'select' and 'update', not uncommon in normal English. To make matters worse, Dutch users wouldn't be able to mention licorice (drop) anymore. Obviously not a very generic (or even sane) approach.

So what we really have to do is make sure that, regardless the data, its content can never be interpreted as SQL. To do this, we use the escaping functions provided by the database API. We do this escaping in the database layer and not directly after receiving input as it may not be the only escaping (or filtering) we have to do.

<?php
/** Example 2 - Insecure
  * SQL injection via $keyword
  * XSS via $keyword and (possibly) $row->cust_name, $row->cust_email
  */
$keyword = $_REQUEST['keyword'];
$query = "SELECT cust_id, cust_name, cust_email FROM customers WHERE category = '$keyword'";
$result = db_query($query);
echo
"<h2>$keyword</h2>":
while (
$row = db_fetch_object($result)) {
  echo
"$row->cust_name, $row->cust_email <br />";
}
?>

Example 2 — though small, riddled with vulnerabilities — demonstrates the issue with input filtering escaping; the $keyword has to be filtered or escaped twice for different purposes: Once to prevent SQL injection and once more to prevent cross site scripting (XSS) attacks. Applying both filters directly to the input $keyword would result in extraneous slashes showing up in output, or failed search attempted with &quot; in them.

The solution is to use an appropriate filter when needed. For example, just before sending plain text to the browser or mixing plain text with HTML, escape it with check_plain.

<?php
/** Example 2 - corrected
  */
$keyword = $_REQUEST['keyword'];
$query = "SELECT cust_id, cust_name, cust_email FROM customers WHERE category = '%s'";
$result = db_query($query, $keyword);
echo
'<h2>'. check_plain($keyword) .'</h2>':
while (
$row = db_fetch_object($result)) {
 
// Not very elegant, but making a point.
 
echo check_plain($row->cust_name) .','. check_plain($row->cust_email) .'<br />';
}
?>

Looking for support? Visit the Drupal.org forums, or join #drupal-support in IRC.