Prepare, Don’t Escape!

Not a bad title for a post about code, so if you’re reading this expecting something non-geeky then look away now! Ready?

Since WordPress 2.5 a new $wpdb function has been included called ‘prepare’. I’ve been using the new function extensively throughout BuddyPress. As it’s so new, there are still quite a few plugin developers who are not aware of its existence.

So what does ‘$wpdb->prepare()’ do exactly? Simply put, it replaces the old ‘$wpdb->escape()’ function for escaping variables passed into an SQL statement. Escaping variables is great for preventing SQL injection attacks and keeping the bad dudes out.

With $wpdb->escape you would usually do something like this:

$field1 = $wpdb->escape("Andy Peatling");
$field2 = $wpdb->escape("It's like that, and that's the way it is.");
$id = $wpdb->escape($_POST['id']);

$wpdb->query( "INSERT INTO $wpdb->sometable( id, field1, field2 ) VALUES ( $id, '$field1', '$field2' ) ");

This works great, but you still have to deal with remembering to put single quotes around strings, and calling the function for every single variable you want to pass into the query.

The $wpdb->prepare() method simplifies things further, generally making your life a lot easier by combing everything into one call:

$field1 = "Andy Peatling";
$field2 = "It's like that, and that's the way it is.";

$wpdb->query( $wpdb->prepare( "INSERT INTO $wpdb->sometable( id, field1, field2 ) VALUES ( %d, %s, %s )", $_POST['id'], $field1, $field2 ) );

Notice that you no longer have to worry about quoting strings. Instead of including the actual variable name within the query you simply place a '%d' for integers and '%s' for strings. Then for the second parameter onwards you pass the variables in the same order they are in the query.

The new WordPress coding standards page touches briefly on the new prepare function. The WordPress Codex however still includes references to the escape method which I will be helping to update. has been updated to include references to the new prepare() method.

Leave any questions in the comments, I'd be happy to help.

7 thoughts on “Prepare, Don’t Escape!

  1. How to write a solid and stable WordPress plugin « Mark on WordPress

  2. You know, I understand the code, but I wish I could understand exactly what it would output into the database.

    What exactly happens with the text that gets input into the database? How is it displayed in the database table?

  3. better someone pointed this out late than never I suppose: there is a close bracket missing…

    “$_POST['id'], $field1, $field2 ));”

  4. I’m just trying to secure my default theme code by making all SQL queries use $wpdb->prepare. However, I’ve got a problem with queries that use the IN ( ) operator. When you pass a list of IDs, each item in the list is an integer, but the whole argument is obviously not. But it’s not a string – passing the list with %s doesn’t work.

    I know the way around this is to pass each item in the IN list individually, but sometimes this seems impractical for some of my dynamic queries. Any ideas?

Comments are closed.