Backdrop provides several functions to send queries to the database. The canonical form is db_query(). Always use functions provided by Backdrop 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. Backdrop'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);
?>

Use access checks on node listings

Use db_select() tagged with (for example) 'node_access' to provide a method for modules to extend your SQL queries. For example, a module which controls access to nodes will need to limit the results of your queries, removing any nodes for which a visitor does not have the required set of access permissions.

If you do not make use of the access control tags, access control modules won't be able to modify or extend your SQL queries, and you may inadvertently expose content that is meant to be restricted.

Possible exceptions include:

Queries which carry out internal module work, but which aren't responsible for showing content to users (e.g. queries within cron tasks).
Queries for administrative pages where it is necessary to show an unfiltered list, and where the user is guaranteed to already have full privileges.

Example:

<?php
$nids = db_select('node', 'n')
  ->fields('n', array('nid'))
  ->condition('status', 1)
  ->addTag('node_access') // Adds access checking
  ->execute()
?>

Securing database access

Parameterized query prevents SQL injection

Use access checks on node listings