Where $wpdb->prepare() Protects You, and Where It Cannot

A filter passed two order IDs into a query that had a single %s placeholder. Nothing threw. The page rendered an empty table, the error log stayed quiet, and the report was wrong for a week before anybody compared it against the database. The $wpdb->prepare() call sat on the line above, which is why the change had cleared two code reviews.

No injection happened there. That is the other half of the same problem: prepare() is a string builder with a strict contract, and the contract has edges. Most of the serious wpdb mistakes I find in audit work sit on one of those edges, inside code that trusted a placeholder to do something it never promised.

The failures split in two. Some are silent: prepare() returns an empty string, quotes a number, or drops a value it could not handle, and the query still ran. Others are loud in the wrong place, because a mismatch produces a notice almost no production site logs. Neither is injection, and both give you a wrong answer. The site where this last surfaced ran WordPress 6.7 on PHP 8.2 with WP_DEBUG off, which is the ordinary production configuration and the reason the notice never appeared.

What prepare() actually does with %s, %d and %f

There is no server-side prepared statement underneath. mysqli has them, and WordPress does not use them here. What prepare() does is narrower and easier to reason about once you see it: it walks the query text, rewrites each placeholder into a slot, escapes the matching value through the database connection, and calls vsprintf() to splice the two back together. MySQL receives finished SQL. It never receives a parameter.

Treat all three as sprintf() conversions, because that is what they are, and sprintf() is looser than a type system.

%s is the string case. Prepare rewrites the placeholder to include single quotes, so the value arrives at MySQL as a quoted literal, and the value itself is escaped through the connection first. That second part matters: the escaping rules follow the connection charset, which is why this holds up where hand-rolled addslashes() did not. If you already wrote '%s' in the query, prepare strips the quotes you added and puts its own back, so both spellings produce the same SQL.

%d is the integer case. The value goes through sprintf()'s %d, so it is cast rather than quoted, and a value that is not numeric arrives at the database as zero. %f and %F are the float cases, and both are plain sprintf() conversions. %f is locale aware, so a process locale with a comma decimal separator renders 1.5 as 1,500000; %F is not. Both default to six decimal places, so bind a decimal with %F unless you have checked the locale yourself.

$title = "Bob's first order";

// %s quotes the value and escapes it through the connection.
$wpdb->prepare(
    "SELECT ID FROM {$wpdb->posts} WHERE post_title = %s",
    $title
);
// SELECT ID FROM wp_posts WHERE post_title = 'Bob\'s first order'

// %d casts in PHP and adds no quotes.
$wpdb->prepare(
    "SELECT ID FROM {$wpdb->posts} WHERE ID = %d",
    '41'
);
// SELECT ID FROM wp_posts WHERE ID = 41

Two consequences follow from the string-splicing design. The finished query is text, so it is what gets logged, cached, filtered and compared, and printing it prints the customer data inside it. And only value slots are covered: position in the query, the number of slots, the table, the joins and the ordering are structure, assembled by your code with no escaping applied at all.

A placeholder count mismatch warns; it does not protect

Two checks run before any escaping. If the query string contains no % character at all, prepare() calls _doing_it_wrong() with the message that the query argument must have a placeholder, and then returns the query unchanged. Nothing was escaped, because there was nowhere to escape into. This is the pattern that reads as protection in review: the call is present, the argument is passed, and the value went into the SQL exactly as written.

The second check compares the number of placeholders against the number of arguments. When they disagree, core calls _doing_it_wrong() again and then behaves differently depending on the direction. Fewer arguments than placeholders returns an empty string, which fails closed at the cost of a query that never runs and never explains itself. More arguments than placeholders drops the extras and runs the statement with whichever values lined up positionally. Neither path raises an exception your error handler will see.

// No placeholder, so no escaping. prepare() returns this string as written.
$sql = $wpdb->prepare( "SELECT ID FROM {$wpdb->posts} WHERE post_title = '$title'" );

// One placeholder, three arguments: the extras are dropped and the query runs.
$sql = $wpdb->prepare(
    'SELECT ID FROM ' . $wpdb->posts . ' WHERE post_status = %s',
    'publish',
    $limit,
    $offset
);
// SELECT ID FROM wp_posts WHERE post_status = 'publish'
// $limit and $offset never reach the query, and only a notice records that.

