wpdb::prepare( string $query, array|mixed $args )

Prepares a SQL query for safe execution. Uses sprintf()-like syntax.


Description Description

The following directives can be used in the query format string: %d (integer) %f (float) %s (string) %% (literal percentage sign – no argument needed)

All of %d, %f, and %s are to be left unquoted in the query string and they need an argument passed for them. Literals (%) as parts of the query must be properly written as %%.

This function only supports a small subset of the sprintf syntax; it only supports %d (integer), %f (float), and %s (string). Does not support sign, padding, alignment, width or precision specifiers. Does not support argument numbering/swapping.

May be called like sprintf() or like vsprintf().

Both %d and %s should be left unquoted in the query string.

$wpdb->prepare( "SELECT * FROM `table` WHERE `column` = %s AND `field` = %d", 'foo', 1337 );
$wpdb->prepare( "SELECT DATE_FORMAT(`field`, '%%c') FROM `table` WHERE `column` = %s", 'foo' );

Parameters Parameters

$query

(string) (Required) Query statement with sprintf()-like placeholders

$args

(array|mixed) (Required) The array of variables to substitute into the query's placeholders if being called like vsprintf(), or the first variable to substitute into the query's placeholders if being called like sprintf().

$args,...

(mixed) (Required) further variables to substitute into the query's placeholders if being called like sprintf().


Top ↑

Return Return

(string|void) Sanitized query string, if there is a query to prepare.


Top ↑

Source Source

File: wp-includes/wp-db.php

	public function prepare( $query, $args ) {
		if ( is_null( $query ) )
			return;

		// This is not meant to be foolproof -- but it will catch obviously incorrect usage.
		if ( strpos( $query, '%' ) === false ) {
			_doing_it_wrong( 'wpdb::prepare', sprintf( __( 'The query argument of %s must have a placeholder.' ), 'wpdb::prepare()' ), '3.9.0' );
		}

		$args = func_get_args();
		array_shift( $args );
		// If args were passed as an array (as in vsprintf), move them up
		if ( isset( $args[0] ) && is_array($args[0]) )
			$args = $args[0];
		$query = str_replace( "'%s'", '%s', $query ); // in case someone mistakenly already singlequoted it
		$query = str_replace( '"%s"', '%s', $query ); // doublequote unquoting
		$query = preg_replace( '|(?<!%)%f|' , '%F', $query ); // Force floats to be locale unaware
		$query = preg_replace( '|(?<!%)%s|', "'%s'", $query ); // quote the strings, avoiding escaped strings like %%s
		array_walk( $args, array( $this, 'escape_by_ref' ) );
		return @vsprintf( $query, $args );
	}

Top ↑

Changelog Changelog

Changelog
Version Description
2.3.0 Introduced.


Top ↑

User Contributed Notes User Contributed Notes

  1. Skip to note content
    Contributed by Ian Dunn

    prepare() is often called with each un-sanitized value explicitly passed as an individual argument; for example:

    $wpdb->prepare( "SELECT id FROM wp_posts WHERE id > %d AND `post_status` = %s", $min_id, $status )

    The function will also accept an array of un-sanitized values, though, like this:

    $wpdb->prepare( "SELECT id FROM wp_posts WHERE id > %d AND `post_status` = %s", array( $min_id, $status ) )

    That can be useful in certain circumstances, like when you have a multi-dimensional array where each sub-array contains a different number of items, and so you need to build the placeholders dynamically:

    foreach ( $new_status_post_id_map as $new_status => $wordcamp_ids ) {
    	$wordcamp_id_placeholders = implode( ', ', array_fill( 0, count( $wordcamp_ids ), '%d' ) );
    	$prepare_values           = array_merge( array( $new_status ), $wordcamp_ids );
    
    	$wpdb->query( $wpdb->prepare( "
    		UPDATE `$table_name`
    		SET `post_status` = %s
    		WHERE ID IN ( $wordcamp_id_placeholders )",
    		$prepare_values
    	) );
    }

    So if a sub-array has 2 items, then $wordcamp_id_placeholders will be '%d, %d', and if the next array has 4 items, then its placeholder string would be '%d, %d, %d, %d'.

  2. Skip to note content
    Contributed by martinkolle

    Reply to https://developer.wordpress.org/reference/classes/wpdb/prepare/#comment-2240
    Tablename should not be defined like this, because if the prefix is changed or used in a plugin, it will not work on all sites. The proper way is:

    $table_name = "{$wpdb->prefix}myTable";
    $myID = 12;
    
    $wpdb->query( $wpdb->prepare( "UPDATE `$table_name` SET `your_column_1` = 1 WHERE `$table_name`.`your_column_id` = %d", $myID ) );
    

You must log in before being able to contribute a note or feedback.