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

Prepares a SQL query for safe execution.

Description

Uses sprintf()-like syntax. The following placeholders can be used in the query string:

• %d (integer)
• %f (float)
• %s (string)
• %i (identifier, e.g. table/field names)

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

$wpdb->prepare(
 "SELECT * FROM %i WHERE %i = %s",
 $table,
 $field,
 $value
);

Parameters

$querystringrequired

Query statement with sprintf()-like placeholders.

$argsmixedrequired

Further variables to substitute into the query’s placeholders if being called with individual arguments.

Return

Source

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

	/*
	 * This is not meant to be foolproof -- but it will catch obviously incorrect usage.
	 *
	 * Note: str_contains() is not used here, as this file can be included
	 * directly outside of WordPress core, e.g. by HyperDB, in which case
	 * the polyfills from wp-includes/compat.php are not loaded.
	 */
	if ( false === strpos( $query, '%' ) ) {
		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'
		);
	}

	/*
	 * 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.

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

	// Extract placeholders from the query.
	$split_query = preg_split( "/(^|[^%]|(?:%%)+)(%(?:$allowed_format)?[sdfFi])/", $query, -1, PREG_SPLIT_DELIM_CAPTURE );

	$split_query_count = count( $split_query );

	/*
	 * Split always returns with 1 value before the first placeholder (even with $query = "%s"),
	 * then 3 additional values per placeholder.
	 */
	$placeholder_count = ( ( $split_query_count - 1 ) / 3 );

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

	$new_query = '';
	$key = 2; // Keys 0 and 1 in $split_query contain values before the first placeholder.
	$arg_id = 0;
	$arg_identifiers = array();
	$arg_strings = array();

	while ( $key < $split_query_count ) {
		$placeholder = $split_query[ $key ];

		$format = substr( $placeholder, 1, -1 );
		$type = substr( $placeholder, -1 );

		if ( 'f' === $type && true === $this->allow_unsafe_unquoted_parameters
			/*
			 * Note: str_ends_with() is not used here, as this file can be included
			 * directly outside of WordPress core, e.g. by HyperDB, in which case
			 * the polyfills from wp-includes/compat.php are not loaded.
			 */
			&& '%' === substr( $split_query[ $key - 1 ], -1, 1 )
		) {

			/*
			 * Before WP 6.2 the "force floats to be locale-unaware" RegEx didn't
			 * convert "%%%f" to "%%%F" (note the uppercase F).
			 * This was because it didn't check to see if the leading "%" was escaped.
			 * And because the "Escape any unescaped percents" RegEx used "[sdF]" in its
			 * negative lookahead assertion, when there was an odd number of "%", it added
			 * an extra "%", to give the fully escaped "%%%%f" (not a placeholder).
			 */

			$s = $split_query[ $key - 2 ] . $split_query[ $key - 1 ];
			$k = 1;
			$l = strlen( $s );
			while ( $k <= $l && '%' === $s[ $l - $k ] ) {
				++$k;
			}

			$placeholder = '%' . ( $k % 2 ? '%' : '' ) . $format . $type;

			--$placeholder_count;

		} else {

			// Force floats to be locale-unaware.
			if ( 'f' === $type ) {
				$type = 'F';
				$placeholder = '%' . $format . $type;
			}

			if ( 'i' === $type ) {
				$placeholder = '`%' . $format . 's`';
				// Using a simple strpos() due to previous checking (e.g. $allowed_format).
				$argnum_pos = strpos( $format, '$' );

				if ( false !== $argnum_pos ) {
					// sprintf() argnum starts at 1, $arg_id from 0.
					$arg_identifiers[] = ( ( (int) substr( $format, 0, $argnum_pos ) ) - 1 );
				} else {
					$arg_identifiers[] = $arg_id;
				}
			} elseif ( 'd' !== $type && 'F' !== $type ) {
				/*
				 * i.e. ( 's' === $type ), where 'd' and 'F' keeps $placeholder unchanged,
				 * and we ensure string escaping is used as a safe default (e.g. even if 'x').
				 */
				$argnum_pos = strpos( $format, '$' );

				if ( false !== $argnum_pos ) {
					$arg_strings[] = ( ( (int) substr( $format, 0, $argnum_pos ) ) - 1 );
				} else {
					$arg_strings[] = $arg_id;
				}

				/*
				 * Unquoted strings for backward compatibility (dangerous).
				 * First, "numbered or formatted string placeholders (eg, %1$s, %5s)".
				 * Second, if "%s" has a "%" before it, even if it's unrelated (e.g. "LIKE '%%%s%%'").
				 */
				if ( true !== $this->allow_unsafe_unquoted_parameters
					/*
					 * Note: str_ends_with() is not used here, as this file can be included
					 * directly outside of WordPress core, e.g. by HyperDB, in which case
					 * the polyfills from wp-includes/compat.php are not loaded.
					 */
					|| ( '' === $format && '%' !== substr( $split_query[ $key - 1 ], -1, 1 ) )
				) {
					$placeholder = "'%" . $format . "s'";
				}
			}
		}

		// Glue (-2), any leading characters (-1), then the new $placeholder.
		$new_query .= $split_query[ $key - 2 ] . $split_query[ $key - 1 ] . $placeholder;

		$key += 3;
		++$arg_id;
	}

	// Replace $query; and add remaining $query characters, or index 0 if there were no placeholders.
	$query = $new_query . $split_query[ $key - 2 ];

	$dual_use = array_intersect( $arg_identifiers, $arg_strings );

	if ( count( $dual_use ) > 0 ) {
		wp_load_translations_early();

		$used_placeholders = array();

		$key = 2;
		$arg_id = 0;
		// Parse again (only used when there is an error).
		while ( $key < $split_query_count ) {
			$placeholder = $split_query[ $key ];

			$format = substr( $placeholder, 1, -1 );

			$argnum_pos = strpos( $format, '$' );

			if ( false !== $argnum_pos ) {
				$arg_pos = ( ( (int) substr( $format, 0, $argnum_pos ) ) - 1 );
			} else {
				$arg_pos = $arg_id;
			}

			$used_placeholders[ $arg_pos ][] = $placeholder;

			$key += 3;
			++$arg_id;
		}

		$conflicts = array();
		foreach ( $dual_use as $arg_pos ) {
			$conflicts[] = implode( ' and ', $used_placeholders[ $arg_pos ] );
		}

		_doing_it_wrong(
			'wpdb::prepare',
			sprintf(
				/* translators: %s: A list of placeholders found to be a problem. */
				__( 'Arguments cannot be prepared as both an Identifier and Value. Found the following conflicts: %s' ),
				implode( ', ', $conflicts )
			),
			'6.2.0'
		);

		return;
	}

	$args_count = count( $args );

	if ( $args_count !== $placeholder_count ) {
		if ( 1 === $placeholder_count && $passed_as_array ) {
			/*
			 * If the passed query only expected one argument,
			 * but the wrong number of arguments was 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).' ),
					$placeholder_count,
					$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 < $placeholder_count ) {
				$max_numbered_placeholder = 0;

				for ( $i = 2, $l = $split_query_count; $i < $l; $i += 3 ) {
					// Assume a leading number is for a numbered placeholder, e.g. '%3$s'.
					$argnum = (int) substr( $split_query[ $i ], 1 );

					if ( $max_numbered_placeholder < $argnum ) {
						$max_numbered_placeholder = $argnum;
					}
				}

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

	$args_escaped = array();

	foreach ( $args as $i => $value ) {
		if ( in_array( $i, $arg_identifiers, true ) ) {
			$args_escaped[] = $this->_escape_identifier_value( $value );
		} elseif ( is_int( $value ) || is_float( $value ) ) {
			$args_escaped[] = $value;
		} else {
			if ( ! is_scalar( $value ) && ! is_null( $value ) ) {
				wp_load_translations_early();
				_doing_it_wrong(
					'wpdb::prepare',
					sprintf(
						/* translators: %s: Value type. */
						__( 'Unsupported value type (%s).' ),
						gettype( $value )
					),
					'4.8.2'
				);

				// Preserving old behavior, where values are escaped as strings.
				$value = '';
			}

			$args_escaped[] = $this->_real_escape( $value );
		}
	}

	$query = vsprintf( $query, $args_escaped );

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

