Preventing SQL Injection

Every query containing a variable goes through $wpdb->prepare; insert, update and delete escape their values for you.

Syntax$wpdb->prepare( string $query, mixed ...$args ): string|null

$wpdb sends whatever string you hand it. It does not parse your SQL and it has no idea which parts came from a request. Anything variable must go through wpdb::prepare(), which is the whole of WordPress's defence against injection.

prepare

$wpdb->prepare( $query, ...$args ) supports %d (integer), %f (float), %s (string), %i (identifier — table and column names, added in WordPress 6.2) and %% for a literal percent sign. Two rules trip people up. Leave every placeholder unquoted: prepare() adds the single quotes around %s and the backticks around %i itself, while %d and %f are cast and left unquoted. And pass a whole LIKE pattern as an argument rather than writing % wildcards into the SQL, escaping the user's part with $wpdb->esc_like().

The methods that escape for you

insert( $table, $data, $format = null ), update( $table, $data, $where, $format = null, $where_format = null ) and delete( $table, $where, $where_format = null ) take raw column => value arrays and escape the values themselves, so never pre-escape data going into them. They return the affected row count or false; update() returning 0 means nothing changed, not an error, so test with false ===.

What prepare cannot do

get_var(), get_row(), get_col(), get_results() and query() prepare nothing — the safe shape is always $wpdb->get_results( $wpdb->prepare( ... ) ). And %i covers identifiers, but an ORDER BY direction or a comparison operator still has to be checked against an allowlist in PHP.

Example

Example · php
<?php
global $wpdb;

$table = $wpdb->prefix . 'acme_signups'; // always from $wpdb->prefix, never a literal

// Placeholders stay unquoted — prepare() adds the quotes for %s itself.
$rows = $wpdb->get_results(
    $wpdb->prepare(
        "SELECT id, email, created_at
           FROM {$table}
          WHERE event_id = %d
            AND status   = %s
       ORDER BY created_at DESC
          LIMIT %d",
        $event_id,
        $status,
        $limit
    )
);

// LIKE: escape the user's part, then pass the complete pattern as one argument.
// Writing "LIKE '%%{$term}%%'" straight into the SQL is the classic injection.
$pattern = '%' . $wpdb->esc_like( $term ) . '%';
$emails  = $wpdb->get_col(
    $wpdb->prepare( "SELECT email FROM {$table} WHERE email LIKE %s", $pattern )
);

// insert() escapes the values itself — hand it raw data, not prepared strings.
$wpdb->insert(
    $table,
    array(
        'event_id'   => $event_id,
        'email'      => $email,
        'created_at' => current_time( 'mysql' ),
    ),
    array( '%d', '%s', '%s' ) // formats, in the same order as $data
);

$new_id = $wpdb->insert_id;

// update() returns 0 when the row already matched. Only false is an error.
$result = $wpdb->update(
    $table,
    array( 'status' => 'confirmed' ),
    array( 'id' => $new_id ),
    array( '%s' ),
    array( '%d' )
);

if ( false === $result ) {
    // real database error
}

When to use it

  • Querying a custom plugin table from an admin screen with user-supplied filters
  • Adding a search box to a list table without opening the database to injection
  • Auditing an inherited plugin for interpolated variables inside $wpdb calls
  • Writing a posts_where or posts_join filter that appends a condition to WP_Query

More examples

Dynamic identifiers and sort order

%i, added in WordPress 6.2, parameterises a table or column name — before that there was no safe way to do it and plugins interpolated the value. What %i still cannot cover is anything that is not an identifier: ASC/DESC, operators, and the number of items in an IN list. Those come from an allowlist or a generated placeholder string. This is the pattern for a sortable, filterable admin table.

Example · php
<?php
global $wpdb;
$table = $wpdb->prefix . 'acme_signups';

// Column name: %i since WordPress 6.2. Still allowlist it — %i quotes the
// identifier but will happily quote a column that does not exist.
$sortable = array( 'created_at', 'email', 'status' );
$orderby  = in_array( $_GET['orderby'] ?? '', $sortable, true ) ? $_GET['orderby'] : 'created_at';

// Direction is not an identifier and has no placeholder. Allowlist only.
$order = ( 'asc' === strtolower( $_GET['order'] ?? '' ) ) ? 'ASC' : 'DESC';

$rows = $wpdb->get_results(
    $wpdb->prepare(
        "SELECT * FROM {$table} WHERE event_id = %d ORDER BY %i {$order} LIMIT %d",
        $event_id,
        $orderby,
        20
    )
);

// IN ( ... ): build one placeholder per value, then splat the array.
$ids          = array_map( 'absint', (array) $_GET['ids'] );
$placeholders = implode( ',', array_fill( 0, count( $ids ), '%d' ) );

$found = $wpdb->get_results(
    $wpdb->prepare( "SELECT * FROM {$table} WHERE id IN ({$placeholders})", ...$ids )
);

The three ways to get this wrong

Each of these appears in real plugins. The first is straightforward injection. The second is subtler: absint() makes that one line safe today, but the safety is invisible at the query and disappears the moment someone changes the variable's source. The third is the LIKE mistake — the wildcards are written into the SQL, so the user's term is interpolated raw. Treat any query string built with . or {} around a variable as a finding.

Example · php
<?php
global $wpdb;
$table = $wpdb->prefix . 'acme_signups';

// 1. Straight interpolation. email=x' OR '1'='1 dumps the table.
$wpdb->get_results( "SELECT * FROM {$table} WHERE email = '{$_GET['email']}'" );

// 2. "It's an int, so it's fine." It is fine until the variable moves.
$id = absint( $_GET['id'] );
$wpdb->get_row( "SELECT * FROM {$table} WHERE id = {$id}" );

// 3. Wildcards written into the SQL, term interpolated raw.
$wpdb->get_col( "SELECT email FROM {$table} WHERE email LIKE '%{$term}%'" );

// --- The same three, correctly ---
$wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$table} WHERE email = %s", $_GET['email'] ) );
$wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$table} WHERE id = %d", $_GET['id'] ) );
$wpdb->get_col( $wpdb->prepare(
    "SELECT email FROM {$table} WHERE email LIKE %s",
    '%' . $wpdb->esc_like( $term ) . '%'
) );

Extending a WP_Query clause safely

When you hook posts_where or posts_join to add SQL to the main query, you are appending a raw string to a query WordPress built — nothing prepares it for you. Prepare the fragment before you concatenate it, and return the string unchanged on every path that does not apply, otherwise you silently break every other query on the site. Use this when a meta_query cannot express the condition.

Example · php
<?php
add_filter( 'posts_where', function ( $where, WP_Query $query ) {
    global $wpdb;

    // Narrow the hook to exactly the query you mean.
    if ( is_admin() || ! $query->is_main_query() || ! $query->get( 'acme_since' ) ) {
        return $where; // untouched on every other query
    }

    $since = $query->get( 'acme_since' ); // e.g. '2026-01-01 00:00:00'

    // The fragment is prepared before it is appended.
    $where .= $wpdb->prepare( " AND {$wpdb->posts}.post_modified_gmt > %s ", $since );

    return $where;
}, 10, 2 );

// Usage:
// $q = new WP_Query( array( 'post_type' => 'acme_event', 'acme_since' => '2026-01-01 00:00:00' ) );

Related

wp wpdbwp wpdb preparewp custom tableswp db schemawp security model

Discussion

  • Be the first to comment on this lesson.