Dynamic SQL: Identifiers, ORDER BY and IN Lists

A filter bar on an orders screen offers a status dropdown, a column picker with an ascending and descending toggle, and a set of checkboxes that selects several references at once. Every one of those controls changes the shape of the query rather than the data in it, and a placeholder only helps with data. This is the point where people who learned "always use prepare()" start concatenating again, and the point where a code review has to know more than the rule.

Three shapes cover almost all of it. A name in a structure slot, such as a column after ORDER BY or a table after FROM. A keyword that is not a name at all, such as the ASC or DESC that follows a sort column. And a list of values whose length is not known until the request arrives, which is what IN (...) asks for. Each has a different correct answer, and only two of the three involve a placeholder.

An identifier is bounded by choosing, not by escaping

The column picker is the easiest of the three to get right when you accept what it actually is. The reader is not sending a column name. The reader is sending a choice from a list your code wrote, and the browser is an unreliable narrator about which one it sent. So write the list down and match against it.

/**
 * Maps a public sort key to a real column.
 *
 * @param string $key The value from the request.
 * @return string A column that exists in the table.
 */
function m4_sort_column( string $key ): string {
    $columns = array(
        'reference' => 'reference',
        'customer'  => 'customer',
        'total'     => 'total',
        'status'    => 'status',
    );

    return $columns[ $key ] ?? 'reference';
}

The switch shape does something the array of allowed names does not: it lets the public key differ from the column, so you can keep a stable API while renaming a column later. It also makes the default explicit rather than implicit in a ternary, which is the difference between "sorted by reference because that is the default" and "sorted by whatever the first entry in the array happened to be".

Once the value is one of four strings you wrote, whether you also pass it through %i is a question about future edits rather than about this one. Pass it anyway. A later change that reads the column from an option, or that lets an administrator define a custom column, keeps the %i escaping and loses only the allowlist:

$column = m4_sort_column( $request['sort'] ?? 'reference' );
$sql    = $wpdb->prepare(
    "SELECT id, reference, total FROM {$wpdb->prefix}m4_orders ORDER BY %i DESC",
    $column
);

The direction is a keyword, so it needs its own allowlist

This is the part that gets forgotten, because the direction looks like a small detail attached to the column. It has no placeholder at all. %i would turn DESC into the identifier `DESC`, which is a column that does not exist, and %s would turn it into the string constant 'DESC', which MySQL accepts and ignores. Neither is what you want, so the direction has to become one of two literal keywords:

$direction = ( 'asc' === strtolower( (string) ( $request['order'] ?? 'asc' ) ) ) ? 'ASC' : 'DESC';

The ternary reads oddly the first time. It is an allowlist with two entries where the fallback is also a valid entry, and it is correct: anything that is not asc, including an empty string, a nested array cast to a string, or a payload, becomes DESC. The output is one of two constants that the developer typed, and the reader's bytes never appear in the query. Compare that with strtoupper( $request['order'] ), which looks tidier and puts arbitrary reader input into a structure slot.

Core WordPress handles the same problem the same way. The posts controller defines its orderby REST argument with a closed list of permitted values, and its order argument with a second list holding only asc and desc. A request carrying anything else fails validation before a query is built. The pattern is worth copying precisely because it is boring.

// From the posts controller in WordPress 7.0.7, abbreviated.
$query_params['order'] = array(
    'default' => 'desc',
    'enum'    => array( 'asc', 'desc' ),
);
$query_params['orderby'] = array(
    'default' => 'date',
    'enum'    => array( 'author', 'date', 'id', 'include', 'modified', 'parent', 'relevance', 'slug', 'title' ),
);

An IN list needs placeholders you generate from the count

The third shape is the one with a genuinely reusable answer, because the number of values is unknown at the time you write the query. The mistake is to build the list by joining escaped values. The correct move is to build the placeholders from the count and let prepare() bind the values.

