Preventing SQL Injection in WordPress: Security Best Practices

By Krasen Slavov WordPress Security & Protection

SQL injection remains one of the most dangerous web vulnerabilities, capable of compromising entire databases, stealing user data, and granting attackers administrative access. In WordPress development, improper database queries are a leading cause of plugin and theme vulnerabilities.

This comprehensive guide teaches WordPress developers how to write secure database queries using prepared statements, proper escaping, and validation techniques to prevent SQL injection attacks.

Understanding SQL Injection

What Is SQL Injection?

SQL injection occurs when user input is inserted directly into SQL queries without proper sanitization, allowing attackers to manipulate the query logic.

Vulnerable Code Example:

// DANGEROUS - NEVER DO THIS
$user_id = $_GET['user_id'];
$results = $wpdb->get_results("SELECT * FROM {$wpdb->users} WHERE ID = $user_id");

Attack:

?user_id=1 OR 1=1

This would return ALL users instead of just one.

Real-World Impact

Data Theft:

  • Extract entire user database
  • Steal password hashes
  • Access sensitive customer information

Privilege Escalation:

  • Modify user roles to admin
  • Create backdoor admin accounts

Site Compromise:

  • Delete database tables
  • Inject malicious content
  • Modify wp-options for persistent access

The WordPress $wpdb Class

WordPress provides the $wpdb global object for all database interactions. Using it correctly prevents SQL injection.

Core Principles

1. NEVER concatenate user input into queries 2. ALWAYS use prepared statements 3. VALIDATE input before database operations 4. ESCAPE output when displaying data

Using $wpdb->prepare() Correctly

Prepared Statement Syntax

global $wpdb;

// Secure query using prepared statement
$user_id = intval($_GET['user_id']); // Always validate first
$results = $wpdb->get_results(
    $wpdb->prepare(
        "SELECT * FROM {$wpdb->users} WHERE ID = %d",
        $user_id
    )
);

Placeholders Explained

%d – Integer placeholder

$post_id = $_GET['post_id'];
$post = $wpdb->get_row(
    $wpdb->prepare(
        "SELECT * FROM {$wpdb->posts} WHERE ID = %d",
        $post_id
    )
);

%s – String placeholder

$username = $_POST['username'];
$user = $wpdb->get_row(
    $wpdb->prepare(
        "SELECT * FROM {$wpdb->users} WHERE user_login = %s",
        $username
    )
);

%f – Float placeholder

$price = $_POST['price'];
$products = $wpdb->get_results(
    $wpdb->prepare(
        "SELECT * FROM {$wpdb->prefix}products WHERE price >= %f",
        $price
    )
);

Common prepare() Mistakes

❌ WRONG: Preparing table/column names

// This doesn't work - prepare() doesn't escape identifiers
$table = $_GET['table'];
$wpdb->get_results(
    $wpdb->prepare("SELECT * FROM %s", $table) // UNSAFE!
);

✅ CORRECT: Whitelist approach

$allowed_tables = ['posts', 'users', 'comments'];
$table = $_GET['table'];

if (!in_array($table, $allowed_tables)) {
    wp_die('Invalid table');
}

// Now safe to use
$wpdb->get_results("SELECT * FROM {$wpdb->prefix}{$table}");

❌ WRONG: Using prepare() with already concatenated strings

$sql = "SELECT * FROM {$wpdb->posts} WHERE post_author = " . $author_id;
$wpdb->get_results($wpdb->prepare($sql)); // Too late!

Safe Database Operations

INSERT Operations

Using $wpdb->insert():

// Secure insert method
$wpdb->insert(
    $wpdb->prefix . 'custom_table',
    [
        'user_id'    => absint($_POST['user_id']),
        'title'      => sanitize_text_field($_POST['title']),
        'content'    => wp_kses_post($_POST['content']),
        'created_at' => current_time('mysql')
    ],
    [
        '%d', // user_id format
        '%s', // title format
        '%s', // content format
        '%s'  // created_at format
    ]
);

if ($wpdb->insert_id) {
    echo "Inserted with ID: " . $wpdb->insert_id;
}

UPDATE Operations

Using $wpdb->update():

$updated = $wpdb->update(
    $wpdb->prefix . 'custom_table',
    [
        'status' => sanitize_text_field($_POST['status']),
        'updated_at' => current_time('mysql')
    ],
    [ 'id' => absint($_POST['id']) ], // WHERE clause
    [ '%s', '%s' ], // Format for UPDATE data
    [ '%d' ]        // Format for WHERE clause
);

if (false === $updated) {
    // Error occurred
    error_log($wpdb->last_error);
}

DELETE Operations

Using $wpdb->delete():

