Database access
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.
Parametrized query prevents SQL injection
Preventing SQL injection is easy; db_query provides a way to use parametrized 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);
?>