Database access

Last updated on
20 February 2017

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:

Insecure example, do not use!

/**
 * 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);

This example 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.

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 the example:

/**
 * 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:

// 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:

$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.