wpdb::prepare( string $query, array|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 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

(array|mixed) (Required) The array of variables to substitute into the query's placeholders if being called with an array of arguments, or the first variable to substitute into the query's placeholders if being called with individual arguments.

$args,...

(mixed) (Required) further variables to substitute into the query's placeholders if being called wih 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( __( '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.
		$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( __( '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 backwards 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',
					/* translators: 1: number of placeholders, 2: number of arguments passed */
					sprintf( __( '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
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.