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

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

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

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

Changelog

VersionDescription
6.2.0Added %i for identifiers, e.g. table or field names.
Check support via wpdb::has_cap( 'identifier_placeholders' ).
This preserves compatibility with sprintf(), as the C version uses %d and $i as a signed integer, whereas PHP only supports %d.
5.3.0Formalized the existing and already documented ...$args parameter by updating the function signature. The second parameter was changed from $args to ...$args.
2.3.0Introduced.

User Contributed Notes

  1. Skip to note 7 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 8 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
    );
  3. Skip to note 9 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 ) );
  4. 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 ) . '%'
    );
  5. Skip to note 11 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.

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