/**
 * Builds a placeholder list and its arguments for a dynamic IN clause.
 *
 * @param array  $values Values to bind. Re-indexed before use.
 * @param string $type   Placeholder for each value: %s, %d or %f.
 * @return array{0:string,1:array} The SQL fragment and the arguments for it.
 */
function m4_in_list( array $values, string $type = '%s' ): array {
    if ( array() === $values ) {
        // "col IN ()" is a syntax error. NULL matches nothing, which is the
        // honest result for an empty selection.
        return array( 'NULL', array() );
    }

    return array(
        implode( ', ', array_fill( 0, count( $values ), $type ) ),
        array_values( $values ),
    );
}

Three details in that function are doing work. array_fill( 0, count( $values ), $type ) produces an array whose length matches the values, so the placeholder count can never drift from the argument count. array_values() drops the keys, which matters because prepare() binds arguments by position and an associative array inherited from a request can carry string keys that break the positional match. And the empty case returns a fragment that is syntactically valid instead of a pair of parentheses with nothing in them.

Used with a spread, the call site stays readable:

list( $placeholders, $args ) = m4_in_list( array( 'ORD-1002', 'ORD-1005' ) );

$sql = $wpdb->prepare(
    "SELECT reference, customer FROM {$wpdb->prefix}m4_orders
     WHERE reference IN ($placeholders) ORDER BY reference",
    ...$args
);

SELECT reference, customer FROM wp_m4_orders
     WHERE reference IN ('ORD-1002', 'ORD-1005') ORDER BY reference

  ORD-1002 | Bo Tran
  ORD-1005 | Eli Farkas

With %d and a pair of integers the same helper produces IN (2, 5) with the two numbers unquoted. The type argument is the caller's promise about the data, and it is the one thing the helper cannot check for you: pass strings to %d and MySQL will coerce them, so a reference of ORD-1002 becomes 0 and silently matches nothing.

Several filters mean an array of conditions and an array of arguments

Real screens combine the shapes, which is where the argument order starts to matter. Build the conditions and the arguments together, in the same order, and concatenate the finished strings:

/**
 * Builds a WHERE clause from optional filters.
 *
 * @return array{0:string,1:array} SQL conditions and their bound arguments.
 */
function m4_orders_where( array $statuses, array $refs ): array {
    $conditions = array();
    $args       = array();

    if ( $statuses ) {
        list( $p, $a ) = m4_in_list( $statuses );
        $conditions[]  = "status IN ($p)";
        $args          = array_merge( $args, $a );
    }

    if ( $refs ) {
        list( $p, $a ) = m4_in_list( $refs );
        $conditions[]  = "reference IN ($p)";
        $args          = array_merge( $args, $a );
    }

    return array( $conditions ? implode( ' AND ', $conditions ) : '1=1', $args );
}

list( $where, $args ) = m4_orders_where( array( 'shipped', 'pending' ), array( 'ORD-1002', 'ORD-1005' ) );

$sql = $wpdb->prepare(
    "SELECT reference, status FROM {$wpdb->prefix}m4_orders
     WHERE $where ORDER BY reference",
    ...$args
);

SELECT reference, status FROM wp_m4_orders
     WHERE status IN ('shipped', 'pending') AND reference IN ('ORD-1002', 'ORD-1005') ORDER BY reference

The function returns SQL that already contains placeholders and an argument array that matches them one for one. That pairing is the contract, and it is the thing to check when this pattern is reviewed: if a branch adds a condition without adding its arguments, or adds arguments in a different order to the conditions, prepare() will report a count mismatch and hand back an empty string. The failure is loud in development and silent in production, which is a good reason to assert the count in a unit test rather than trusting the reading.

The '1=1' default deserves a word. Returning an empty string for "no filters" forces every caller to branch on whether the clause is empty, and the branch that forgets produces a query ending in WHERE ORDER BY. A clause that is always true keeps the string shape constant and costs nothing measurable.

esc_sql returns escaped data, not a safe query

