title: SQL Injection Defense and Low-Level $wpdb Security description: >- Deep engineering examination of database security in WordPress 7.0+ (PHP 8.4+): internal mechanics of $wpdb->prepare(), the %i identifier placeholder (WP 6.2+), safe LIKE querying via esc_like, InnoDB transactions, and why unquoted esc_sql() constitutes a critical vulnerability. module: security order: 2 difficulty: advanced estimatedTime: 42 min topics:
SQL Injection (SQLi, CWE-89) remains one of the most catastrophic vulnerability classes in web engineering. Successful exploitation allows an adversary to bypass application business logic, exfiltrate confidential user data, overwrite administrator password hashes in wp_users, implant persistent backdoors, or destroy database clusters entirely.
Within the WordPress core, database communication is encapsulated inside the global singleton class $wpdb (an instance of wpdb). Direct string concatenation of user-controlled variables into raw SQL statements is strictly prohibited. The industry standard for safe query execution is Parameterization via the $wpdb->prepare() method.
However, many engineers introduce severe architectural vulnerabilities through common fallacies:
esc_sql(): The esc_sql() function merely escapes special characters within string literals, but does not wrap values in quotes. If a variable is concatenated into a numeric clause (WHERE id = $id), an attacker supplying 1 OR 1=1 successfully executes an injection, as there are no special characters to escape.%s placeholder automatically encloses its value in single quotes (SELECT * FROM 'wp_posts'), producing invalid MySQL syntax. Modern WordPress provides the dedicated %i (Identifier Placeholder) to safely interpolate dynamic table and column identifiers.LIKE Clauses: Query constructs matching LIKE '%term%' without prior $wpdb->esc_like() escaping allow attackers to supply % and _ wildcards, converting targeted lookups into unindexed Full Table Scans that can exhaust server resources.Visualize executing SQL queries through $wpdb->prepare() as signing a standardized legal agreement with a notary:
%d, %f, %s, %i) is the official printed contract with strictly typed fill-in fields (the "Amount" field %d only physically accommodates integers, the "Client Name" field %s accepts strings enclosed in quotes, and the "Department Identifier" field %i strictly accepts certified identifiers).$wpdb->prepare() method is the notary who validates incoming credentials and transcribes them into the form, enclosing strings in protective quotes and casting numeric inputs to their mathematical primitives."WHERE id = " . $_GET['id']) is akin to signing a blank sheet of paper, allowing the other party to write arbitrary contractual clauses in fine print ("...and transfer all company assets to me: OR 1=1; DROP TABLE wp_users;").INCOMING INPUTS: $_POST['status'], $_POST['limit'], $_POST['table']
│
▼
┌────────────────────────────────────────────────────────────────────────┐
│ TEMPLATE: "SELECT * FROM %i WHERE status = %s LIMIT %d" │
└──────────────────────────────┬─────────────────────────────────────────┘
│ Invoking $wpdb->prepare(..., $table, $status, $limit)
▼
┌────────────────────────────────────────────────────────────────────────┐
│ ARGUMENT PROCESSING PIPELINE: │
│ • %i (Identifier) ──► Wrapped in backticks: `wp_custom_table` │
│ • %s (String) ──► mysqli_real_escape_string + single quotes │
│ • %d (Integer) ──► Forced mathematical cast via intval() │
└──────────────────────────────┬─────────────────────────────────────────┘
│ Safe Resulting SQL
▼
┌────────────────────────────────────────────────────────────────────────┐
│ EXECUTION IN MYSQL: $wpdb->get_results() │
│ SELECT * FROM `wp_custom_table` WHERE status = 'active' LIMIT 20; │
└────────────────────────────────────────────────────────────────────────┘Queries are designed with explicit placeholders: %d (integers), %f (floats), %s (strings), and %i (table and column identifiers).
The prepare() method uses internal vsprintf() routines to enforce strict numeric casting (intval/floatval) and validates argument counts.
For %s placeholders, special characters ( , , , , ', ", ) are escaped and the value is wrapped in single quotes.
For %i placeholders, values are stripped of backticks and safely escaped within backtick delimiters (`table_name`).
The query executes within an ACID-compliant transaction (START TRANSACTION, COMMIT, ROLLBACK) with automated error logging.
Across the Fullstack Agentic Lab infrastructure, $wpdb security standards govern:
WHERE, ORDER BY, and GROUP BY clauses.$wpdb->esc_like() before parameter binding via %s.Examining the security divergence between vulnerable patterns and modern $wpdb standards:
Below is an enterprise production artifact built for WordPress 7.0+ and PHP 8.4+. It implements an Enterprise Audit Telemetry Repository demonstrating:
%d, %s, %f).%i with secondary static allowlist validation.$wpdb->esc_like().<?php
/**
* Plugin Name: Fullstack Agentic Lab — Enterprise Database Repository
* Description: Hardened $wpdb architecture: %i placeholder, InnoDB transactions, and SQLi prevention.
* Version: 3.0.0
* Requires at least: 6.8
* Requires PHP: 8.4
* Author: Artur Yusupov (Fullstack Agentic Lab)
*/
declare(strict_types=1);
namespace AgenticLab\Security\Database;
use RuntimeException;
use wpdb;
defined('ABSPATH') || exit;
final readonly class TelemetryLogRepository
{
/**
* Strict table identifier allowlist to prevent table substitution attacks.
*/
private const array ALLOWED_TABLES = [
'agentic_telemetry_logs',
'agentic_audit_events',
'agentic_performance_benchmarks',
];
public function __construct(
private wpdb $db
) {}
/**
* Records an audit event within a secure InnoDB transaction.
*
* @param array{userId: int, eventName: string, latencyMs: float, payload: array<string, mixed>} $eventData
*/
public function recordEvent(string $tableName, array $eventData): int
{
$sanitizedTable = $this->assertValidTable($tableName);
// Begin transaction to enforce ACID atomicity
$this->db->query('START TRANSACTION');
try {
$fullTableName = $this->db->prefix . $sanitizedTable;
// CRITICAL: Use %i (WP 6.2+) for table identifiers!
// Scalar bindings: %d (integers), %s (strings), %f (floats)
$query = $this->db->prepare(
'INSERT INTO %i (user_id, event_name, latency_ms, payload_json, created_at_gmt)
VALUES (%d, %s, %f, %s, %s)',
$fullTableName,
$eventData['userId'],
$eventData['eventName'],
$eventData['latencyMs'],
wp_json_encode($eventData['payload'], JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE),
gmdate('Y-m-d H:i:s')
);
$result = $this->db->query($query);
if ($result === false) {
throw new RuntimeException("SQL execution failure: {$this->db->last_error}");
}
$insertedId = (int) $this->db->insert_id;
// Commit atomic transaction
$this->db->query('COMMIT');
return $insertedId;
} catch (\Throwable $e) {
// Immediate rollback on any exception
$this->db->query('ROLLBACK');
throw new RuntimeException("Transaction aborted: {$e->getMessage()}", 0, $e);
}
}
/**
* Substring event query using safe LIKE wildcard escaping and Keyset pagination.
*
* @return array<int, array<string, mixed>>
*/
public function searchEvents(
string $tableName,
string $searchTerm,
int $minUserId,
int $lastSeenId = 0,
int $limit = 50
): array {
$sanitizedTable = $this->assertValidTable($tableName);
$fullTableName = $this->db->prefix . $sanitizedTable;
// 1. CRITICAL: Escape LIKE wildcards (% and _) prior to interpolation!
$escapedSearch = $this->db->esc_like($searchTerm);
$likeArgument = '%' . $escapedSearch . '%';
// 2. High-performance Keyset cursor pagination instead of slow LIMIT offset
$query = $this->db->prepare(
'SELECT id, user_id, event_name, latency_ms, created_at_gmt
FROM %i
WHERE event_name LIKE %s
AND user_id >= %d
AND id > %d
ORDER BY id ASC
LIMIT %d',
$fullTableName,
$likeArgument,
$minUserId,
$lastSeenId,
min($limit, 100)
);
/** @var array<int, array<string, mixed>>|null $results */
$results = $this->db->get_results($query, ARRAY_A);
return $results ?: [];
}
/**
* Validates dynamic table names against an immutable allowlist.
*/
private function assertValidTable(string $table): string
{
$clean = sanitize_key($table);
if (!in_array($clean, self::ALLOWED_TABLES, true)) {
throw new RuntimeException("Unauthorized table access attempt: {$table}");
}
return $clean;
}
}
Map parameters to strict formats: %d for primary keys and counts, %f for financial values, %s for strings, and %i for identifiers.
When table or column names are selected dynamically, verify them against an immutable class constant allowlist.
For LIKE queries, sanitize search terms through $wpdb->esc_like() before attaching leading and trailing percentage signs.
Enclose mutating sequences within START TRANSACTION / COMMIT blocks, monitor $wpdb->last_error, and execute ROLLBACK on failure.