A reporting endpoint lets callers choose the sort column and direction, and in some deployments the table to read from. Placeholders cannot bind those positions. How do you build such a query safely, and why is an allowlist the only sound answer?
answer
- Identifiers shape the plan → unbindable by design
- Token → code-owned fragment map
- Pattern validation still allows password_hash
- Identifier quoting is dialect-specific and doubles the delimiter
- Table/schema choice comes from the principal, not the request
basics
~20 sMap the caller's opaque token to a fixed, code-owned identifier through a lookup table, and reject anything not in the table. Identifiers determine the plan, so they must be resolved before parsing — binding is impossible and quoting is a weak substitute.
solid answer
~50 sPlaceholders bind values because the plan is fixed at parse time; identifiers *are* part of the plan, so no engine can bind them. That leaves two options: transform the input, or choose from a closed set. Transformation (identifier quoting with `"`, `[ ]` or backticks) is dialect-specific, has its own escape rule — doubling the closing delimiter — and even when perfect it still lets a caller name a *valid but unauthorised* column such as `password_hash`. So the correct design is a server-side map from an opaque API token (`sort=recent`) to a constant the code owns (`created_at DESC`), with an else-branch that rejects or falls back to a default. The set is enumerable and finite, so the check is closed-world rather than open-world guessing. Direction becomes an enum of two constants, `LIMIT`/`OFFSET` are bound or coerced to integers, and multi-tenant table selection is derived from the authenticated principal, never from the request.
code
text · 8 lines# open-world (bad): the caller's bytes end up in the text
sql = "... ORDER BY " + validate_pattern(req.sort) # 'password_hash' passes
# closed-world (good): only developer-written fragments are concatenated
ALLOWED = { recent: "created_at DESC", amount: "total_cents DESC" }
frag = ALLOWED.get(req.sort)
if frag is None: reject(400) or frag = ALLOWED["recent"] # and log the miss
sql = "... ORDER BY " + frag + " LIMIT ?" # LIMIT still boundgo deeper
Know that column and table names cannot be bound and that the safe pattern is a lookup table from a fixed set of allowed values.
Explain why identifiers are unbindable (they are inputs to the plan) and handle direction and LIMIT correctly as well.
Cover the unauthorised-but-valid-column risk, dialect-specific identifier quoting, deriving schema from the principal, and making the unsafe call unrepresentable in the API signature.
Treat it as an API design question: expose a stable, small sort vocabulary decoupled from the schema, centralise the one place concatenation happens, and enforce it with types and static analysis rather than review.
## Why identifiers cannot be parameters A placeholder marks a spot where a *value* will be substituted after the statement is parsed and a plan produced. Identifiers — table names, column names, schema names — and structural keywords such as `ASC`/`DESC` are inputs to that parse and plan. The optimiser must know which table and which column before it can choose an index or a join order. If identifiers could arrive later, the plan could not exist. This is not a missing feature that some engine might add; it follows from the ordering that makes binding safe in the first place. So dynamic identifiers force the untrusted input back into the statement *text*, and the question becomes which defence rung applies when separation is unavailable. ## The defence ladder without separation **Detection** (a filter that looks for suspicious substrings) is the weakest: open-world, easily bypassed, and it fails legitimate identifiers. **Validation by pattern** — accept only `[A-Za-z_][A-Za-z0-9_]{0,62}` — is better than nothing and does block the `; DROP` shapes, but it is still open-world: it says which *strings* are acceptable, not which *columns* are allowed. `password_hash` and `internal_cost_basis` match the pattern perfectly. Pattern validation prevents syntax injection but not information disclosure, and those are two different bugs sharing one input. **Transformation** — quoting the identifier — removes syntax injection if done exactly right, and "exactly right" is dialect-specific: ANSI and PostgreSQL use double quotes with a doubled `""` to embed one; SQL Server accepts `[ ]` with `]]` to embed a bracket; MySQL uses backticks with doubled backticks (and, under `ANSI_QUOTES` mode, double quotes instead — another session setting that silently changes the grammar). Quoting also makes identifiers case-sensitive in engines that otherwise fold case, which turns a security control into a source of runtime errors. And again: correct quoting still permits any real column name. **Enumeration** — the allowlist — is the only closed-world option, and closed-world is why it is sound. The set of sortable columns for a report is finite, small, known at build time, and owned by the code. A map from an API-visible token to a code-owned fragment gives you three things at once: no untrusted byte ever reaches the statement text; the exposed vocabulary is decoupled from the schema, so renaming a column does not break clients and clients cannot enumerate your schema; and the unauthorised-column problem disappears because unauthorised columns are simply not in the map. ## The concrete pattern ``` SORTS = { "recent": "created_at DESC", "oldest": "created_at ASC", "amount": "total_cents DESC" } fragment = SORTS[request.sort] or SORTS["recent"] # never the raw input sql = "SELECT ... FROM orders WHERE tenant = ? ORDER BY " + fragment + " LIMIT ?" ``` Note what is *not* in the map: the request string itself. The key lookup is total — unknown key means default or 4xx — so the concatenated fragment is always a literal that a developer wrote. If the else-branch quietly falls back, log it; a spike of unknown sort keys is an attacker enumerating. Direction is the same idea in miniature: `dir == "asc" ? "ASC" : "DESC"`. Never `"ORDER BY x " + dir`. `LIMIT`/`OFFSET` are bindable in most engines (PostgreSQL, MySQL) but not universally, and older SQL Server needs `OFFSET ... FETCH`. Where you cannot bind, coerce to an integer type in the application and clamp it — an integer that has been parsed into a numeric type cannot carry syntax, and clamping also protects the database from a caller asking for ten million rows. ## Multi-tenant table or schema selection When the *table* or schema varies, the input almost never legitimately comes from the request body. It comes from the authenticated principal: tenant id → schema name, resolved through the same enumerated map or a validated tenant registry. Deriving it from the request means an authorisation bug even if the SQL is perfectly safe, because a caller could name another tenant's schema. Treat schema selection as an authorisation decision that happens to be implemented with a string. ## Testing and enforcement The allowlist is only a control if the raw input cannot bypass it, so enforce structurally: the data-access API should accept a `SortKey` enum rather than a string, making the unsafe call unrepresentable. Add a test that sends an unknown sort key and asserts a rejection or the default, and one that sends `created_at; DROP TABLE orders --` and asserts the same. Static analysis rules that flag concatenation into SQL should not be silenced for these files; instead, keep the concatenation in one small helper that the rule can be reviewed against once.
- A colleague argues that a regex allowing only letters, digits and underscore is equivalent to an allowlist. What is your response?The regex is an open-world check: it constrains the character set but not the meaning, so any genuine column name passes, including sensitive ones the endpoint was never meant to expose. It prevents syntax injection but not information disclosure. An enumerated map is closed-world — the set of acceptable inputs is finite and owned by the code — and it additionally decouples the public vocabulary from the schema.
- How would you keep this safe as the report grows to dozens of optional filters?Stop hand-assembling text and build the statement through a typed query builder or a small internal DSL where each filter contributes a fixed fragment plus bound values, and where the only strings that can be concatenated come from an enum. Push the enum into the data-access API signature so a caller physically cannot pass a raw string, then keep one reviewed helper as the single place any concatenation occurs.
Binding is filling in blanks on a form; choosing a table or column is choosing which form to use. You do not let a stranger write the form's title — you hand them a short list of forms you already printed.
saying these in an interview costs you the question
- "Just bind the column name too" — impossible; identifiers are needed to produce the plan.
- "Quoting the identifier makes it safe" — dialect-specific, has its own doubling rule, and still exposes every real column.
- "A regex on the identifier is an allowlist" — it constrains characters, not the permitted set of columns.
- Taking the tenant's table or schema name from the request body instead of the authenticated principal.
- Silently ignoring unknown sort keys with no logging, hiding enumeration attempts.