Prepared Statements and Their Limits in wpdb

You add $wpdb->prepare() to a query, the plugin passes review, and six months later a report comes in that a customer can read other customers' orders. The prepared call is still there. It was doing exactly what it was asked to do, and it never covered the part of the query the attacker used.

The name is the source of most of the confusion. A prepared statement in PDO or MySQLi sends the query and the data as two separate things, so the server parses the statement knowing that a marker is a value and can never be syntax. WordPress does not do that. prepare() is a formatting function that runs in PHP, substitutes your arguments into the query string, and hands the finished string to mysqli_query(). There are no bound parameters anywhere in the path.

That distinction matters because a formatter can only help where a value is allowed to appear. Table names, column names and a sort direction are structure. A placeholder for them would be meaningless to a function that escapes values, which is why %s cannot be used for a column and why WordPress needed a separate placeholder for identifiers.

Every placeholder is substituted before the SQL is sent

Start with the part that is easy to get wrong. On a lab table holding five orders, this query is safe and the substitution is visible in the finished string:

global $wpdb;

$reference = 'ORD-1001';
$sql = $wpdb->prepare(
    "SELECT id, reference, customer FROM {$wpdb->prefix}m4_orders WHERE reference = %s",
    $reference
);

SELECT id, reference, customer FROM wp_m4_orders WHERE reference = 'ORD-1001'

The quotes around ORD-1001 were not in the query you wrote. They were added by prepare(), because a single %s with no format specifier is treated as a string value and quoted. The quote characters the reader supplies are escaped first: pass x' OR '1'='1 and the result is 'x\' OR \'1\'=\'1', which MySQL reads as one string literal containing an apostrophe. That is the behaviour most people mean when they say WordPress escapes the value for you.

The other three placeholders exist so that you do not have to guess what MySQL will do with a string. %d casts to an integer. Pass '12abc' to %d and the finished string contains 12 with no quotes. %f casts to a float and formats it with six decimal places, so '1e3' becomes 1000.000000, again unquoted.

One exception is worth memorising because it is documented and easy to trip over. A numbered placeholder is not quoted for you. The manual page for wpdb::prepare() says that numbered or formatted placeholders such as %1$s and %5s are left as they are for backward compatibility. So this produces invalid SQL:

$sql = $wpdb->prepare(
    "SELECT id FROM {$wpdb->prefix}m4_orders WHERE reference = %1\$s",
    'ORD-1002'
);

SELECT id FROM wp_m4_orders WHERE reference = ORD-1002

If you need a numbered placeholder, you supply the quotes yourself. The plain %s form needs nothing.

A placeholder binds a value, and only a value

SQL has two kinds of slot. A value slot is where the grammar expects an expression: somewhere a string or a number could legally stand. A structure slot is where the grammar expects a name or a keyword: after FROM, after ORDER BY, and the ASC or DESC that follows a sort column.

Escaping answers the question "how do I make this text into a value". It has no answer for "how do I make this text into a column name", because the correct output for a value is a quoted literal and the correct output for a column name is a backtick identifier, and the two are not interchangeable. Hand an escaped value to a structure slot and MySQL does not complain. It reads the quoted literal in that position as a constant expression, which is legal almost everywhere, and carries on.

That is the failure mode to internalise. There is no error, no log line and no exception. The query returns the wrong rows, or the same rows in an arbitrary order, and everything downstream looks like it works.

An ORDER BY built from %s is still an injection you can reach

Take a sort control that passes its request value into a prepared statement. Both queries below are the real output of prepare() against the lab table:

$orderby = $_GET['orderby'] ?? 'reference';
$sql = $wpdb->prepare(
    "SELECT id, reference FROM {$wpdb->prefix}m4_orders ORDER BY %s",
    $orderby
);

SELECT id, reference FROM wp_m4_orders ORDER BY 'reference'

MySQL accepts that statement. It sorts by the constant string 'reference', which is the same value for every row, so the ordering is whatever the storage engine returns. The page looks fine. Now pass the sort direction from the same request, and let the reader supply the string that used to be appended to the query:

// The query as a maintainer would write it after a review that said
// "use prepare() for request values".
$orderby = $_GET['orderby'];
$sql = "SELECT id, reference FROM {$wpdb->prefix}m4_orders ORDER BY " . esc_sql( $orderby );

