skip to content

Why can't a table or column name be a sqlite3 query parameter, and what replaces it?

level: middleimportance: must knowfreq 55%

answer

  1. Some positions are decided before binding
  2. Values bind, structure does not
  3. One case fails loudly, one silently
  4. ORDER BY placeholder sorts by a constant
  5. Closed set of names you wrote

basics

~20 s

Parameters bind after the statement is compiled, but identifiers must be known during compilation so the engine can resolve the table and columns. An identifier therefore belongs in the SQL text, chosen from a fixed allowlist your code owns.

solid answer

~50 s

SQLite parses the statement text first and binds parameters into the compiled result afterwards, so anything the compiler needs in order to build the plan — table names, column names, sort direction — cannot arrive as a parameter. `SELECT doc FROM ?` raises `sqlite3.OperationalError` with a syntax error, and the more dangerous case is `ORDER BY ?`, which compiles fine and sorts every row by the same constant, returning unsorted rows with no error at all. The replacement is a closed allowlist: map the caller's token through a literal `dict` or `frozenset` of column names written in your source, raise `ValueError` on a miss, and interpolate only the fragment you looked up. A regular-expression check that a string "looks like an identifier" is not equivalent — it tests shape where the requirement is membership. Note that `LIMIT ?` does bind, because a row limit is a value.

code

python · 18 lines
python
import sqlite3

SORTABLE = {"doc", "pages", "created_at"}

def page(con, sort_by, limit):
    if sort_by not in SORTABLE:
        raise ValueError(f"unsupported sort column: {sort_by!r}")
    sql = f"SELECT doc FROM job ORDER BY {sort_by} LIMIT ?"
    return con.execute(sql, (limit,)).fetchall()

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE job(doc TEXT, pages INTEGER, created_at TEXT)")
con.executemany("INSERT INTO job VALUES(?, ?, ?)",
                [("z.pdf", 9, "2026-01-03"), ("a.pdf", 1, "2026-01-01")])

print(page(con, "doc", 10))
print(con.execute("SELECT doc FROM job ORDER BY ?", ("doc",)).fetchall())
con.close()

go deeper

for a junior

Remember the rule and the reason in one line: placeholders carry values, and the table and column names have to be in the SQL text because the engine needs them to compile the statement.

for a middle

Explain the compile-then-bind ordering, and show the allowlist pattern with an explicit raise on a miss. Be able to state why ORDER BY with a placeholder is worse than an error — it returns unsorted rows silently.

for a senior

Show how you would keep dynamic sorting and filtering safe across a whole API surface: external tokens mapped to internal columns so the schema does not leak, one shared builder rather than per-endpoint string work, and tests that assert an unknown token raises.

for a principal

Own the design position that callers may pick from a set of shapes the program owns, never supply a shape. Be ready to argue that tradeoff against demands for a fully dynamic query API, and to name what such an API costs in review burden and statement-cache behaviour.

