Drupal database prepared statements

Drupal 7's database layer is awesome, it is built upon PDO and one of the great things about PDO is named placeholders, they allow you to build queries like:

$unsafestring = "this string can contain quotes: ' or other things";
$query = db_select('table')
           ->fields('table')
           ->condition('field', $unsafestring);

The SQL that is sent to the database is:


SELECT table.* FROM table WHERE (field = :db_condition_placeholder_0)


This is sent along with the contents of $unsafestring to replace the :db_condition_placeholder_0 token. Note that this isn't some lame string replacement, but an actual argument for the SQL statement.

This has some interesting implications for converting some code patterns in Drupal 6. Let's say that you want to select all rows from a table where a column matches the value of another, but the user can choose from one of three columns to match against. I've seen this sort of code used to get this sort of variable into a database string:

$field_name = fetch_from_user(); // returns 'fieldA', 'fieldB' or 'fieldC'
db_query('SELECT table.* FROM table WHERE (field = %s)', $field_name);

This probably isn't the best pattern, but it does work.

However, it's direct conversion to Drupal 7 does not:

$field_name = fetch_from_user(); // returns 'fieldA', 'fieldB' or 'fieldC'
$query = db_select('table')
           ->fields('table')
           ->condition('field', $field_name);

Because the SQL prepared by the database layer is exactly the same as before:


SELECT table.* FROM table WHERE (field = :db_condition_placeholder_0)


So, you'll actually be selecting rows where the value of field is fieldA, fieldB or fieldC not matching the values in those columns.

Instead of using the ->condition method, we actually need to use the ->where method that allows us to construct whatever clause we like:

$field_name = fetch_from_user(); // returns 'fieldA', 'fieldB' or 'fieldC'
$query = db_select('table')
           ->fields('table')
           ->where('field = '. db_escape_field($field_name));

Note the call to db_escape_field to sanitize the user input, which was actually missing from my Drupal 6 example above. For 'fieldA' this gets turned into:


SELECT table.* FROM table WHERE (field = fieldA)


And the query will return the correct results.