WordPress Coding Standards and SQL

Another frustrating coding session and yey another thing that I had to learn the hard way, so again I wrote it up so you don't have to go through the same thing, or more realistically so future me can remember later!

I've spent the morning fixing $wpdb->prepare()errors and "Interpolated Variable" warnings generated by my WordPress SQL.

This code worked but did not meet the exacting standards of the WordPress plugin directory. Of course they need those high standards, otherwise people would unwittingly add bad or unexpected code to their sites (~45% of the web).

My frustration was tracking down where the error was happening and what I could do otherwise.

What's the problem?

Ordinarily it would not be super tricky to track down because you would have maybe one or two places you needed your own SQL, and if you do it would be relatively simple. My plugin though was intended to add advanced filters to the list of posts and pages, which meant the user input would be incorporated.

Yep, a security hygiene issue and potential injection vector.

When working with custom admin queries in WordPress, especially those involving WP_Query modifications or subqueries, you may run into a warning like this:

WARNING WordPress.DB.PreparedSQL.InterpolatedNotPrepared: Use placeholders and $wpdb->prepare(); found interpolated variable...

This often shows up in plugins that dynamically build IN (...) SQL clauses using arrays of values.

Here’s what it means in practice, and how to fix it properly.

What You Shouldn't Do

Here’s a common pattern that triggers the warning:

$in_clause_placeholders = implode(',', array_fill(0, count($term_ids), '%d'));
$prepared_query = $wpdb->prepare(
    "SELECT ... WHERE tt.term_id IN ($in_clause_placeholders)",
    array_map('intval', $term_ids)
);

Even though you're using prepare() and escaping your data, this still violates WordPress coding standards because you're injecting a variable ($in_clause_placeholders) directly into the SQL string before the prepare() call.

The rule is simple: don’t put variables inside the SQL string you pass to $wpdb->prepare(). WordPress expects all SQL parts with variable data to be passed as placeholders, and let prepare() build the query safely.

A Standards-Compliant Fix

A better and more compliant approach is to:

1. Manually sanitize the values (e.g. cast to int)
2. Manually build the IN (...) clause, avoiding placeholder strings
3. Use prepare() only where you’re inserting raw user input like strings

$term_ids = array_map( 'intval', $term_ids );
$term_ids_sql = implode( ',', $term_ids );

// Escape taxonomy name separately
$taxonomy_sql = $wpdb->prepare( 'tt.taxonomy = %s', $taxonomy );

// Build the subquery
$subquery = "
    SELECT tr.object_id
    FROM {$wpdb->term_relationships} AS tr
    INNER JOIN {$wpdb->term_taxonomy} AS tt 
        ON tr.term_taxonomy_id = tt.term_taxonomy_id
    WHERE $taxonomy_sql
        AND tt.term_id IN ($term_ids_sql)
";

// Add to WHERE clause of your main query
$where .= " AND {$wpdb->posts}.ID IN ($subquery)";

Why it works:

• $term_ids are integers, manually sanitized
• No variables are interpolated inside a prepare() call
• prepare() is used properly for string input
• The query is readable and avoids the warning

Use $wpdb->prepare() only for raw user input that is:

• Being inserted directly into a query
• Of an unknown type (like user-submitted strings)

Avoid using it:

• To build placeholder lists (like %d, %d, %d) inside SQL strings
• On values you've already safely cast (like arrays of integers)

Bottom Line

WordPress coding standards are sometimes stricter than the usual PHP safety needs, but for good reason—they help ensure plugin code remains readable, predictable, and secure.

Whenever you're working with dynamic SQL, follow these simple rules:

• Use prepare() for input, not structure
• Never interpolate variables inside a SQL string passed to prepare()
• Manually sanitize and build dynamic lists like IN (...) outside the query

You'll not just pass code sniffers you'll also write cleaner, safer WordPress code your future self (and your users) will appreciate.