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

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


Description Description

The following placeholders can be used in the query string: %d (integer) %f (float) %s (string)

All placeholders MUST be left unquoted in the query string. A corresponding argument MUST be passed for each placeholder.

For compatibility with old behavior, numbered or formatted string placeholders (eg, %1$s, %5s) will not have quotes added by this function, so should be passed with appropriate quotes around them for your usage.

Literal percentage signs (%) in the query string must be written as %%. Percentage wildcards (for example, to use in LIKE syntax) must be passed via a substitution argument containing the complete LIKE string, these cannot be inserted directly in the query string. Also see wpdb::esc_like().

Arguments may be passed as individual arguments to the method, or as a single array containing all arguments. A combination of the two is not supported.

Examples: $wpdb->prepare( "SELECT * FROM table WHERE column = %s AND field = %d OR other_field LIKE %s", array( ‘foo’, 1337, ‘%bar’ ) ); $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

(mixed) (Required) Further variables to substitute into the query's placeholders if being called with individual arguments.


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 ) {
			wp_load_translations_early();
			_doing_it_wrong(
				'wpdb::prepare',
				sprintf(
					/* translators: %s: wpdb::prepare() */
					__( 'The query argument of %s must have a placeholder.' ),
					'wpdb::prepare()'
				),
				'3.9.0'
			);
		}

		// If args were passed as an array (as in vsprintf), move them up.
		$passed_as_array = false;
		if ( is_array( $args[0] ) && count( $args ) == 1 ) {
			$passed_as_array = true;
			$args            = $args[0];
		}

		foreach ( $args as $arg ) {
			if ( ! is_scalar( $arg ) && ! is_null( $arg ) ) {
				wp_load_translations_early();
				_doing_it_wrong(
					'wpdb::prepare',
					sprintf(
						/* translators: %s: Value type. */
						__( 'Unsupported value type (%s).' ),
						gettype( $arg )
					),
					'4.8.2'
				);
			}
		}

		/*
		 * Specify the formatting allowed in a placeholder. The following are allowed:
		 *
		 * - Sign specifier. eg, $+d
		 * - Numbered placeholders. eg, %1$s
		 * - Padding specifier, including custom padding characters. eg, %05s, %'#5s
		 * - Alignment specifier. eg, %05-s
		 * - Precision specifier. eg, %.2f
		 */
		$allowed_format = '(?:[1-9][0-9]*[$])?[-+0-9]*(?: |0|\'.)?[-+0-9]*(?:\.[0-9]+)?';

		/*
		 * If a %s placeholder already has quotes around it, removing the existing quotes and re-inserting them
		 * ensures the quotes are consistent.
		 *
		 * For backward compatibility, this is only applied to %s, and not to placeholders like %1$s, which are frequently
		 * used in the middle of longer strings, or as table name placeholders.
		 */
		$query = str_replace( "'%s'", '%s', $query ); // Strip any existing single quotes.
		$query = str_replace( '"%s"', '%s', $query ); // Strip any existing double quotes.
		$query = preg_replace( '/(?<!%)%s/', "'%s'", $query ); // Quote the strings, avoiding escaped strings like %%s.

		$query = preg_replace( "/(?<!%)(%($allowed_format)?f)/", '%\\2F', $query ); // Force floats to be locale unaware.

		$query = preg_replace( "/%(?:%|$|(?!($allowed_format)?[sdF]))/", '%%\\1', $query ); // Escape any unescaped percents.

		// Count the number of valid placeholders in the query.
		$placeholders = preg_match_all( "/(^|[^%]|(%%)+)%($allowed_format)?[sdF]/", $query, $matches );

		if ( count( $args ) !== $placeholders ) {
			if ( 1 === $placeholders && $passed_as_array ) {
				// If the passed query only expected one argument, but the wrong number of arguments were sent as an array, bail.
				wp_load_translations_early();
				_doing_it_wrong(
					'wpdb::prepare',
					__( 'The query only expected one placeholder, but an array of multiple placeholders was sent.' ),
					'4.9.0'
				);

				return;
			} else {
				/*
				 * If we don't have the right number of placeholders, but they were passed as individual arguments,
				 * or we were expecting multiple arguments in an array, throw a warning.
				 */
				wp_load_translations_early();
				_doing_it_wrong(
					'wpdb::prepare',
					sprintf(
						/* translators: 1: Number of placeholders, 2: Number of arguments passed. */
						__( 'The query does not contain the correct number of placeholders (%1$d) for the number of arguments passed (%2$d).' ),
						$placeholders,
						count( $args )
					),
					'4.8.3'
				);
			}
		}

		array_walk( $args, array( $this, 'escape_by_ref' ) );
		$query = vsprintf( $query, $args );

		return $this->add_placeholder_escape( $query );
	}

Top ↑

Changelog Changelog

Changelog
Version Description
5.3.0 Formalized the existing and already documented ...$args parameter by updating the function signature. The second parameter was changed from $args to ...$args.
2.3.0 Introduced.


Top ↑

User Contributed Notes User Contributed Notes

  1. Skip to note 1 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 2 content
    Contributed by martinkolle

    Reply to http://wpsocket.com/wpref/class/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 ) );
    
  3. Skip to note 3 content
    Contributed by macbookandrew

    Argument swapping is not supported in the sense that you can not reuse the same argument several times in a prepare statement.

    For example, this does not work but throws an error because the number of placeholders does not match the number of arguments passed:

    // Does NOT work due to not enough arguments being passed.
    $wpdb->prepare(
    	"SELECT * FROM {$wpdb->posts} WHERE `post_date` > %1$s AND `post_title` LIKE %2$s OR `post_content` LIKE %2$s",
    	$post_date,
    	$search_string
    );
    

    Instead, you need to pass each argument individually:

    // Pass each argument for every time you need it.
    $wpdb->prepare(
    	"SELECT * FROM {$wpdb->posts} WHERE `post_date` > %1$s AND `post_title` LIKE %2$s OR `post_content` LIKE %3$s",
    	$post_date,
    	$search_string,
    	$search_string
    );
    

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