Filtering, Sorting, Searching & Field Selection with Safe Dynamic SQL

Expose filter, sort, search, and field-selection query parameters on a collection endpoint safely, using allowlists to prevent SQL injection through dynamic column names and ORDER BY clauses.

A collection endpoint like GET /orders almost never returns 'everything, in whatever order the database feels like' — real clients need to filter by status, search by customer name, sort by different columns, and sometimes ask for only specific fields to keep the payload small. All of that means building SQL dynamically based on query parameters the client controls, which is exactly the situation SQL injection thrives in if you're not careful — parameterized queries protect *values* (WHERE status = ?), but they cannot protect *identifiers* like column names, because a placeholder can only stand in for a value, never for a piece of SQL syntax like a column or table name. This lesson covers how to expose ?status=shipped&sort=-created_at&search=alice&fields=id,total safely: filtering and searching with parameterized values as usual, but sorting and field selection through a strict allowlist that maps client-facing names to real, known-safe column names — never interpolating client input directly into a column position.

Filtering: parameterized values, known operators

Filtering by known fields (?status=shipped, ?customer_id=42) is the most straightforward case: the *column name* (status, customer_id) is fixed by your own code, chosen by which query parameters you decided to support, and only the *value* comes from the client — so a normal parameterized query (WHERE status = ?, binding the client's value) is completely safe. The danger only appears if you let the client control which column gets filtered, not just what value to filter it by.

Range filters (?min_price=1000&max_price=5000) and multi-value filters (?status=shipped,cancelled) follow the same rule: build the SQL with a fixed, known clause shape in your code, and bind every client-supplied value as a parameter, never as a raw string concatenated into the query.

Searching: LIKE, but escaped and still parameterized

Free-text search (?search=alice) typically becomes a LIKE '%value%' (or a full-text search feature for larger datasets), and the value is still just a value — bind it as a parameter like any other. The one extra wrinkle is that LIKE's own wildcard characters (% and _) are meaningful inside the pattern, so if you want a literal % or _ in a user's search term to be treated literally (not as a wildcard), you need to escape those characters within the bound value before wrapping it in %...%, using your database driver's escape mechanism.

Sorting: the allowlist is mandatory

Sorting is where things get dangerous, because the client is naturally choosing a *column name*, not just a value — ?sort=created_at or ?sort=-price (a leading - for descending) means 'ORDER BY created_at' or 'ORDER BY price DESC'. A parameterized query placeholder cannot stand in for a column name or the ASC/DESC keyword — those are SQL syntax, not values — so if you interpolate the client's raw sort parameter directly into the query string, you've opened a straightforward SQL injection: a request like ?sort=created_at; DROP TABLE orders;-- (or more realistically, a subquery smuggled into the ORDER BY clause) can run arbitrary SQL.

The fix is an allowlist: a fixed PHP array mapping client-facing sort keys to the actual, hard-coded column names you already know are safe ('created_at' => 'created_at', 'price' => 'total_cents'). The client's sort value is looked up in that array; if it's not a key in the array, you reject the request (422) or fall back to a default sort — you never pass anything the client sent directly into the SQL string's identifier position. This is the single most important rule in this lesson: identifiers (column/table names) must come from your own code's allowlist, never from client input, even when parameterized queries are used everywhere else.

Field selection: same allowlist discipline

Field selection (?fields=id,total_cents,status) lets a client request a subset of a resource's fields, which is useful for reducing payload size on mobile clients or high-frequency polling. Just like sorting, the client is naming columns, so the same allowlist rule applies: intersect the client's requested field list with a fixed set of exposable column names, and silently drop (or reject with 422) anything not on the list, rather than building a SELECT column list directly from client input.

Field selection also has a security dimension beyond SQL injection: a resource often has internal-only columns (a password hash, an internal risk score, a soft-delete flag) that should never be selectable at all, regardless of what the client asks for — the allowlist is simultaneously the SQL-safety mechanism and the access-control boundary for which fields even exist from the API's point of view.

• Filtering: column fixed by your code, value bound as a parameter — safe by default. • Searching: value bound as a parameter inside a LIKE pattern, with % and _ escaped if literal matches are wanted. • Sorting: client picks a *key*, your code maps that key through an allowlist to the real column name — never interpolate the client's raw column name. • Field selection: intersect the client's requested fields with an allowlist of exposable columns; never build a SELECT list directly from client input.

The allowlist pattern

The client's sort string is only ever used as a lookup KEY into a fixed PHP array — it never reaches the SQL string directly. An unrecognized sort key is rejected outright (422), not silently ignored, so the client finds out immediately if it typo'd a field name.

<?php
declare(strict_types=1);

// Client-facing sort key => real, known-safe SQL column.
const SORTABLE_COLUMNS = [
    'created_at' => 'created_at',
    'price'      => 'total_cents',
    'status'     => 'status',
];

function resolveSortColumn(string $clientSort): string {
    $desc = str_starts_with($clientSort, '-');
    $key  = ltrim($clientSort, '-');

    if (!array_key_exists($key, SORTABLE_COLUMNS)) {
        throw new ApiException(422, 'invalid_sort', "Cannot sort by '$key'");
    }

    return SORTABLE_COLUMNS[$key] . ($desc ? ' DESC' : ' ASC');
}

Code Examples

Every SQL *value* the client influences (status, min_total, search text) is bound through a placeholder in $params, exactly as usual. Every SQL *identifier* the client could influence — which columns to sort by, which columns to select — is instead looked up through a fixed PHP allowlist (SORTABLE, SELECTABLE_FIELDS) that maps a client-facing name to a real, hard-coded column expression; an unrecognized key is rejected with 422 rather than ever reaching the query string. This is what makes ?sort= and ?fields= safe to expose directly as query parameters even though they logically 'choose columns'. Expected result: 422 invalid_sort for ?sort=price (not in the allowlist -- the real column is exposed only as 'total') if (!array_key_exists($sortKey, self::SORTABLE)) { throw new ApiException(422, 'invalid_sort',...); }

Common mistake: Building "ORDER BY " . $_GET['sort'] directly would let a client pass something like created_at; (SELECT password_hash FROM users LIMIT 1) or a database-specific injection payload straight into the query, because ORDER BY's target is SQL syntax, not a bindable value.

<?php
declare(strict_types=1);

final class OrderListQuery {
    // Allowlists: client-facing name => real column. This is the ONLY
    // place client input is allowed to influence a SQL identifier.
    private const SORTABLE = [
        'created_at' => 'o.created_at',
        'total'      => 'o.total_cents',
        'status'     => 'o.status',
    ];

    private const FILTERABLE_STATUSES = ['pending', 'shipped', 'delivered', 'cancelled'];

    private const SELECTABLE_FIELDS = [
        'id' => 'o.id', 'status' => 'o.status', 'total_cents' => 'o.total_cents',
        'customer_id' => 'o.customer_id', 'created_at' => 'o.created_at',
        // note: internal columns like risk_score, admin_notes are deliberately
        // NOT in this list -- they can never be selected via the API at all.
    ];

    public static function run(PDO $pdo, array $q): array {
        // --- FIELD SELECTION: intersect requested fields with the allowlist ---
        $requestedFields = isset($q['fields'])
            ? array_map('trim', explode(',', $q['fields']))
            : array_keys(self::SELECTABLE_FIELDS);
        $safeFields = array_intersect($requestedFields, array_keys(self::SELECTABLE_FIELDS)
…

Values vs. identifiers: what parameterization protects

Parameterized queries protect values; only an allowlist protects identifiers like column names.

WHERE status = ?          <-- VALUE position: placeholder works, fully safe
       ▲
       └── bound param: client's raw string, never executed as SQL

ORDER BY <???>            <-- IDENTIFIER position: NO placeholder can go here
       ▲
       └── must be a hard-coded column name chosen by YOUR code

Client sends: sort=price
                 │
                 ▼
        ┌───────────────────┐
        │  ALLOWLIST LOOKUP  │   'price' -> 'total_cents'  (known-safe)
        │  (fixed PHP array) │   'DROP TABLE x' -> not found -> 422 rejected
        └───────────────────┘
                 │
                 ▼
        ORDER BY total_cents ASC   <-- only allowlisted, hard-coded SQL ever reaches the query

Important Rules

Bind every client-supplied VALUE (filter values, search text, min/max ranges) as a parameterized placeholder — never string-concatenate it into SQL.

Never let client input choose a column or table name directly — a bind placeholder cannot stand in for a SQL identifier.

Map every client-facing sort key and field-selection name through a fixed, hard-coded allowlist array before it can influence SQL.

Reject an unrecognized sort/field/filter key explicitly (422) rather than silently ignoring or guessing what the client meant.

Keep internal-only columns out of the selectable-fields allowlist entirely — the allowlist is an access-control boundary, not just an injection defense.

Escape LIKE wildcard characters (% and _) in search input when you want them matched literally rather than as wildcards.

Why this matters at scale

This exact allowlist pattern is what battle-tested query builders (Doctrine's QueryBuilder, Eloquent's orderBy combined with a validated allowed-sorts config, or libraries like spatie/laravel-query-builder) implement under the hood — they don't magically make client-controlled sorting safe, they just wrap the same 'map a client key through a fixed allowlist before it touches SQL' discipline in reusable code. Understanding the raw pattern here is what lets you configure (or audit) those libraries correctly instead of trusting them blindly.

$sql = "SELECT * FROM orders ORDER BY " . $_GET['sort'] . " LIMIT 50";

$orderSql = self::SORTABLE[$sortKey] ?? throw new ApiException(422, 'invalid_sort', ...); // then interpolate only the allowlisted value

Concatenating a raw client-supplied sort parameter directly into the SQL string is a classic identifier-injection vulnerability — a placeholder can't protect an ORDER BY column position, so this is exploitable exactly like unescaped string concatenation in a WHERE clause, just in a spot developers check less often.

SELECT * FROM orders -- returns every column, including internal-only ones, regardless of ?fields=

SELECT o.id AS id, o.status AS status, ... FROM orders o -- only allowlisted, explicitly named columns

SELECT * bypasses field-selection allowlisting entirely and risks leaking internal-only columns (password hashes, internal flags) into every response, whether or not the client even asked for them via ?fields=.

Why can't a parameterized query placeholder (like PDO's ?) protect a client-controlled ORDER BY column name? A. Placeholders only work with SELECT statements B. A placeholder can only stand in for a value, never for SQL syntax like a column/table identifier or keyword C. ORDER BY doesn't support parameters in any programming language D. PDO specifically disables placeholders in ORDER BY clauses

B. A placeholder can only stand in for a value, never for SQL syntax like a column/table identifier or keyword — Bind placeholders substitute in as literal values at execution time — they cannot represent a column name, table name, or SQL keyword, which is exactly the position a client-controlled 'sort by' parameter needs to fill.

What is the correct way to safely expose a client-controlled ?sort= query parameter? A. Concatenate it directly into the SQL string after basic string escaping B. Map it through a fixed, hard-coded allowlist array from client-facing name to real column name, rejecting anything not in the map C. Only allow numeric sort values D. Disable sorting entirely to avoid any risk

B. Map it through a fixed, hard-coded allowlist array from client-facing name to real column name, rejecting anything not in the map — The allowlist pattern ensures only column expressions your own code has hard-coded as safe can ever reach the SQL string's identifier position, regardless of what the client sends as the sort key.

Besides SQL injection, what other risk does a field-selection allowlist (for ?fields=) guard against? A. Slow query performance B. Leaking internal-only columns (like a password hash or internal flag) that should never be exposed via the API regardless of what the client requests C. The client requesting too few fields D. JSON encoding errors

B. Leaking internal-only columns (like a password hash or internal flag) that should never be exposed via the API regardless of what the client requests — The selectable-fields allowlist doubles as an access-control boundary — columns simply never on the list can't be returned no matter what a client asks for, which protects internal-only data even from a client that discovers or guesses the real column name.

A GET /products endpoint should support ?category=electronics, ?sort=-price or ?sort=name, and ?fields=id,name,price. Write the allowlist arrays you'd need and explain, for each of the three parameters, whether it needs an allowlist or can be safely parameterized as a plain value.

category: bind as a parameterized WHERE value (WHERE category = ?), optionally also checked against an allowed-categories list for a cleaner 422 on typos. sort: needs a SORTABLE allowlist, e.g. ['price' => 'price', 'name' => 'name'], mapping the client key (with an optional leading '-' for DESC) to the real column — reject anything not in the map. fields: needs a SELECTABLE allowlist, e.g. ['id'=>'id','name'=>'name','price'=>'price'], intersected with the client's requested list; internal columns (e.g. cost_price, supplier_id) are simply never included in that map.

category is a value (parameterize it, maybe with an allowed-categories check too); sort and fields are identifiers and must go through an allowlist.