View all references View on Trac View on GitHub

Related

Changelog

User Contributed Notes

1. Skip to note 8 content

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

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

   In the future, https://core.trac.wordpress.org/ticket/54042 may provide an easier way to do this.

2. Skip to note 9 content

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

   • Placeholders like %1$s have been deprecated since version 4.8.2.

     Berdych 7 years ago
3. Skip to note 10 content

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

   • `%i` is also available now, for escaping table names. See https://core.trac.wordpress.org/ticket/52506

     Ian Dunn 3 years ago
4. Skip to note 11 content

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

   • In your example, the table names are not escaped properly. To escape them properly in WordPress 6.2 or greater, use the %i placeholder, like this: $table_name = "{$wpdb->prefix}myTable"; $myID = 12; $wpdb->query( $wpdb->prepare( "UPDATE %i SET `your_column_1` = 1 WHERE %i.`your_column_id` = %d", $table_name, $table_name, $myID ) );

     flimm 3 years ago
5. Skip to note 12 content

   $wpdb->prepare("%d", 1); // "1"
$wpdb->prepare("%d", 1.1); // "1"
$wpdb->prepare("%d", 'string'); // "0"
$wpdb->prepare("%d", NULL); // "0"
$wpdb->prepare("%d", false); // "0"

   $wpdb->prepare("%f", 1); // "1.000000"