The reason esc_sql() cannot stand in for the patterns above is that it solves half a problem. It escapes the characters that are special inside a MySQL string literal. It does not add the surrounding quotes, and it does not know whether the position it is being pasted into is a literal at all. Both gaps are real, and the first one is the one that shows up in penetration tests.

The second gap is easier to demonstrate than to argue about. Under the NO_BACKSLASH_ESCAPES SQL mode MySQL stops treating a backslash as an escape character, so the backslash that esc_sql() added in front of a quote is only a backslash. A value whose only special character is a trailing backslash then closes the string literal:

global $wpdb;
$wpdb->query( "SET SESSION sql_mode = 'NO_BACKSLASH_ESCAPES'" );

$hostile = "x\\";
$escaped = esc_sql( $hostile );

$sql = "SELECT COUNT(*) FROM {$wpdb->prefix}m4_orders WHERE customer = '$escaped' OR 1=1 -- '";
// SELECT COUNT(*) FROM wp_m4_orders WHERE customer = 'x\' OR 1=1 -- '

rows matched     : 5 (of 5)

Five of five rows matched a query that was supposed to filter by customer, and no error was returned. The reader's input was a single backslash. The same value through a placeholder produces the finished string WHERE customer = 'x\' with no boolean after it, matches nothing, and returns zero rows with no error. The escaping in the two cases is identical, because both call mysqli_real_escape_string(). The difference is that prepare() decided where the quotes go, so the attacker cannot use the quote that the developer was supposed to place.

That is the argument in one sentence: esc_sql() is a function that returns escaped data, and the security property people want belongs to the composition of the query. Keep the composition inside prepare() and every string your code assembles is a fragment you wrote. Move it outside and the security of the query is a property of your ability to remember quotes, in every branch, forever.

list( $sql, $args ) = m4_find_orders( array(
    'status'    => array( 'shipped', 'pending' ),
    'min_total' => '100',
    'sort'      => 'total',
    'order'     => 'desc',
) );

function m4_find_orders( array $request ): array {
    global $wpdb;

    $conditions = array();
    $values     = array();

    // One value and several values collapse into the same list.
    if ( ! empty( $request['status'] ) ) {
        $statuses = array_values( (array) $request['status'] );
        $statuses = array_map( 'sanitize_key', $statuses );
        list( $p, $a ) = m4_in_list( $statuses );
        $conditions[]  = "status IN ($p)";
        $values        = array_merge( $values, $a );
    }

    if ( isset( $request['min_total'] ) && '' !== $request['min_total'] ) {
        $conditions[] = 'total >= %f';
        $values[]     = (float) $request['min_total'];
    }

    $where = $conditions ? implode( ' AND ', $conditions ) : '1=1';

    $columns   = array( 'reference', 'customer', 'total', 'status' );
    $sort      = (string) ( $request['sort'] ?? 'reference' );
    $column    = in_array( $sort, $columns, true ) ? $sort : 'reference';
    $direction = ( 'asc' === strtolower( (string) ( $request['order'] ?? 'asc' ) ) ) ? 'ASC' : 'DESC';

    // prepare() binds its arguments in the order the placeholders appear, and
    // the identifier placeholder is last in this query, so it is last here.
    $args = array_merge( array_values( $values ), array( $column ) );

    $sql = $wpdb->prepare(
        "SELECT id, reference, customer, total, status
         FROM {$wpdb->prefix}m4_orders
         WHERE $where
         ORDER BY %i $direction",
        ...$args
    );

    return array( $sql, $args );
}

SELECT id, reference, customer, total, status FROM wp_m4_orders WHERE status IN ('shipped', 'pending') AND total >= 100.000000 ORDER BY `total` DESC

args (4): ["shipped","pending",100,"total"]
rows: 3  error: (none)
   ORD-1003 410.75 shipped
   ORD-1005 256.10 pending
   ORD-1001 124.50 shipped

SELECT 1 WHERE status IN ('total', 'shipped') AND total >= 0.000000 ORDER BY `100`