The database API: $wpdb and prepared queries

WordPress talks to the database through a single object, $wpdb. Core functions such as get_posts() and update_option() use it behind the scenes, and you can use it directly when no higher-level API fits. Used carelessly, it is the fastest route to SQL injection. Used with prepare(), it is safe.

General SQL and prepared statements are covered in the PHP and MySQL course. This lesson is about the WordPress way.

First, do you need SQL at all?

Before writing a query, check whether an API already does the job:

These APIs run hooks other plugins rely on, use the object cache, and keep working if core changes its schema. Raw SQL skips all of that. Reach for $wpdb when you have your own custom table, or a reporting query that the APIs cannot express efficiently.

Using $wpdb

$wpdb is a global. Declare it inside each function that uses it:

<?php

function myplugin_count_events(): int {
    global $wpdb;

    return (int) $wpdb->get_var(
        $wpdb->prepare(
            "SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_type = %s AND post_status = %s",
            'myplugin_event',
            'publish'
        )
    );
}

Table names and the prefix

Never write wp_posts. Sites can use a different prefix, and on Multisite each site has its own tables. Use the properties instead:

• core tables: $wpdb->posts, $wpdb->postmeta, $wpdb->users, $wpdb->usermeta, $wpdb->options, $wpdb->terms, $wpdb->term_relationships and so on;
• your own tables: $wpdb->prefix . 'myplugin_bookings'.

The read methods

$output can be OBJECT (the default, rows as objects), ARRAY_A (associative arrays) or ARRAY_N (numeric arrays).

prepare: the one rule

Any query that contains a variable goes through $wpdb->prepare(). You write placeholders in the SQL and pass the values separately:

<?php

global $wpdb;

$table = $wpdb->prefix . 'myplugin_bookings';

$bookings = $wpdb->get_results(
    $wpdb->prepare(
        'SELECT id, user_id, guests FROM %i WHERE event_id = %d AND status = %s ORDER BY created_at DESC LIMIT %d',
        $table,
        $event_id,
        'confirmed',
        20
    )
);

prepare() escapes each value for its type and adds quotes around strings. Compare the unsafe version:

<?php

// Never do this: $status comes straight from the request.
$status = $_GET['status'];
$rows   = $wpdb->get_results( "SELECT * FROM {$table} WHERE status = '$status'" );

A request with status=x' OR '1'='1 returns every row; worse inputs can read other tables, including user password hashes.

Rules that catch people out:

• Do not put quotes around placeholders. Write status = %s, not status = '%s'. prepare() adds the quotes.
• Sanitising is not a substitute. sanitize_text_field() does not make a value safe for SQL. Always use prepare(), even for values you have already sanitised.
• Placeholders are for values and identifiers, not keywords. You cannot use a placeholder for ASC or DESC. Choose from an allowlist instead:

<?php

$order = ( isset( $_GET['order'] ) && 'asc' === strtolower( sanitize_key( wp_unslash( $_GET['order'] ) ) ) ) ? 'ASC' : 'DESC';

$sql = $wpdb->prepare(
    "SELECT id, guests FROM %i WHERE event_id = %d ORDER BY created_at {$order}",
    $table,
    $event_id
);

$order can only ever be the literal ASC or DESC, so placing it in the string is safe.

LIKE searches

% and _ are wildcards in LIKE. Escape them in the user's text with esc_like(), add your own wildcards, then pass the whole thing as a %s value:

<?php

$search = sanitize_text_field( wp_unslash( $_GET['s'] ?? '' ) );
$like   = '%' . $wpdb->esc_like( $search ) . '%';

$names = $wpdb->get_col(
    $wpdb->prepare(
        'SELECT name FROM %i WHERE name LIKE %s LIMIT 20',
        $wpdb->prefix . 'myplugin_venues',
        $like
    )
);

IN lists

Build one placeholder per value, then pass the values as an array:

<?php

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

$totals = $wpdb->get_results(
    $wpdb->prepare(
        "SELECT event_id, SUM(guests) AS guests FROM %i WHERE event_id IN ($placeholders) GROUP BY event_id",
        array_merge( array( $table ), $ids )
    )
);

Only the string of %d placeholders is interpolated; every value still goes through prepare(). Check that $ids is not empty first, because IN () is a syntax error.

Writing rows with the helpers

For INSERT, UPDATE and DELETE, the helper methods prepare the query for you. Pass data as arrays and a format for each value:

<?php