$deleted = $wpdb->delete(
    $wpdb->prefix . 'custom_table',
    [ 'id' => absint($_POST['id']) ],
    [ '%d' ]
);

if ($deleted) {
    echo "Deleted $deleted row(s)";
}

SELECT Queries

Using prepare() with get_results():

// Multiple results
$posts = $wpdb->get_results(
    $wpdb->prepare(
        "SELECT * FROM {$wpdb->posts}
        WHERE post_type = %s
        AND post_status = %s
        ORDER BY post_date DESC
        LIMIT %d",
        'post',
        'publish',
        10
    )
);

// Single row
$post = $wpdb->get_row(
    $wpdb->prepare(
        "SELECT * FROM {$wpdb->posts} WHERE ID = %d",
        $post_id
    )
);

// Single variable
$count = $wpdb->get_var(
    $wpdb->prepare(
        "SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_author = %d",
        $author_id
    )
);

// Single column
$post_ids = $wpdb->get_col(
    $wpdb->prepare(
        "SELECT ID FROM {$wpdb->posts} WHERE post_type = %s",
        'page'
    )
);

Input Validation and Sanitization

Validate Before Database

// Validate integer
$user_id = absint($_POST['user_id']); // Forces positive integer

// Validate email
$email = sanitize_email($_POST['email']);
if (!is_email($email)) {
    wp_die('Invalid email address');
}

// Validate URL
$url = esc_url_raw($_POST['url']);

// Sanitize text field
$title = sanitize_text_field($_POST['title']);

// Sanitize textarea (allows newlines)
$description = sanitize_textarea_field($_POST['description']);

// HTML content (allows safe HTML)
$content = wp_kses_post($_POST['content']);

// Alphanumeric only
$slug = sanitize_key($_POST['slug']);

Custom Validation Functions

function validate_username($username) {
    // Only letters, numbers, underscores, hyphens
    if (!preg_match('/^[a-zA-Z0-9_-]+$/', $username)) {
        return false;
    }

    // Length check
    if (strlen($username) < 3 || strlen($username) > 20) {
        return false;
    }

    return sanitize_user($username);
}

// Usage
$username = validate_username($_POST['username']);
if (!$username) {
    wp_die('Invalid username format');
}

LIKE Queries and Wildcards

Using $wpdb->esc_like()

// Search with LIKE query
$search_term = $_GET['search'];

$results = $wpdb->get_results(
    $wpdb->prepare(
        "SELECT * FROM {$wpdb->posts}
        WHERE post_title LIKE %s",
        '%' . $wpdb->esc_like($search_term) . '%'
    )
);

Why esc_like() is necessary:

Without it, users could inject wildcards:

// Malicious input: %
// Would match everything

Handling Dynamic ORDER BY

Whitelist Approach

// UNSAFE - Never do this
$orderby = $_GET['orderby'];
$sql = "SELECT * FROM {$wpdb->posts} ORDER BY $orderby";

// SAFE - Whitelist allowed columns
$orderby = $_GET['orderby'];
$allowed_orderby = [
    'title' => 'post_title',
    'date'  => 'post_date',
    'author' => 'post_author'
];

$orderby_column = isset($allowed_orderby[$orderby])
    ? $allowed_orderby[$orderby]
    : 'post_date';

$order = ($_GET['order'] === 'ASC') ? 'ASC' : 'DESC';

$results = $wpdb->get_results(
    "SELECT * FROM {$wpdb->posts}
    ORDER BY {$orderby_column} {$order}"
);

IN Clause with Multiple Values

Safe Array Handling

// Get posts with specific IDs
$post_ids = $_POST['post_ids']; // Array of IDs

// Sanitize each ID
$post_ids = array_map('absint', $post_ids);

// Create placeholders
$placeholders = implode(',', array_fill(0, count($post_ids), '%d'));

$results = $wpdb->get_results(
    $wpdb->prepare(
        "SELECT * FROM {$wpdb->posts} WHERE ID IN ($placeholders)",
        $post_ids
    )
);

For string arrays:

$categories = $_POST['categories']; // Array of strings

// Sanitize
$categories = array_map('sanitize_text_field', $categories);

// Placeholders
$placeholders = implode(',', array_fill(0, count($categories), '%s'));

$results = $wpdb->get_results(
    $wpdb->prepare(
        "SELECT * FROM {$wpdb->terms} WHERE slug IN ($placeholders)",
        $categories
    )
);

Using WordPress Core Functions

Prefer WP Functions Over Raw SQL

Instead of raw queries, use:

// Get posts
$posts = get_posts([
    'post_type'   => 'product',
    'post_status' => 'publish',
    'author'      => $author_id,
    'numberposts' => 10
]);

// Get users
$users = get_users([
    'role'    => 'subscriber',
    'orderby' => 'registered',
    'number'  => 50
]);

