Fixing update_sql() to accept parameters

Tags: 

A pretty simple yet cool feature of Drupal's db_query() function is that you can pass in parameters that will make it automatically adjust the query to correctly escape the arguments. This is one of the simple security features in Drupal as it will properly escape the string to avoid SQL injection attacks, and just simply safe you hassle. Good stuff!

When it comes to module development you'll occasionally have to update the database schema for one reason or another, so you'd figure the corresponding update_sql() function, which has some extra purpose for update scripts, would also handle parameters. Not so, unfortunately. In Drupal 5 and 6 the only argument that update_sql() accepts is the query itself, so there's no direct way of doing the parameter auto-magic gravy. Thankfully, all is not lost.

If you look at the code in update_sql(), it's actually very simple - it just runs the standard db_query() string and compiles a nice array to let the update script give a reasonable return message. So, simply put, to do the parameter processing in Drupal 5 and 6 all you have to do is replicate the update_sql() function inline, e.g.:

<?php
/**
 * Change the primary color from blue to green.
 */
function funkychicken_update_6001() {
  $ret = array();
  
  $sql = "UPDATE {funkychicken} SET color='%s' WHERE color='blue'";
  $result = db_query($sql, 'green');
  $ret[] = array('success' => $result !== FALSE, 'query' => check_plain($sql));

  return $ret;
}

So that's all there is to it!

Going a step further, here's an alternative to update_sql() to make it even easier. This can be added to your module's update file, e.g.:

<?php
/**
 * Change the primary color from blue to green.
 */
function funkychicken_update_6001() {
  $ret = array();
  
  $ret[] = update_sql2("UPDATE {funkychicken} SET color='%s' WHERE color='blue'", 'green');

  return $ret;
}

/**
 * An alternative to update_sql() that accepts parameters.
 * Params:
 *   $sql - The query to execute, as a string.
 *   $args - The parameters to pass to the query.
 * Return:
 *   An array containing the success status (boolean) and the query.
 */
if (!function_exists('update_sql2')) {
  function update_sql2($sql, $args = NULL) {
    $result = db_query($sql, $args);
    return array('success' => $result !== FALSE, 'query' => check_plain($sql));
  }
}

FYI there's a patch available for Drupal 6 that might eventually make it into a release, it just needs to be worked on a bit more. Either way, hopefully a fix for this will work its way into Drupal 7.