Preventing SQL Injection in WordPress: A Deep Dive into $wpdb

Previously in this course, we explored Data Access Objects Pattern for Secure WordPress Development to encapsulate our CRUD operations. While the DAO pattern organizes our code, it is only as secure as the queries inside it. Today, we address the critical task of preventing SQL injection in custom database queries.

SQL Injection (SQLi) remains the most dangerous threat to WordPress sites because it bypasses application logic entirely. When you concatenate variables directly into a SQL string, you are inviting attackers to manipulate your schema, exfiltrate user data, or gain administrative access.

The First Principle: Never Trust Input

In WordPress, the $wpdb object is our interface to the database. Many developers mistakenly believe that escaping strings with esc_sql() is sufficient. It is not. esc_sql() is a helper, not a security boundary.

The only acceptable way to handle dynamic data in a SQL query is through parameterized queries via $wpdb->prepare(). This method separates the SQL statement logic from the data, ensuring the database engine treats variables as literal values rather than executable code.

Using $wpdb->prepare Correctly

The prepare method acts as a wrapper for sprintf(), but with a critical security layer. It supports specific placeholders: %s (string), %d (integer), and %f (float).

Worked Example: Secure Repository Query

Assume we are building a method in our KnowledgeBaseRepository to fetch articles by category ID and status.

PHP
public function get_articles_by_category(int $category_id, string $status): array {
    global $wpdb;

    #6A9955">// We use %d for integers and %s for strings.
    #6A9955">// The query structure remains static; the data is bound later.
    $query = $wpdb->prepare(
        "SELECT * FROM {$wpdb->prefix}kb_articles 
         WHERE category_id = %d 
         AND status = %s",
        $category_id,
        $status
    );

    return $wpdb->get_results($query);
}

Never do this: WHERE category_id = " . $category_id. Even if you think $category_id is an integer, a malicious actor or a bug could inject SQL syntax if it wasn't properly cast or prepared.

Implementing Strict Type Casting

While prepare is essential, your repository layer should enforce strict typing before the query is even built. By using PHP 7.4+ type hinting and casting, you reduce the attack surface before the data reaches the database driver.

1. Type Hinting: Use int, string, bool in method signatures.
2. Explicit Casting: If a value comes from an array (like $_POST), cast it explicitly: (int) $data['id'].
3. Validation: Check against an allow-list for status strings (e.g., in_array($status, ['published', 'draft'])).

Auditing Existing Repository Queries

To secure your plugin, you must audit every instance of $wpdb->query, $wpdb->get_results, and $wpdb->get_var. Use grep or your IDE’s search functionality to find these patterns:

• The "Concatenation" Pattern: Search for " or ' . $variable inside SQL strings. These are high-priority targets for refactoring.
• The "Missing Prepare" Pattern: Any call to $wpdb that does not use prepare() or pass a static string is an immediate security vulnerability.

Hands-on Exercise

1. Open your KnowledgeBaseRepository class.
2. Identify any query that uses concatenation.
3. Refactor one method to use $wpdb->prepare.
4. Add a check to verify the input type (e.g., if you are passing an ID, ensure it is cast to an integer before passing it to prepare).
5. Run a test case using a string where an integer is expected to ensure the prepare method handles the sanitization correctly.

Common Pitfalls

• Forgetting the Placeholder: A common mistake is using %s for an integer. While it often works, it loses the type-safety benefits of the underlying driver. Always match the placeholder to the expected data type.
• Assuming prepare is a magic wand: prepare only handles the values. You cannot use it to parameterize table names or column names. If you need dynamic table names, use esc_sql() on the table name only after validating it against a hardcoded allow-list of known table names.
• Nested Queries: If you are building complex queries, it is tempting to build strings in parts. Avoid this. Build the full prepare statement in one pass to ensure the context remains clear.

Recap

• SQL Injection is prevented by separating SQL code from data.
• $wpdb->prepare is your primary defense; use it for all variable data.
• Type casting adds a second layer of defense by ensuring data conforms to expectations before it hits the database.
• Audit your codebase regularly for concatenation patterns in queries.

By strictly adhering to these patterns, you ensure your plugin remains resilient against common database-level attacks.

Up next: Secure REST API Endpoints — we will apply these same principles to sanitize and validate data coming through our custom API routes.