skip to content

Why can't ORDER BY use a SQL placeholder for a user-chosen sort column, and what replaces it?

level: seniorimportance: should knowfreq 45%

answer

  1. values bind, structure does not
  2. the parser needs the column first
  3. map a request key to a fixed fragment
  4. unknown key is a 400, not a fallback
  5. escaping still leaves you validating

basics

~20 s

Placeholders bind values, while an identifier decides what the statement means and must be fixed before parsing. Replace it with a lookup from a request key to a column fragment written in your own source.

solid answer

~50 s

A bind parameter fills a value slot after the statement has been parsed and planned. A column name is not a value — it changes the statement's meaning — so `ORDER BY $1` cannot work. Worse, on some engines it is accepted and sorts every row by the same constant, so you get no error and no ordering. The replacement is an allowlist: a `map[string]string` (or a `switch`) from a stable API key like `"newest"` to a fragment your program contains, such as `"created_at DESC"`. The request selects a key; an unknown key is a 400, never a fallback to the raw input. `LIMIT` and `OFFSET` are values, so they stay bound — and clamped. Quoting and escaping the user's identifier is not a substitute: you would still have to check it is a real column, and once you do, you have written an allowlist with extra steps.

code

go · 12 lines
go
var sortColumns = map[string]string{
	"newest": "created_at DESC",
	"total":  "total_cents DESC",
}

orderBy, ok := sortColumns[r.URL.Query().Get("sort")]
if !ok {
	http.Error(w, "unknown sort", http.StatusBadRequest)
	return
}
q := "SELECT id, total_cents FROM orders WHERE tenant_id = $1 ORDER BY " + orderBy + " LIMIT $2"
rows, err := db.QueryContext(ctx, q, tenantID, limit)

go deeper

for a junior

Remember the split: values go in as placeholder arguments, but table and column names cannot. If a sort option comes from the user, look it up in a fixed map of allowed options rather than putting the parameter into the SQL.

for a middle

Explain the mechanism — the server parses and plans before binding, so an identifier must be present in the text — and note that some engines accept ORDER BY with a parameter and silently sort by a constant instead of erroring.

for a senior

Demonstrate the production version: an allowlist whose values are source literals, an explicit 400 on an unknown key, clamped LIMIT, a test that keeps the map in step with the schema, and reading the query log to confirm the set of statement texts stays small and enumerable.

for a principal

Own the rule rather than the instance — where dynamic fragments may be assembled, what the allowlist may contain, and who signs off when someone wants to widen it. Weigh the product pressure for arbitrary sorting against the review cost it creates for every future change.

## Values bind, identifiers do not When a statement reaches the server it is parsed into a tree and planned: which tables, which indexes, which sort key. Bind parameters are filled into leaf slots of that tree afterwards. An identifier — a column, a table, a sort direction, an operator — is a *structural* part of the tree, so it has to be present before parsing. There is no point in the pipeline where a parameter could supply one. That is why `ORDER BY $1` fails to do what people expect. On engines that accept it, the parameter is a constant expression, so every row sorts by the same value and the ordering is effectively arbitrary — a silent bug, which is worse than an error. Nobody notices until a customer reports that page two repeats rows from page one. ## The allowlist The fix is to make the request *select* a fragment rather than *supply* one: ```go var sortColumns = map[string]string{ "newest": "created_at DESC", "total": "total_cents DESC", "name": "customer_name ASC", } ``` The map's values are string constants in the program's source. The user controls only which key is looked up. An unrecognised key returns `400 Bad Request` — it never falls through to using the raw parameter, and it never gets "sanitised" into one. Reviewing this is mechanical: look at the map's values, and if they are all literals in the file, the SQL text is program-owned. The same technique covers everything a placeholder cannot reach: - **Sort direction** — either part of the mapped fragment, as above, or a two-element allowlist. - **Table or schema names** — in a multi-tenant design, prefer a bound `tenant_id` column over a per-tenant table name; when the table really must vary, map it. - **Optional predicates** — build the WHERE clause from a fixed set of fragments, each with its own placeholders, appending an argument per fragment you enable. Values stay bound throughout. `LIMIT $2` and `OFFSET $3` are values, so they bind normally; clamp them anyway, because an unbounded `LIMIT` is a cost problem rather than an injection one. ## Why escaping is the wrong answer The tempting alternative is to quote the identifier — wrap it in double quotes, double any embedded quote — and pass it through. Three objections, in order of weight: 1. **You still have to validate it.** A syntactically safe identifier can name a column the caller should not sort by, or one that does not exist, turning a bad request into a server error. 2. **Quoting rules are dialect-specific** and change with server modes; getting them right is exactly the work `database/sql` refused to do for values. 3. **The allowlist is smaller.** Once you have validated existence and authorisation, you have enumerated the legal set — so store the fragments and skip the quoting. A weaker variant is a regexp such as `^[a-zA-Z_][a-zA-Z0-9_]*$`. It closes the injection hole but leaves objection 1 wide open, and it silently expands as the schema grows: a column added for internal bookkeeping becomes sortable and therefore probeable. ## Keeping the allowlist honest The practical failure mode is drift: a column is renamed and one map value now names something that no longer exists, in a branch nobody exercises. Two cheap defences — a table-driven test that runs every mapped sort against a test schema, so a rename fails the build; and keeping the map next to the query that uses it rather than in a shared "constants" file where it rots. ## Confirming it in production The database's own query log settles arguments about whether a service builds SQL from input. A correctly built endpoint shows a small, enumerable set of statement texts — one per sort option — each recorded with its bound parameters beside it. If the log shows an open-ended variety of statement texts, or values embedded in the text instead of listed as parameters, the allowlist is not holding.

  • Can LIMIT and OFFSET be bound as parameters?
    On typical engines yes — they take values, not identifiers, so `LIMIT $2 OFFSET $3` binds normally. Bind them and clamp them: an unbounded page size is a cost and memory problem even though it is not an injection one. A few engines are fussier about parameters in LIMIT, which is worth checking once per engine rather than assuming.
  • Why not validate the column name with a regexp and interpolate it?
    It closes the injection hole but not the authorisation one: a syntactically valid identifier can be a column the caller should not sort by, or one that does not exist, producing server errors. And it grows silently with the schema — every new column becomes sortable. Once you validate that the column exists and is permitted, you have enumerated the legal set, which is the allowlist.
  • How do you keep the sort allowlist from drifting away from the schema?
    A table-driven test that executes each mapped sort fragment against the test schema, so a renamed or dropped column fails the build rather than a rarely used sort option in production. Keeping the map beside the query it serves, rather than in a shared constants file, also makes the pairing visible in review.
  • What does a correct implementation look like in the database's query log?
    A small, enumerable set of statement texts — one per allowed sort option — each logged with its bound parameters listed separately. An open-ended variety of statement texts, or literal values appearing inside the text, means something is building SQL from request data and the allowlist is being bypassed.

saying these in an interview costs you the question

  • Writes ORDER BY $1 and assumes it sorts by that column
  • Escapes the user's column name and interpolates it
  • Validates the identifier with a regexp and calls it safe
  • Falls back to the raw parameter when the key is unmapped
  • Thinks a sort column cannot be dangerous because it is not in WHERE