Does $wpdb->prepare not create a prepared statement that I can execute multiple times?

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?

Read More

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

Related posts

Leave a Reply

2 comments

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

  2. 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.