// Two placeholders, one argument: prepare() returns '' and the query does not run.
$sql = $wpdb->prepare( 'SELECT ID FROM %s WHERE ID = %d', $table );

The notice is more useful than it looks. _doing_it_wrong() fires the doing_it_wrong_run action on every call, and only turns the message into a PHP notice when WP_DEBUG is on. That split is why these mistakes reach production: the signal exists, and the default configuration throws it away. Hooking the action and logging wpdb::prepare messages for a week on staging is the cheapest way to find the calls that have been quietly returning empty strings for months.

add_action( 'doing_it_wrong_run', function ( $function_name, $message ) {
    if ( 'wpdb::prepare' === $function_name ) {
        error_log( 'prepare() misuse: ' . $message );
    }
}, 10, 2 );

An identifier is not a value, so the allowlist is still yours

Table names and column names cannot be escaped into safety, because escaping transforms values and an identifier is not a value. Bind a table name with %s and you get 'wp_postmeta' with quotes around it, which MySQL rejects in a FROM clause. It would not have protected you anyway, since the quoted name still came from the request.

%i improves the mechanics without changing the decision. On WordPress 6.2 and newer it backtick-quotes the identifier and doubles any backtick inside it, which stops a name from breaking out of its position. It does not decide whether the name is one you were willing to query. Hand %i a column the request chose and the request is still choosing the column; the quoting only makes the choice syntactically tidy.

So the pattern is a fixed array in your file, a lookup by key, and a default you wrote yourself. The default is the part people skip, and it is the part that matters: falling back to the user's own value when it is not in the list restores the bug you were trying to remove.

$tables = array(
    'posts'    => $wpdb->posts,
    'postmeta' => $wpdb->postmeta,
    'options'  => $wpdb->options,
);

$key = isset( $_GET['source'] ) ? sanitize_key( $_GET['source'] ) : 'posts';

// Resolve to a name this file defines, never to the value that arrived.
$table = isset( $tables[ $key ] ) ? $tables[ $key ] : $tables['posts'];

With $table resolved this way, interpolating it into a query string is honest practice rather than a shortcut, because no request value reaches the SQL. The same reasoning covers column lists: build them from arrays of names, join the ones the request selected, and let the following prepare() call deal with the values in the WHERE clause.

LIKE wildcards and IN lists need work before the query runs

Both of these are cases where the developer did use a placeholder, did get escaping, and still got the wrong query. The placeholder is not the bug. The value handed to it is.

The wildcards in a LIKE pattern are part of the pattern

% and _ mean something to LIKE and nothing to prepare(). If a customer types a percent sign into a search box and you wrap it in wildcards without touching it, the pattern becomes "anything before, anything after", and the search returns the table. A leading wildcard also costs you the index on that column, which turns a lookup into a scan. $wpdb->esc_like() escapes the percent sign, the underscore and the backslash in the user's text. Add your own wildcards after that call, never before, then hand the result to prepare(), which quotes it.

$term = isset( $_GET['s'] ) ? wp_unslash( $_GET['s'] ) : '';

// esc_like() first. Then the wildcards you actually intend.
$pattern = '%' . $wpdb->esc_like( $term ) . '%';

$sql = $wpdb->prepare(
    "SELECT ID FROM {$wpdb->posts} WHERE post_title LIKE %s",
    $pattern
);

Reversing the order is worse than useless. The backslashes esc_like() adds become literal characters in the pattern, so the search starts matching rows whose titles contain a backslash rather than the rows it should.

An IN clause needs one placeholder per element

IN ( %s ) binds a single value. Pass a comma-joined string and MySQL compares the column against that one string, which matches nothing you meant. The shape you want is one placeholder per element, built after you know how many elements there are.

$ids = array_filter( array_map( 'absint', (array) $raw_ids ) );

if ( ! $ids ) {
    return array(); // IN () is a syntax error, so stop before you build it.
}

$placeholders = implode( ', ', array_fill( 0, count( $ids ), '%d' ) );

$sql = $wpdb->prepare(
    "SELECT ID FROM {$wpdb->posts} WHERE ID IN ( $placeholders )",
    $ids
);