$inserted = $wpdb->insert(
    $table,
    array(
        'event_id'   => $event_id,
        'user_id'    => get_current_user_id(),
        'guests'     => $guests,
        'status'     => 'confirmed',
        'created_at' => current_time( 'mysql', true ),
    ),
    array( '%d', '%d', '%d', '%s', '%s' )
);

if ( false === $inserted ) {
    error_log( 'Booking insert failed: ' . $wpdb->last_error );
} else {
    $booking_id = (int) $wpdb->insert_id;
}

$wpdb->update(
    $table,
    array( 'status' => 'cancelled' ),        // SET
    array( 'id' => $booking_id ),            // WHERE
    array( '%s' ),
    array( '%d' )
);

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

current_time( 'mysql', true ) returns the current time in UTC in MySQL's DATETIME format. Each method returns the number of rows affected, or false on error, so compare with false === rather than a loose check, because 0 rows is not an error.

The values are escaped, but the code calling them still needs the usual checks: a capability, a nonce, and validated input. $wpdb protects the query, not the action.

Creating a custom table

Custom tables suit structured data you query by several columns, at a volume where post meta would be slow: bookings, logs, analytics. Create them on activation with dbDelta(), which creates the table or alters it to match:

<?php
/**
 * Plugin Name: My Plugin Bookings
 * Text Domain: myplugin
 */

defined( 'ABSPATH' ) || exit;

const MYPLUGIN_DB_VERSION = '1.0';

register_activation_hook( __FILE__, 'myplugin_install_tables' );

function myplugin_install_tables(): void {
    global $wpdb;

    require_once ABSPATH . 'wp-admin/includes/upgrade.php';

    $table           = $wpdb->prefix . 'myplugin_bookings';
    $charset_collate = $wpdb->get_charset_collate();

    $sql = "CREATE TABLE {$table} (
        id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
        event_id bigint(20) unsigned NOT NULL,
        user_id bigint(20) unsigned NOT NULL,
        guests tinyint(3) unsigned NOT NULL DEFAULT 0,
        status varchar(20) NOT NULL DEFAULT 'confirmed',
        created_at datetime NOT NULL,
        PRIMARY KEY  (id),
        KEY event_id (event_id),
        KEY user_id (user_id)
    ) {$charset_collate};";

    dbDelta( $sql );

    update_option( 'myplugin_db_version', MYPLUGIN_DB_VERSION, false );
}

dbDelta() is strict about formatting:

• each column on its own line;
• two spaces between PRIMARY KEY and the column list;
• use KEY, not INDEX, and give each key a name.

Store a schema version option. When a later plugin version changes the table, compare the stored version on plugins_loaded and run the install function again, because activation hooks do not run on updates. Add indexes for the columns you filter and join on, as above. Drop the table in uninstall.php if the user deletes the plugin.

Caching query results

$wpdb does not cache. If the same expensive query runs on many requests, cache the result with the object cache or a transient, and clear it when the data changes:

<?php

function myplugin_get_guest_total( int $event_id ): int {
    $total = wp_cache_get( 'guests_' . $event_id, 'myplugin_bookings' );

    if ( false === $total ) {
        global $wpdb;

        $total = (int) $wpdb->get_var(
            $wpdb->prepare(
                'SELECT COALESCE(SUM(guests), 0) FROM %i WHERE event_id = %d AND status = %s',
                $wpdb->prefix . 'myplugin_bookings',
                $event_id,
                'confirmed'
            )
        );

        wp_cache_set( 'guests_' . $event_id, $total, 'myplugin_bookings', HOUR_IN_SECONDS );
    }

    return (int) $total;
}

Call wp_cache_delete( 'guests_' . $event_id, 'myplugin_bookings' ) after inserting, updating or deleting a booking. The options and object cache lessons go further.

Debugging queries

• $wpdb->last_error holds the last MySQL error, and $wpdb->last_query the last SQL sent.
• Query Monitor lists every query on the page, with timings and the function that ran it.
• prepare() triggers a "doing it wrong" notice when the number of placeholders and values do not match. Keep WP_DEBUG on locally so you see it.

Summary

• Prefer core APIs; use $wpdb for custom tables and queries the APIs cannot express.
• Use $wpdb->prefix and properties such as $wpdb->posts; never hard-code wp_.
• Every query with a variable goes through $wpdb->prepare() with %s, %d, %f or %i, and placeholders are never quoted.
• Escape LIKE text with esc_like(), allowlist keywords such as ASC, and build one placeholder per value for IN lists.
• Use insert(), update() and delete() with formats, create tables with dbDelta(), and cache expensive results.