Prevent SQL Injection and XSS in WordPress Plugins with Prepared Statements and Escaping

Home » Security » Prevent SQL Injection and XSS in WordPress Plugins with Prepared Statements and Escaping
January 5, 2024 Security
Prevent SQL Injection and XSS in WordPress Plugins with Prepared Statements and Escaping

SQL injection and cross-site scripting (XSS) are the two most common vulnerability classes in WordPress plugins — SQL injection exploits unsanitized input concatenated into database queries, while XSS exploits unescaped output rendered as HTML in the browser. WordPress provides a complete set of functions for both prevention layers: $wpdb->prepare() for parameterized SQL queries, and a family of context-specific escaping functions (esc_html(), esc_attr(), esc_url(), esc_js(), wp_kses()) for output. $wpdb->prepare() accepts a query string with %s, %d, and %f placeholders and a variadic list of values — it quotes and escapes string values, casts integers and floats, and cannot be bypassed by injection payloads in the values. Never use string interpolation or concatenation to build SQL queries with user input — even after sanitizing the input, a missed edge case can still allow injection. Output escaping must happen at the point of output, not at the point of input — sanitizing on input and echoing raw values later is unsafe because the sanitized value may still contain characters that are dangerous in an HTML context. The escaping function must match the HTML context: esc_html() for text nodes, esc_attr() for HTML attribute values, esc_url() for href and src attributes, and wp_kses_post() for rich text that should allow a limited set of HTML tags. The wp_nonce_field() and check_admin_referer() pair prevents CSRF on form submissions — nonces are single-use tokens tied to the action, user, and session that expire after 12–24 hours. Stored XSS via the WordPress editor is mitigated by wp_kses_post() on save and wp_kses_post() or the_content() on output — the the_content filter applies wptexturize(), wpautop(), and other filters that also normalize dangerous markup. The custom product fields post applies these same patterns — wc_clean() for input sanitization and esc_html() for output escaping.

Problem: A plugin shortcode accepts a category ID from a URL query parameter and passes it directly into a $wpdb->query() call, and its output is echoed without escaping — creating a SQL injection point and a reflected XSS vulnerability accessible to any visitor.

Solution: Validate and cast the input to an integer with absint(), use $wpdb->prepare() with a %d placeholder for all database queries, and escape every output value with the context-appropriate escaping function at the point of rendering.

// ── BAD: SQL injection + XSS vulnerability ────────────
// NEVER do this:
function bad_shortcode($atts): string {
    global $wpdb;
    $cat = $_GET['cat'];  // unsanitized user input
    // SQL injection: attacker can append UNION SELECT or DROP TABLE
    $results = $wpdb->get_results("SELECT * FROM {$wpdb->posts} WHERE post_category = $cat");
    $out = '';
    foreach ($results as $row) {
        // XSS: if post_title contains <script>, it executes in the browser
        $out .= '<li>' . $row->post_title . '</li>';
    }
    return $out;
}

// ── GOOD: prepared statement + output escaping ────────────
function good_shortcode($atts): string {
    global $wpdb;

    // 1. Validate: cast to positive integer; 0 means "no filter"
    $cat_id = absint($_GET['cat'] ?? 0);
    if ($cat_id === 0) return '';

    // 2. Parameterized query — $wpdb->prepare() handles quoting and escaping
    $sql = $wpdb->prepare(
        "SELECT ID, post_title, guid FROM {$wpdb->posts}
         WHERE post_status = %s AND post_type = %s
         LIMIT %d",
        'publish', 'post', 10
    );
    $results = $wpdb->get_results($sql);

    // 3. Output escape at the point of rendering
    $out = '<ul>';
    foreach ($results as $row) {
        $out .= sprintf(
            '<li><a href="%s">%s</a></li>',
            esc_url(get_permalink((int)$row->ID)),  // esc_url for href
            esc_html($row->post_title)              // esc_html for text node
        );
    }
    $out .= '</ul>';
    return $out;
}
add_shortcode('category_posts', 'good_shortcode');

// ── CSRF protection for a custom admin form ────────────
add_action('admin_post_myplugin_save_settings', function() {
    // Verify nonce before processing POST data
    check_admin_referer('myplugin_save_settings', 'myplugin_nonce');

    // Verify capability
    if (!current_user_can('manage_options')) {
        wp_die(esc_html__('You do not have permission to do this.', 'myplugin'), 403);
    }

    $value = sanitize_text_field(wp_unslash($_POST['my_option'] ?? ''));
    update_option('myplugin_my_option', $value);

    wp_safe_redirect(admin_url('options-general.php?page=myplugin&saved=1'));
    exit;
});

NOTE: $wpdb->prepare() does not protect against SQL injection in the query structure itself — only in the values. Dynamic table names, column names, and ORDER BY / LIMIT clauses that are built from user input must be validated against an allowlist before being interpolated into the query: $direction = in_array($dir, ['ASC', 'DESC'], true) ? $dir : 'ASC';. Never pass $_GET or $_POST values directly as table or column names even after calling prepare().

Related posts