Last updated December 4, 2013. Created on December 5, 2006.
Edited by snufkin, sreynen, drupalshrek, Crell. Log in to edit this page.

Drupal provides several functions to send queries to the database. The canonical form is db_query. Always use functions provided by Drupal to access the database to guard against SQL injections attacks. However, just using the functions is not enough as the following example illustrates:

<?php
/** Example 1 - Insecure
  * SQL injection via $type
  * Display node titles of type $type (input supplied by the user via a form textfield)
  */
$result = db_query("SELECT n.nid, n.title FROM {node} n WHERE n.type = '$type'");

$items = array();
while (
$row = db_fetch_object($result)) {
 
$items[] = l($row->title, "node/{$row->nid}");
}
return
theme('item_list', $items);
?>

Example 1 displays a list of titles depending on the type argument supplied by a user. A list of page nodes will be retrieved when $type is page, a list of story nodes when $type is story. Unfortunately, the example is vulnerable to SQL injection.

The vulnerability can be used on databases with UNION support (MySQL 4.1+) to gain administrator access to the site by supplying as type: story' UNION SELECT s.sid, s.sid FROM {sessions} s WHERE s.uid = 1 -- .

This will cause the following query to be executed:

SELECT n.nid, n.title FROM {node} n WHERE n.type = 'story' UNION SELECT s.sid, s.sid FROM {sessions} s WHERE s.uid = 1 -- '

As the snippet will now display valid session ids for the administrator user account, an attacker can instruct his/her browser to use the id and have full permissions on the site.

Parameterized query prevents SQL injection

Preventing SQL injection is easy; db_query provides a way to use parameterized queries. Drupal's database functions replace the sprintf-like placeholders with the properly escaped arguments in order of appearance.

<?php
db_query
("SELECT n.nid FROM {node} n WHERE n.nid > %d", $nid);
db_query("SELECT n.nid FROM {node} n WHERE n.type = '%s'", $type);
db_query("SELECT n.nid FROM {node} n WHERE n.nid > %d AND n.type = '%s'", $nid, $type);
db_query("SELECT n.nid FROM {node} n WHERE n.type = '%s' AND n.nid > %d", $type, $nid);
?>

Valid placeholders are documented in the API documentation:

  • %d - integers
  • %f - floats
  • %s - strings, enclose in ''
  • %b - binary data, do not enclose in ''
  • %% - replaced with %

That leads us to a correction of Example 1:

<?php
/** Example 1 - Corrected
  * Display node titles of type $type (input supplied by the user via a form textfield)
  */
$result = db_query("SELECT n.nid, n.title FROM {node} n WHERE n.type = '%s'", $type);

$items = array();
while (
$row = db_fetch_object($result)) {
 
$items[] = l($row->title, "node/{$row->nid}");
}
return
theme('item_list', $items);
?>

Multiple arguments

You sometimes need to inject multiple arguments into the query, using IN() function.

In Drupal 5, you should use this code:

<?php
// Prepare $placeholders to be, e.g., "%d, %d, %d, %d, %d"
$placeholders = implode(',', array_fill(0, count($from_user), "%d"));

db_query("SELECT t.s FROM {table} t WHERE t.field IN ($placeholders)", $from_user);
?>

Drupal 6 provides a db_placeholders function. Example:

<?php
$values
= array(1, 2, 3, 4, 5);

$placeholders = db_placeholders($values);

db_query("SELECT t.s FROM {table} t WHERE t.field IN ($placeholders)", $values);
?>

For Drupal 7, see the appropriate section in the new Database API documentation.

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

Comments

reanim888’s picture

To my mind very helpful Database API documentation for Drupal 7!