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.

One thing I've learned from my Drupal development time is that every site should have a new module created for it to house all of the small custom functions and things you'll use. Common things I've added to these per-site modules include:

• Default views (more on those later),
• Odd string modifications, e.g. shortening a node summary to fit a certain amount of space,
• Hacks to modify forms in unusual ways, e.g. pre-assign taxonomy terms to simplify the editorial process,

I've found it much easier to manage these in one single module than e.g. hacking blocks with funky PHP code, etc, and you also gain the benefit of having it all in code, so you can track changes in a code versioning system like svn or git.

Bonus Tip

One final tidbit on this is to create a module "package" for all of the modules you create, i.e. add the following line to your mymodule.info file:

package = My Stuff

Now when you view the module admin page they'll all be grouped together, making them easier to find.