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

1295
1296
1297
1298
1299
1300
1301
1302
1303
1304
1305
1306
1307
1308
1309
1310
1311
1312
1313
1314
1315
1316
1317
1318
1319
1320
1321
1322
1323
1324
1325
1326
1327
1328
1329
1330
1331
1332
1333
1334
1335
1336
1337
1338
1339
1340
1341
1342
1343
1344
1345
1346
1347
1348
1349
1350
1351
1352
1353
1354
1355
1356
1357
1358
1359
1360
1361
1362
1363
1364
1365
1366
1367
1368
1369
1370
1371
1372
1373
1374
1375
1376
1377
1378
1379
1380
1381
1382
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 1 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:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    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 2 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:

    1
    2
    3
    4
    $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 ) );
  3. Skip to note 3 content
    Contributed by macbookandrew

    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:

    1
    2
    3
    4
    5
    6
    // 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:

    1
    2
    3
    4
    5
    6
    7
    // 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
    );
  4. Skip to note 4 content
    Contributed by wpfanatico.com

    Example: Simple update with prepare (one parameter)

    CASE: Update a column value Where ID column = 12

    1
    2
    3
    4
    5
    $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));

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