// Get post meta
$value = get_post_meta($post_id, 'custom_field', true);

// WP_Query for complex queries
$query = new WP_Query([
    'post_type' => 'post',
    'meta_query' => [
        [
            'key'     => 'price',
            'value'   => 100,
            'compare' => '>=',
            'type'    => 'NUMERIC'
        ]
    ]
]);

Custom Table Best Practices

Creating Secure Custom Tables

function create_custom_table() {
    global $wpdb;
    $table_name = $wpdb->prefix . 'my_custom_table';
    $charset_collate = $wpdb->get_charset_collate();

    $sql = "CREATE TABLE IF NOT EXISTS $table_name (
        id mediumint(9) NOT NULL AUTO_INCREMENT,
        user_id bigint(20) NOT NULL,
        title varchar(255) NOT NULL,
        content longtext NOT NULL,
        status varchar(20) NOT NULL,
        created_at datetime DEFAULT CURRENT_TIMESTAMP NOT NULL,
        PRIMARY KEY  (id),
        KEY user_id (user_id)
    ) $charset_collate;";

    require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
    dbDelta($sql);
}

Querying Custom Tables Safely

$table_name = $wpdb->prefix . 'my_custom_table';

// Insert
$wpdb->insert(
    $table_name,
    [
        'user_id' => get_current_user_id(),
        'title'   => sanitize_text_field($_POST['title']),
        'content' => wp_kses_post($_POST['content']),
        'status'  => 'pending'
    ],
    ['%d', '%s', '%s', '%s']
);

// Select
$results = $wpdb->get_results(
    $wpdb->prepare(
        "SELECT * FROM $table_name WHERE user_id = %d AND status = %s",
        $user_id,
        'approved'
    )
);

Testing for SQL Injection

Manual Testing

Try these payloads in inputs:

1' OR '1'='1
1; DROP TABLE wp_posts;--
' UNION SELECT NULL, NULL, NULL--
1' AND 1=0 UNION ALL SELECT table_name,2,3 FROM information_schema.tables--

If any return unexpected results or errors, you have SQL injection.

Automated Security Scanning

# WPScan
wpscan --url https://yoursite.com --enumerate vp

# RIPS (PHP security scanner)
# Upload code to https://github.com/ripsscanner/rips

# Psalm with security plugin
composer require --dev vimeo/psalm psalm/plugin-wordpress
psalm --taint-analysis

Common Vulnerable Patterns

Pattern 1: Direct Variable Insertion

// VULNERABLE
$id = $_GET['id'];
$wpdb->query("DELETE FROM {$wpdb->posts} WHERE ID = $id");

// SECURE
$id = absint($_GET['id']);
$wpdb->delete($wpdb->posts, ['ID' => $id], ['%d']);

Pattern 2: String Concatenation

// VULNERABLE
$search = $_POST['search'];
$sql = "SELECT * FROM {$wpdb->posts} WHERE post_title LIKE '%" . $search . "%'";

// SECURE
$search = $_POST['search'];
$results = $wpdb->get_results(
    $wpdb->prepare(
        "SELECT * FROM {$wpdb->posts} WHERE post_title LIKE %s",
        '%' . $wpdb->esc_like($search) . '%'
    )
);

Pattern 3: Unvalidated Array Access

// VULNERABLE
$ids = $_POST['ids']; // "1,2,3 OR 1=1"
$sql = "SELECT * FROM {$wpdb->posts} WHERE ID IN ($ids)";

// SECURE
$ids = array_map('absint', $_POST['ids']);
$placeholders = implode(',', array_fill(0, count($ids), '%d'));
$results = $wpdb->get_results(
    $wpdb->prepare("SELECT * FROM {$wpdb->posts} WHERE ID IN ($placeholders)", $ids)
);

Security Checklist

✅ Use $wpdb->prepare() for all queries with variables ✅ Validate all user input (absint, sanitize_text_field, etc.) ✅ Use $wpdb->insert/update/delete methods ✅ Whitelist table/column names (never from user input) ✅ Use $wpdb->esc_like() for LIKE queries ✅ Prefer WordPress core functions over raw SQL ✅ Test with SQL injection payloads ✅ Regular security audits and code reviews ✅ Use static analysis tools ✅ Keep WordPress and plugins updated

SQL injection is preventable through disciplined coding practices. Always treat user input as hostile, validate rigorously, use prepared statements consistently, and leverage WordPress’s built-in security functions.

  1. SQL Injection (OWASP)
  2. wpdb Class Documentation
  3. Data Validation
  4. Prepared Statements
  5. WordPress Security Handbook

Call to Action

Secure your site with bulletproof backups! Backup Copilot Pro offers automated security audits, malware scanning before backups, and instant recovery—try it free!

Related Articles