I am wondering from the syntax

$sql = $wpdb->prepare( 'query' [, value_parameter, value_parameter ... ] )

It appears (from the syntax - values within prepare) that it simple escapes values not craete a prepared statement giving me performance benefits when I execute it multiple times will different params?

On 2nd look, since values are optional, I can set them after preparing? How do I do that?


$wpdb-prepare works like sprintf and vsprintf. The first argument will always be a format string.

The only acceptable format specifiers are %s and %d. Others I have never tested but may result in parse error as per the Codex. You must escape literal % in your query with %, e.g: %%

If you use it like sprintf which is only possible if you know the number of arguments before runtime then you can the number of arguments should match the number of format specifiers in your format string.

E.g: $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}_votes WHERE post_id = %d AND username = %s", $post_id, $username );

If you don't know the number of arguments till the runtime then you must use it like vsprintf. In this case the first argument will be format specifier but the second argument will be an array.

E.g: $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}_votes WHERE post_id = %d AND username = %s", array( $post_id, $username ) );

$wpdb->prepare will return a SQL QUERY string which you can execute as many times as you like.

For the above examples the resulted query will be: SELECT * FROM wp_votes WHERE post_id = 747 AND username = 'cooluser'

  • hmm suppose I want to execute it many time ... I will do it like? pastie.org/2099326? which means I am running prepare many times still? – JM at Work Jun 21 '11 at 2:45
  • Yes, when ever you will need a new query you should run prepare again. And when ever there is change in your arguments you will need new query for that. – Hameedullah Khan Jun 21 '11 at 5:52
  • I use %u when I am using unsigned integers. Although not support by the Codex, I have not been getting errors from this and it might provide you with the sanitizing you need. – Marc Dingena Mar 27 '14 at 14:59
  • You have not quoted your %s strings in your query, and your answer implies that WP will quote them for you. However, with WP 4.1 I am finding that WP does not quote strings and you need to do it yourself in your query. The WP Codex examples also show it not quoted. Are there instances when bound strings don't get quoted and other instances where they do? – Jason Feb 24 '15 at 16:48

I think it goes without saying that developers expect that a "prepared" statement means that it's "prepared" in the database!

It was asked in a sub question if the statement can be used over and over. The wpdb->prepare does not actually "prepare" the statement. It only sanitizes the inputs. You can see this in: wordpress/wp-includes/wp-db.php. Look for the prepare function and you see that it's just creating a normal non-prepared query:

$query = array_shift($args);
$query = str_replace("'%s'", '%s', $query);
$query = str_replace('"%s"', '%s', $query);
$query = str_replace('%s', "'%s'", $query); // quote the strings
array_walk($args, array(&$this, 'escape_by_ref'));
return @vsprintf($query, $args); 

Basically, the wpdb->prepare function should be called "sanitize" or "clean" or something like that. Calling it "prepare" is misleading for anyone who knows SQL.

  • 2
    This answer needs a lot more attention! The word prepare has meaning in the database world, and what Wordpress is doing is not prepare. – dotancohen Jan 8 '17 at 19:28
  • thx for clarification, i expected something completely different. – nfo Nov 2 '17 at 14:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.