Safe custom database queries in WordPress using $wpdb

Home » Coding » Safe custom database queries in WordPress using $wpdb
May 15, 2021 Coding, MySQL
Safe custom database queries in WordPress using $wpdb

Every WordPress installation ships with a global database abstraction object called $wpdb that wraps all database communication in a clean PHP interface. Understanding how to use it correctly is one of the most important security skills a WordPress developer can have, because SQL injection — the vulnerability where user-supplied data is inserted directly into a query string without sanitization — remains one of the most common and most devastating attack types in web applications, appearing consistently in the OWASP Top 10. The mechanics are straightforward: when a WordPress plugin or theme builds a SQL query by concatenating user input directly into the query string, an attacker can submit specially crafted input that escapes the intended string context and injects arbitrary SQL commands. A single vulnerable query can expose every row in the users table, delete entire post collections, or execute system commands on the database server depending on the MySQL user’s privilege level. WordPress’s $wpdb->prepare() method is the correct protection against SQL injection. It uses a printf-style placeholder system where the query template contains typed format markers — %d for integers, %s for strings, %f for floats — and actual values are passed as separate arguments. The method escapes and quotes each value according to its type and the current MySQL connection’s character set, making injection impossible regardless of what characters the input contains. For reads, $wpdb->get_results() returns multiple rows, $wpdb->get_row() returns one row, $wpdb->get_var() returns a single scalar value, and $wpdb->get_col() returns a single column as an array. For writes, $wpdb->insert(), $wpdb->update(), and $wpdb->delete() handle the three modification operations with automatic escaping — no manual prepare call required. The $wpdb->prefix property holds the table prefix dynamically, which is critical on non-standard installations like the one discussed in our guide to WordPress database find and replace where the prefix is not the default wp_. Hardcoding a table prefix in a plugin or theme makes the code fail silently on any site that uses a custom prefix, which also happens to be a security hardening best practice.

Problem: Writing custom SQL queries in WordPress without proper escaping creates SQL injection vulnerabilities.

Solution: Always use $wpdb->prepare() for queries with variable input, and use the helper methods for inserts and updates:

<?php
global $wpdb;

// WRONG — SQL injection risk: never concatenate user input directly
// $results = $wpdb->get_results( "SELECT * FROM {$wpdb->posts} WHERE post_title = '" . $_GET['title'] . "'" );

// CORRECT — use prepare() with typed placeholders
$title   = sanitize_text_field( $_GET['title'] ?? '' );
$results = $wpdb->get_results(
    $wpdb->prepare(
        "SELECT ID, post_title FROM {$wpdb->posts} WHERE post_title = %s AND post_status = %s",
        $title,
        'publish'
    )
);

// Get a single value (e.g. count)
$count = $wpdb->get_var(
    $wpdb->prepare(
        "SELECT COUNT(*) FROM {$wpdb->postmeta} WHERE meta_key = %s AND meta_value = %d",
        'featured',
        1
    )
);

// Safe INSERT using the insert() helper (no prepare() needed)
$wpdb->insert(
    $wpdb->prefix . 'my_custom_table',
    array(
        'user_id'    => get_current_user_id(),
        'action'     => sanitize_key( $action ),
        'created_at' => current_time( 'mysql' ),
    ),
    array( '%d', '%s', '%s' ) // format for each value
);

// Safe UPDATE
$wpdb->update(
    $wpdb->prefix . 'my_custom_table',
    array( 'status' => 'processed' ),      // data to set
    array( 'id' => $record_id ),           // WHERE condition
    array( '%s' ),                         // data formats
    array( '%d' )                          // WHERE formats
);

NOTE: $wpdb->prepare() is not optional for user-supplied input — it is mandatory. Even inputs that have already passed through sanitize_text_field() or other sanitization functions should still go through prepare() before being placed in a query, because sanitization and SQL escaping serve different purposes. Always use $wpdb->prefix or the specific table properties ($wpdb->posts, $wpdb->postmeta, etc.) instead of hardcoded table names so your code works correctly on any WordPress installation regardless of the configured table prefix.

Related posts