Skip to main content

Drupal database prepared statements

An article from ComputerMinds - Building with Drupal in the UK since 2005
25th May 2011

Steven Jones

Senior Developer
Steven Jones
Hey, you seem to look at this article a lot! Why not Bookmark this article so you can find it easily in the future?

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.

Hi, thanks for reading

ComputerMinds are the UK’s Drupal specialists with offices in Bristol and Coventry. We offer a range of Drupal services including Consultancy, Development, Training and Support. Whatever your Drupal problem, we can help.