wpdb::prepare()
Allows writing an SQL query with sanitization of the parameters passed to it.
In the query string, instead of the passed parameter, you need to use a placeholder:
%d(integer)%f(float)%s(string)
Also, for each of the placeholders, a PHP variable must be specified that will replace the placeholder. When replacing, the variable will be sanitized. The syntax is similar to sprintf().
Since WP 3.5, at least 2 parameters must be passed: the query and the variable value, otherwise there will be a PHP error (User Notice).
- Quotes for placeholders
%sand'%s'. Placeholders can be in quotes or without them:
WHERE field = %sorWHERE field = '%s'. It is customary not to use quotes.echo $wpdb->prepare( "foo = %s", 'a' ); // foo = 'a' echo $wpdb->prepare( "foo = '%s'", 'a' ); // foo = 'a'
- Parameter for each placeholder.
A parameter must be specified for each placeholder.
echo $wpdb->prepare( 'foo = %s AND bar = %s', 'a' ); echo $wpdb->prepare( 'foo = %1$s AND bar = %1$s', 'a' ); // in both cases we will see an error: // User Notice: wpdb::prepare was called incorrectly. // The query does not contain the correct number of placeholders (2) // for the number of arguments passed (1).
- Positional placeholders
%1$s. For compatibility with older versions: positional placeholders (for example,
%1$s,%5s) are processed differently - they do not have quotes added, so they must be supplied with the correct quotes in the query string.echo $wpdb->prepare( 'foo = %1$s', 'a"a' ); // foo = a\"a echo $wpdb->prepare( 'foo = "%1$s"', 'a"a' ); // foo = "a\"a" echo $wpdb->prepare( 'foo = %1s', 'a"a' ); // foo = a\"a echo $wpdb->prepare( 'foo = %s', 'a"a' ); // foo = 'a\"a'
- The sign
% The sign
%in the query string that does not relate to a placeholder should be written as%%.echo $wpdb->prepare( "%foo AND id = %d", 2 ); // User Notice: wpdb::prepare was called incorrectly. echo $wpdb->prepare( "%%foo AND id = %d", 2 ); // %foo AND id = 2
%in LIKE syntaxPercentage wildcard signs
%in LIKE syntax should be specified through a substitution parameter containing the full LIKE string, not directly in the query. Also see wpdb::esc_like().$like = '%'. $wpdb->esc_like( "bar's" ) .'%end'; echo $wpdb->prepare( "foo LIKE %s", $like ); // foo LIKE '{a0d1d}bar\'s{a0d1d}end'
SQL injection
In SQL, there is a concept called "injection" (inserting SQL code into a query). This can be done when dynamic data is passed in the query. For example, a value from an input field is passed in the query, and this field can contain data that will ultimately become part of the SQL query. This way, one can inject into the query and spoil something or simply disrupt the code of the query itself. It looks like this:
$sql = "SELECT * FROM table WHERE id = '$var'";
Now, if var = 2' AND id = (DROP TABLE table2) then the resulting query will look like this:
SELECT * FROM table WHERE id = '2' AND id = (DROP TABLE table2)
Thus, one can inject into the query itself and change it. To prevent this from happening, queries with passed variables must be processed using the prepare() method:
$sql = $wpdb->prepare( "SELECT * FROM table WHERE id = %s", $var );
esc_sql()
In addition to the $wpdb->prepare() method, a query can be sanitized using the function esc_sql(). However, "prepare" is preferable because it fixes some formatting errors.
$name = esc_sql( $name ); $status = esc_sql( $status ); $wpdb->get_var( "SELECT something FROM table WHERE foo = '$name' and status = '$status'" );
IMPORTANT! After esc_sql(), the sanitized string can only be used inside quotes '' or "". That is, it should be written as field = '$value', not field = $value, where $value = esc_sql( $value );
Method of the class: wpdb{}
No Hooks.
Returns
String|null. Sanitized query string, if there is a query to prepare.
Usage
global $wpdb; $wpdb->prepare( $query, ...$args );
- $query(string) (required)
The query string. It can use placeholders:
%d- number%s- string%f- floating point number (floating point number, since version 3.3).
- ...$args(string/number/array)
Variables that will be used to replace the placeholders
%s %d %fin the query string.These variables can be specified as comma-separated (like additional function parameters) or in an array:
$wpdb->prepare( 'query', $param1, $param2 )$wpdb->prepare( 'query', [ $param1, $param2 ] ).
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 ) . '%' );
#6 Clearing values for WHERE IN conditions
This is useful when you have an array of values that you want to pass to the IN condition of the query. The number of array elements can be different, so you have to create the placeholders dynamically:
$in_values = [ 'one', 'two' ]; $in_pholders = implode( ',', array_fill( 0, count( $in_values ), '%s' ) ); $sql = $wpdb->prepare( "SELECT $wpdb->posts WHERE post_type = %s WHERE post_name IN ( $in_pholders )", [ 'page', ...$in_values ] ); echo $sql; // SELECT wp_posts WHERE post_type = 'page' WHERE post_name IN ( 'one','two' )
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. |
| Since 6.2.0 | Added %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. |