Cracking Drupal®: A Drop in the Bucket

5.1. Database Sanitizing: db_query and Friends

The database is the basic storage unit for data within Drupal, and it is no coincidence that it has a rich set of APIs to interact with it safely. The major issue with database queries is that strings and binary data (blobs) must be escaped so that the user-supplied data is inserted into the database rather than being interpreted as part of the instructions in the SQL itself. Up until Drupal 6.x, Drupal's database functions have utilized a placeholder replacement system based on the style of the C programming language's printf() function. The main function in the Drupal database API is db_query, but db_query also has some friends: special functions like db_query_range and pager_query, which have similar syntax and security best practices.

NOTE

For Drupal 7.x (due to be released in 2009) the database layer has changed a bit, though many of these concepts still apply. So, first I'll demonstrate 6.x and prior style and then the 7.x and newer style.

5.1.1. Queries for Drupal 6.x and Earlier

The API for 6.x and earlier is fairly easy to memorize. You can generally use db_query() to run a query. If you need to limit the range of the query (that is, to provide the equivalent of the MySQL "LIMIT 0, 10") you would use db_query_range(). The query should use placeholders for any variables. There are five % placeholders to use in a query, as shown in Table 5-1.

Table 5.1. % placeholders

%s	For strings such as a username
%d	For integer numbers (i.e., ...