### Where a placeholder is allowed to stand A placeholder occupies a position where the SQL compiler expects a **value expression**. When `sqlite3` executes a statement, SQLite parses the text first and produces a compiled program; the parameters are then bound into slots in that already-compiled program. For that to work, everything the compiler needs in order to *build* the program must already be present in the text. Table names, column names, sort direction, and structural keywords are exactly that: the compiler must resolve them to real schema objects to decide which table to open, which column index to read and which index to use. None of that can wait for a value that arrives afterwards. So identifiers are not "unsupported by sqlite3" out of caution — they are unbindable in principle, in every DB-API driver, because binding happens strictly after compilation. ### Two different failures, and the quiet one is worse Ask for a table name as a parameter and you get a loud error: ```python con.execute("SELECT doc FROM ?", ("job",)) # sqlite3.OperationalError: near "?": syntax error ``` Ask for a sort column and you get something far more dangerous: ```python con.execute("SELECT doc FROM job ORDER BY ?", ("pages",)).fetchall() # runs fine, and the rows come back unsorted ``` That statement is valid: `ORDER BY <expression>` accepts any expression, and a bound parameter is a perfectly good expression — a constant. Sorting every row by the same constant is a no-op, so the query silently returns rows in whatever order the scan produced. No exception, no warning, wrong answer. It typically ships, because the developer tests with one page of data that happened to come back in insertion order. Note the contrast with `LIMIT ?`, which **does** work: a row limit is a value, not an identifier. The boundary is not "values versus everything else in the query" — it is precisely at the things the compiler must resolve to build the plan. ### The replacement: an allowlist, not a validator Since the identifier must be part of the text, the only safe construction is one where the text can only ever be a string **you** wrote. That means mapping the caller's token through a fixed, closed set: ```python SORT_COLUMNS = {"name": "doc", "size": "pages", "age": "created_at"} def page(con, sort_key, limit): column = SORT_COLUMNS.get(sort_key) if column is None: raise ValueError(f"unsupported sort key: {sort_key!r}") return con.execute(f"SELECT doc FROM job ORDER BY {column} LIMIT ?", (limit,)).fetchall() ``` Three properties make this correct rather than merely careful. The interpolated fragment is a *literal from your source*, so its content does not depend on modelling anyone's escaping rules. The membership test is `in` on a closed container — an exact-match lookup, not a pattern judgement. And the miss path raises rather than falling through to a default column, so a typo is an error instead of a surprise ordering. Mapping an external token to an internal column, rather than accepting the column name directly, is a bonus worth mentioning: the public API stops leaking your schema, and renaming a column becomes a one-line change in the map. ### The two tempting near-misses **Regex validation.** "Reject anything that is not `[A-Za-z_][A-Za-z0-9_]*`" feels equivalent and is not. It admits every identifier-shaped string, including real column names the caller was never meant to reach, and it is a rule about *shape* where the requirement is about *membership*. Worse, the correctness of a pattern-based check depends on your model of the quoting rules being complete; the allowlist's correctness does not depend on such a model at all. **Quoting the identifier yourself.** Wrapping the value in double quotes and doubling any embedded quote is closer to right, but it makes the safety of the call site depend on a hand-written escaper being exhaustive, which is the same bet parameterization was adopted to stop making. There is also a mundane cost to unbounded identifiers: every distinct statement text is a distinct entry in the driver's compiled-statement cache, so a query whose text varies freely also loses statement reuse. ### Where the allowlist should live Keep it as a plain module-level constant next to the code that builds the statement — a `frozenset` or a `dict` of literals, with no imports behind it. A tempting alternative is to derive it from whatever module already describes the schema, and in a queue-shaped service where the schema module imports the query helpers, that derivation is how a circular import at startup gets introduced. A literal map costs one line per column and cannot import anything. ### The interview summary Values bind; structure does not. If a caller needs to influence the *shape* of a statement, the program chooses from a set of shapes it wrote, and the caller only picks an index into that set.

  • Why is a regular-expression check on the identifier not equivalent to an allowlist?
    A pattern tests the shape of a string; the requirement is membership in a known set. Every real column name is identifier-shaped, so the pattern admits columns the caller was never meant to reach, and its safety also depends on your model of the engine's quoting rules being complete. An allowlist's correctness depends on no such model — the text that reaches the statement is a literal from your own source, and the lookup is exact.
  • Where should the allowlist itself live in the codebase?
    As a plain module-level `frozenset` or `dict` of literals next to the code that builds the statement, with no imports behind it. Deriving it from whatever module already describes the schema looks tidier but couples the query helpers to the schema layer, and in a service whose schema module already imports those helpers that is a straightforward way to introduce a circular import at startup. A literal map costs one line per column and cannot import anything.
  • Which positions in a statement can take a placeholder, if identifiers cannot?
    Anywhere the compiler expects a value expression: the operands of comparisons, values in an INSERT, and `LIMIT` and `OFFSET`, which bind fine because a row count is a value. The boundary is not values versus everything else — it is exactly the things the compiler must resolve to build the plan. `ORDER BY` is the trap, because it accepts an expression, so a bound parameter is legal there and simply means "sort by this constant".

You can leave blanks on a printed form for someone to fill in, but you cannot let them fill in which form gets used — that choice has to be made before anything is printed.

saying these in an interview costs you the question

  • Claims a table name can be bound like any other parameter
  • Thinks ORDER BY with a placeholder sorts by that column
  • Validates the identifier with a regex instead of a fixed set
  • Double-quotes the caller's string and calls it safe
  • Falls back to a default column when the lookup misses
  • Assumes LIMIT also needs interpolation

context

resolving links…