With orderby set to id, (CASE WHEN (SELECT COUNT(*) FROM wp_users) > 0 THEN id ELSE reference END) DESC, the finished query is a valid statement and MySQL executes the subquery:

SELECT id, reference FROM wp_m4_orders ORDER BY id, (CASE WHEN (SELECT COUNT(*) FROM wp_users) > 0 THEN id ELSE reference END) DESC

rows: 5  error: (none)
  1 ORD-1001
  2 ORD-1002
  3 ORD-1003
  4 ORD-1004
  5 ORD-1005

Five rows came back and no error was logged. The attacker learned that wp_users has at least one row, and by changing the condition can walk the rest of the schema and the data one bit at a time. Nothing here is exotic. The subquery is legal because ORDER BY accepts expressions, and esc_sql() is irrelevant because the payload contains no quote characters to escape.

%i escapes as an identifier, which is a different kind of safety

WordPress 6.2 added %i for identifiers. It quotes the argument with backticks and doubles any backtick inside it, so the value in that position can only ever be read as one name. The plugin-facing check for it is $wpdb->has_cap( 'identifier_placeholders' ), which returns true here.

The same payload through %i:

$sql = $wpdb->prepare(
    "SELECT id, reference FROM {$wpdb->prefix}m4_orders ORDER BY %i",
    $orderby
);

SELECT id, reference FROM wp_m4_orders ORDER BY `id, (CASE WHEN (SELECT COUNT(*) FROM wp_users) > 0 THEN id ELSE reference END) DESC`

WordPress database error Unknown column 'id, (CASE WHEN (SELECT COUNT(*) FROM wp_users) > 0 THEN id ELSE reference END) DESC' in 'order clause' for query ...

MySQL looked for a column whose name contains the whole payload, did not find one, and refused the query. The value the reader controls is now a name, and a name is a thing the schema either has or does not have.

Two limits come with %i. It escapes, it does not validate: the payload above is still visible in the error message, so a site that prints database errors to the browser now leaks the attacker's own input back to them, which is harmless, and would leak real column names in other cases. And a dotted name is quoted as one identifier, so %i with wp_m4_orders.total asks for a column called wp_m4_orders.total and fails. To reach a qualified column you write %i.%i and pass the parts separately. Neither limit matters if the argument came from an allowlist in the first place, which is where the next lesson goes.

prepare() reports misuse and then does something anyway

Three of the mistakes above produce a notice rather than an exception, and it is worth knowing which, because in production the notice is frequently the only signal you get.

The first row is the one that catches people who wrap an already-safe query in prepare() to be tidy. It returns the query you passed and adds a debug notice, so the call has no effect except noise. That is the intended behaviour: a prepared query with no placeholders means either the query is fully static, in which case the call is unnecessary, or something was supposed to be interpolated and was not. Both are worth knowing about at development time and neither should be discovered in production for the first time.

The rows about counts and value types are the reason to run with WP_DEBUG enabled on a staging copy. prepare() returning an empty string does not throw, and a caller that ignores the return value will run an empty query against the database and treat the empty result as "no records found".

One last property to keep in mind: the escape that prepare() performs is the same mysqli_real_escape_string() that esc_sql() uses. Its correctness depends on the session's SQL mode, because under NO_BACKSLASH_ESCAPES the backslash before a quote stops being an escape character. In practice prepare() survives that mode and a hand-quoted string does not, but the reason is not that the escaping is better. It is that prepare() places the quote characters itself, so there is no mismatch between what the developer quoted and what the session thinks a quote means. The next lesson shows the two queries side by side.

add_shortcode( 'm4_orders', function ( $atts ) {
    global $wpdb;
    $atts = shortcode_atts( array( 'sort' => 'reference' ), $atts );
    $sql  = $wpdb->prepare(
        "SELECT id, reference FROM {$wpdb->prefix}m4_orders ORDER BY %s DESC",
        $atts['sort']
    );
    // ... render the rows
} );

add_shortcode( 'm4_orders', function ( $atts ) {
    global $wpdb;

    $atts = shortcode_atts( array( 'sort' => 'reference' ), $atts );

    $allowed = array( 'reference', 'total', 'customer', 'status' );
    $column  = in_array( $atts['sort'], $allowed, true ) ? $atts['sort'] : 'reference';

    $sql = $wpdb->prepare(
        "SELECT id, reference FROM {$wpdb->prefix}m4_orders ORDER BY %i DESC",
        $column
    );
    // ... render the rows
} );