$rows = $wpdb->get_col( $sql );

That works because of a quirk worth knowing. When the only argument is an array, prepare() flattens it and treats its elements as the argument list, which is why the canonical IN pattern passes $ids whole. The same flattening is what makes the naive version fail: prepare( 'IN ( %s )', $ids ) with two elements is one placeholder against two arguments, so core warns that the query expected one placeholder and an array of multiple placeholders was sent, and returns nothing at all. With one element it works perfectly, which is how it survives testing and breaks on the first order with two line items.

The other array failure is quieter. A value that is not scalar has no string form, so prepare records an unsupported value type and substitutes an empty string. The statement runs with an empty literal where the value should have been, and nothing in the response says so.

The call was there, and the query was still wrong

Six situations, one shared cause: the placeholder was asked to cover something that is not a value slot. None of the fixes below is a stronger escape. Each one replaces a hole with a decision your code made in advance.

Situation	Naive code	Correct approach
Table or column chosen by the request	ORDER BY {$_GET['sort']}	Resolve through a fixed array, with a default
A number compared against an integer column	WHERE ID = %s	Use %d, so PHP does the cast
A list of values	IN ( %s ), array passed in	One placeholder per element via array_fill()
A wildcard search	LIKE %s with "%$term%"	'%' . $wpdb->esc_like( $term ) . '%'
A query with no placeholder at all	prepare( "… = '$title'" )	Nothing was escaped; fix the query first
A decimal value	%f	%F, so the locale cannot change the separator

Read the table as a grep list rather than a reference: each row is a string you can search for in a plugin directory in a few minutes, and the third column is the shape the answer takes.

prepare() cannot protect a query whose shape is attacker-controlled

Everything above gathers into one limit. A query has a shape: which table, which joins, which columns are compared, how many comparisons there are, and in what order the rows come back. A placeholder covers a value inside that shape. When the request decides the shape, there is nothing for the placeholder to protect, because the attacker is not trying to become a value. They are choosing structure.

A dynamic ORDER BY is the ordinary version of this. Escaping the sort parameter does not get you halfway, because ORDER BY takes a column reference rather than a string. The correct move is to resolve the parameter against a fixed map and sort by the column the map names. The same applies to a direction flag, a table choice, a column list in SELECT, and any LIMIT assembled from more than a number.

$columns = array( 'date' => 'post_date', 'title' => 'post_title' );

$sort   = isset( $_GET['sort'] ) ? sanitize_key( $_GET['sort'] ) : 'date';
$column = isset( $columns[ $sort ] ) ? $columns[ $sort ] : $columns['date'];

$direction = ( isset( $_GET['dir'] ) && 'asc' === strtolower( $_GET['dir'] ) ) ? 'ASC' : 'DESC';

// The two interpolated fragments come from literals in this file.
// Only the values pass through prepare().
$sql = $wpdb->prepare(
    "SELECT ID FROM {$wpdb->posts} WHERE post_status = %s ORDER BY {$column} {$direction} LIMIT %d",
    'publish',
    50
);

There is a version of this that still bites after the allowlist. A resolved identifier is safe from injection and not safe from being useful to an attacker: letting the request choose between sortable columns tells them which columns exist, and pairing a sort parameter with a search parameter can produce result patterns that reveal more than the interface intended. That is a smaller problem than injection and a real one, and the answer is to sort only by the columns the interface actually offers rather than every column the table happens to have.

The broader limit: prepare() is a tool for a query you already decided to run. If the request decides which query runs, escaping inside that query cannot help, because the decision happened one level up, in the code that chose the branch.

What to do on Monday

1. Grep for $wpdb->prepare( and open every call. Any call whose query string has no percent sign is decoration, and the value beside it went in unescaped.
2. Grep for ORDER BY, GROUP BY and table names built from a variable. Each one needs a fixed array and a default, not a stronger escape.
3. Grep for LIKE and confirm esc_like() runs on the search term before prepare(), with the wildcards added afterwards.
4. Grep for IN ( and confirm the placeholder count is computed from the array, with an early return for an empty one.
5. Hook doing_it_wrong_run on staging, log the wpdb::prepare messages, and read them before the next release. It takes ten minutes and it finds the calls that fail silently.