$wpdb->prepare and the Queries That Get Sites Hacked

Most WordPress code never writes SQL, and that is a feature. WP_Query, get_posts, get_post_meta and the options API cover an enormous amount of ground and they are safe by construction.

Then a reporting screen needs a GROUP BY that the query API will not express, and you drop to $wpdb, and now you are responsible for something you were not responsible for before.

The rule is short. Every value that came from outside your code goes through a placeholder. Nothing else does, and nothing else can.

What prepare actually does

$wpdb->prepare() takes a query string with placeholders and a list of values, and returns a query string with those values safely inserted, quoted and escaped for the connection in use.

global $wpdb;

$rows = $wpdb->get_results(
    $wpdb->prepare(
        "SELECT id, title FROM {$wpdb->prefix}acme_items
          WHERE status = %s AND author_id = %d
          ORDER BY created_at DESC
          LIMIT %d",
        $status,
        $author_id,
        $limit
    )
);

The placeholders:

• %s a string
• %d an integer
• %f a float
• %i an identifier, such as a table or column name, available in recent versions
• %% a literal percent sign

%d and %f also coerce. A %d placeholder given the string 7 OR 1=1 produces an integer, which is a second layer of protection on top of the escaping.

Note there are no quotes around %s in the query. prepare adds them. Writing '%s' yourself produces doubled quotes and a query that does not do what you meant.

What prepare cannot do

This is the part that gets skipped, and it is where the real mistakes live.

A table or column name is not a value. SQL does not allow an identifier to be parameterized the way a value can be, which is why %i had to be added as a distinct placeholder rather than reusing %s. If you are on a version without %i, an identifier that varies must be validated against an allowlist you wrote:

$allowed = array( 'created_at', 'title', 'status' );
$order_by = in_array( $requested, $allowed, true ) ? $requested : 'created_at';

That is validation, not escaping, and no escaping function can replace it.

ASC and DESC are not values either. Same treatment: a two-item allowlist.

Concatenation defeats it entirely. This is prepared and completely unprotected:

// Wrong. The interpolation happens before prepare ever sees the string.
$wpdb->get_results(
    $wpdb->prepare( "SELECT * FROM t WHERE name = '$name'" )
);

prepare receives a string that already contains the input. There is nothing left for it to do. The presence of the call is what makes this one dangerous: it reads as safe.

One argument means no arguments. prepare with a query and no values is a call that does nothing useful, and recent versions warn about it. If there is no placeholder, there is no value to protect, and you should be asking why the query is going through prepare at all.

Table prefixes and multisite

$wpdb->prefix is the prefix for the current site. $wpdb->base_prefix is the network-wide one. On a multisite install those differ, and hard-coding wp_ is a bug that only appears on the installs where it matters most.

Core tables have properties: $wpdb->posts, $wpdb->postmeta, $wpdb->users, $wpdb->options. Use them rather than composing the names.

The IN clause

There is no placeholder for a list, and this is where people give up and concatenate.

Build the placeholder string from the count, then pass the array:

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

if ( empty( $ids ) ) {
    return array();
}

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

$rows = $wpdb->get_results(
    $wpdb->prepare(
        "SELECT * FROM {$wpdb->prefix}acme_items WHERE id IN ( $placeholders )",
        $ids
    )
);

The $placeholders interpolation is safe because it is built from count(), not from input. It contains nothing but %d and commas. The values still go through prepare.

The early return is not optional: IN () with nothing inside it is a syntax error.

LIKE has its own escaping

% and _ are wildcards inside a LIKE pattern. A user searching for 100% will match far more than they expected unless you escape those characters first.

$term = '%' . $wpdb->esc_like( $search ) . '%';
$rows = $wpdb->get_results(
    $wpdb->prepare(
        "SELECT * FROM {$wpdb->prefix}acme_items WHERE title LIKE %s",
        $term
    )
);

esc_like escapes the wildcards. prepare handles the SQL quoting. Both, in that order, and the surrounding % characters are yours to add deliberately.

The methods that prepare for you

For straightforward writes, do not write SQL at all:

$wpdb->insert( $table, array( 'title' => $title, 'author_id' => $id ),
                       array( '%s', '%d' ) );

$wpdb->update( $table, array( 'status' => $status ),
                       array( 'id' => $id ),
                       array( '%s' ), array( '%d' ) );

$wpdb->delete( $table, array( 'id' => $id ), array( '%d' ) );

These prepare internally. Pass the format arrays anyway. Without them, everything is treated as a string, and you lose the type coercion that %d gives you for free.

Two habits worth more than any single rule

Check the return value. get_results and friends return null or an empty array on failure, and PHP will happily iterate nothing and show an empty screen. $wpdb->last_error holds the message when something went wrong, and reading it during development turns a blank page into a sentence.

Do not audit this by eye. Unprepared interpolation in a query is a mechanical property, and mechanical properties are what static analysis is good at. The WordPress coding standards ruleset flags unprepared queries directly. Reading a diff catches the obvious cases and misses the one where the interpolation happened three lines earlier, which is exactly the argument for making the tooling part of the routine described in the checklist to run before merging code you did not write line by line.

The short version

• Values through placeholders, always. %s, %d, %f.
• Identifiers through %i or through an allowlist. Never through escaping.
• Interpolating before prepare means prepare did nothing.
• IN clauses: build placeholders from the count, pass the array.
• LIKE: esc_like first, then prepare.
• Prefer insert, update, delete with explicit format arrays.