Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

up vote 1 down vote accepted

$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'

share|improve this answer
    
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 at 14:59

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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