$wpdb->prepare("%f", 1.1); // "1.100000"
$wpdb->prepare("%f", 'string'); // "0.000000"
$wpdb->prepare("%f", NULL); // "0.000000"
$wpdb->prepare("%f", false); // "0.000000"

   $wpdb->prepare("%s", 1); // "'1'"
$wpdb->prepare("%s", 1.1); // "'1.1'"
$wpdb->prepare("%s", 'string'); // "'string'"
$wpdb->prepare("%s", NULL); // "''"
$wpdb->prepare("%s", false); // "''"

   $wpdb->prepare("%i", 1); // "`1`"
$wpdb->prepare("%i", 1.1); // "`1.1`"
$wpdb->prepare("%i", 'string'); // "`string`"
$wpdb->prepare("%i", NULL); // "``"
$wpdb->prepare("%i", false); // "``"

6. Skip to note 13 content

   The code below does not work!

   $results = $wpdb->get_results($wpdb->prepare(
    "SELECT `id`, `name`, `desc` ".
    "FROM `%s` ORDER BY `id`, 
    $table_name));

   I would end up with a select statement:
   SELECT `id`, `name`, `desc` FROM `’table-name’` ORDER BY `id`

   Even if I remove the ` character from around my %s placeholder, it still will not work because table-name cannot be surrounded by ‘ characters. If $wpdb->prepare could check if my %s placeholder is already surrounded by ` characters and in that case not add ‘ characters around my %s placeholder then the code above would work.

   So my workaround (for select-statements) is to use sprintf instead (of $wpdb->prepare) and I always check sql statements in Phpmyadmin.

   • There is no need to use wpdb->prepare for table names. wpdb->prepare is for the variables you get from user input or from front-end via javascript/html form as $_POST,$_GET variables. Table name is always defined and used by the system developer, so there is no need to add extra check for table names. And nobody should put table name to user-facing forms.

     mogo 3 years ago
   • You can now use the `%i` placeholder for table names. See https://core.trac.wordpress.org/ticket/52506

     Ian Dunn 3 years ago
   • Use `$wpdb` table names instead, "SELECT `ID` from `{$wpdb->posts}`..." or for custom tables, "SELECT `my_field` from `{$wpdb->prefix}my_custom_table` ... "

     Aurovrata Venet 3 years ago
7. Skip to note 14 content

   Example: Simple update with prepare (one parameter)

   CASE: Update a column value Where ID column = 12

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