wpdb::prepare() │ public │ WP 2.3.0

Prepares a SQL query for safe execution.

Uses sprintf()-like syntax. 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.

Note: There is one exception to the above: 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.

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'
);

{} It's a method of the class: wpdb{}

Uses: wpdb::add_placeholder_escape()

No Hooks.

Return

String|null. Sanitized query string, if there is a query to prepare.

Usage

global $wpdb;
$wpdb->prepare( $query, ...$args );

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

Examples

#1 Demo of sanitizing SQL query

$sql = $wpdb->prepare(
	"SELECT * FROM `table` WHERE `column` = %s AND `field` = %d OR `other_field` LIKE %s",
	[ 'foo', 1337, '%bar' ]
);

$wpdb->get_results( $sql );

$sql = $wpdb->prepare(
	"SELECT DATE_FORMAT( `field`, '%%c' ) FROM `table` WHERE `column` = %s",
	'column_val'
);

$wpdb->get_results( $sql );

#2 Add a custom field to post 10

In this example you may see there is no need to take care of escaping quotes and other things that can harm the query.

$metakey = "'crash' database";
$metavalue = "WordPress can 'break' the Database if the query is not escaped";

$wpdb->query( $wpdb->prepare(
	"INSERT INTO $wpdb->postmeta ( post_id, meta_key, meta_value ) VALUES ( %d, %s, %s )",
	10, $metakey, $metavalue
) );

#3 Passing parameters as an array

This is the same example, only here all variables are passed in the second parameter as an array.

Passing parameters as an array can be useful when we don't know in advance the number of arguments to pass.

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

$wpdb->prepare( 
	"SELECT id FROM $wpdb->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 ) {

	$ids_pholders   = 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 ( $ids_pholders )",
		$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'.

#4 Argument swapping is not supported

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.
$post_date = 'post_date';
$search_string = 'search_string';

echo $wpdb->prepare(
	'SELECT * FROM table_name WHERE `post_date` > %1$s AND `post_title` LIKE %2$s OR `post_content` LIKE %2$s',
	$post_date,
	$search_string
);

/* Result:

SELECT * FROM table_name WHERE `post_date` > post_date AND `post_title` LIKE search_string OR `post_content` LIKE search_string

PHP User Notice: Function wpdb::prepare was called incorrectly. The query does not contain the correct number of placeholders (3)
*/

Instead, you need to pass each argument individually:

$post_date = 'post_date';
$search_string = 'search_string';

echo $wpdb->prepare(
	'SELECT * FROM table_name WHERE post_date > %1$s AND post_title LIKE %2$s OR post_content LIKE %3$s',
	$post_date,
	$search_string,
	$search_string
);

/*
SELECT * FROM table_name WHERE post_date > post_date AND post_title LIKE search_string OR post_content LIKE search_string
*/

There is no string escape (tested WP 6.0):

echo $wpdb->prepare( 'post_date = %1$s', 'post" date' ); // post\" date
echo $wpdb->prepare( 'post_date = %s', 'post" date' );   // post_date = 'post\" date'

#5 Available placeholders

%s – string (value is escaped and wrapped in quotes)
%d – integer
%f – float
%% – % sign

LIKE Statements – use esc_like() and use placeholder % in arg-value, not inside the query

$my_domain = 'example.com';

$sql = $wpdb->prepare(
	"SELECT * FROM $wpdb->options WHERE option_value LIKE %s;",
	'%' . $wpdb->esc_like( $my_domain ) . '%'
);

Changelog

Since 2.3.0	Introduced.
Since 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`.

Code of `wpdb::prepare() wpdb::prepare` ^{WP 6.1.1}

wp-includes/class-wpdb.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 ( isset( $args[0] ) && is_array( $args[0] ) && 1 === count( $args ) ) {
		$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, e.g. $+d
	 * - Numbered placeholders, e.g. %1$s
	 * - Padding specifier, including custom padding characters, e.g. %05s, %'#5s
	 * - Alignment specifier, e.g. %05-s
	 * - Precision specifier, e.g. %.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 );

	$args_count = count( $args );

	if ( $args_count !== $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,
					$args_count
				),
				'4.8.3'
			);

			/*
			 * If we don't have enough arguments to match the placeholders,
			 * return an empty string to avoid a fatal error on PHP 8.
			 */
			if ( $args_count < $placeholders ) {
				$max_numbered_placeholder = ! empty( $matches[3] ) ? max( array_map( 'intval', $matches[3] ) ) : 0;

				if ( ! $max_numbered_placeholder || $args_count < $max_numbered_placeholder ) {
					return '';
				}
			}
		}
	}

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

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