Run safe custom database queries in WordPress using wpdb

Home » Coding » Run safe custom database queries in WordPress using wpdb
June 20, 2022 Coding, MySQL
Run safe custom database queries in WordPress using wpdb

WordPress provides the global $wpdb object as the approved interface for all custom database queries. It wraps the MySQL connection, handles character encoding, logs query errors in debug mode, and most importantly provides the prepare() method that prevents SQL injection by separating query structure from data values. Using raw mysqli_query() or building SQL strings with string concatenation in WordPress code is a security anti-pattern — $wpdb is the correct tool for every custom query. The four most commonly needed methods are: get_results() for SELECT queries returning multiple rows, get_row() for a single row, get_var() for a single value, and query() for INSERT, UPDATE, or DELETE when you do not need the return value. For INSERT and UPDATE operations that map directly to a table row, $wpdb->insert() and $wpdb->update() accept an associative array of column-to-value pairs and a format array, handling escaping internally without needing prepare(). The prepare() method uses %s for strings, %d for integers, and %f for floats as placeholders — identical syntax to sprintf(). A common mistake is passing user input directly into a LIKE clause — even with prepare(), you must escape the % and _ wildcard characters in the search term first using $wpdb->esc_like() before wrapping it in prepare(). The $wpdb->prefix property always contains the correct table prefix, so queries work on any WordPress installation regardless of the configured prefix. Review this alongside the full-text search guide for advanced query patterns.

Problem: You need to run custom SELECT, INSERT, and UPDATE queries in WordPress against both core and custom tables without risking SQL injection from user-supplied values.

Solution: Always use $wpdb->prepare() for queries with external data, and $wpdb->insert() or $wpdb->update() for write operations:

global $wpdb;

// SELECT multiple rows — returns array of objects
$posts = $wpdb->get_results(
    $wpdb->prepare(
        "SELECT ID, post_title FROM {$wpdb->posts}
         WHERE post_status = %s AND post_type = %s
         ORDER BY post_date DESC LIMIT %d",
        'publish', 'post', 10
    )
);

// SELECT a single value (e.g. count)
$count = $wpdb->get_var(
    $wpdb->prepare(
        "SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_author = %d AND post_status = %s",
        get_current_user_id(), 'publish'
    )
);

// LIKE search — escape wildcards FIRST, then prepare
$search_term = $wpdb->esc_like( $_GET['s'] ?? '' );
$results = $wpdb->get_results(
    $wpdb->prepare(
        "SELECT ID, post_title FROM {$wpdb->posts}
         WHERE post_title LIKE %s AND post_status = %s",
        '%' . $search_term . '%', 'publish'
    )
);

// INSERT a row — no prepare() needed, $wpdb->insert() escapes internally
$wpdb->insert(
    $wpdb->prefix . 'ha_custom_table',
    [
        'user_id'    => get_current_user_id(),
        'event_name' => sanitize_text_field( $event ),
        'created_at' => current_time( 'mysql' ),
    ],
    [ '%d', '%s', '%s' ]  // Format array matching values
);
$new_id = $wpdb->insert_id; // ID of the inserted row

// UPDATE a row
$wpdb->update(
    $wpdb->prefix . 'ha_custom_table',
    [ 'event_name' => sanitize_text_field( $new_name ) ],  // Data
    [ 'id'         => absint( $row_id ) ],                  // WHERE
    [ '%s' ],                                               // Data format
    [ '%d' ]                                                // WHERE format
);

// DELETE
$wpdb->delete(
    $wpdb->prefix . 'ha_custom_table',
    [ 'user_id' => get_current_user_id() ],
    [ '%d' ]
);

NOTE: $wpdb->prepare() was updated in WordPress 5.3 to require at least one placeholder — calling it with a query that has no placeholders triggers a _doing_it_wrong() notice. For static queries with no external data, pass the SQL string directly to get_results() without prepare(). Always check $wpdb->last_error after a query during development to catch silent MySQL errors, or enable WP_DEBUG which causes $wpdb to output query errors automatically. Never use $wpdb->show_errors() in production as it prints raw SQL errors to the page.

Related posts