skip to content

questions

6

In SQL, why can a bind parameter in a prepared statement stand for a value but not for a table or column name?

level: seniorimportance: must knowfreq 42%

answer

  1. order of the server's work
  2. what analysis has to know first
  3. grammar wants a name after FROM
  4. what ORDER BY $1 really sorts by
  5. silent failure, no error raised

basics

~20 s

The server must parse a statement and resolve its names before any value exists, because names decide its meaning, columns and types. A parameter supplies only a value at run time, so a name must go in the text.

solid answer

~50 s

A statement goes through the server in order: parse it, resolve its names against the catalog, work out types and result columns, and only then accept values. A table or column name feeds those early steps, so the server has to know it before it can tell what the statement means. A parameter arrives after that and can only be a value. PostgreSQL shows both failures. `FROM $1` is a syntax error, because the grammar wants a name there. Nastier, `ORDER BY $1` bound to `'created_at'` parses, sorts every row by the same constant string and returns them in no particular order, with no error. `SELECT $1` just returns that text for each row. So a run-time name has to go into the statement text, quoted or taken from an allowlist, because an unquoted one is injectable.

code

sql · 7 lines
sql
PREPARE show_col (text) AS SELECT $1 FROM orders;
EXECUTE show_col('total');
-- one row per order, each holding the text total

PREPARE sort_col (int) AS SELECT id FROM orders ORDER BY $1;
EXECUTE sort_col(1);
-- a bound 1 is a constant, not the first output column

go deeper

for a junior

Recall the rule: placeholders stand for values only, so a table name, a column name or a keyword such as DESC cannot be passed as a bind parameter.

for a middle

Explain the order: parsing and name resolution happen before values exist. Then predict each position in PostgreSQL: a syntax error after FROM, and a silent constant in ORDER BY or the select list.

for a senior

Diagnose the silent case from its symptom, unsorted rows and no error, and say how you would confirm it: an out-of-order test and a plan with no Sort step.

for a principal

Make the misuse hard to write: give the team one sanctioned way to choose a sort column, because a bound ORDER BY passes review and passes tests on small tables.

## The order the server works in A prepared statement separates the statement's text from its values. To see why a name cannot be a value, follow what the server does with the text. 1. **Parse.** The grammar turns the text into a tree. It decides what each token is: a keyword, a name, an expression. 2. **Analyse.** Names are looked up. Which table is `orders`? Does it have a `total` column, and of what type? Which `=` operator fits those types? What columns will the result have? 3. **Plan and run.** The server chooses how to execute the statement and runs it. Values are in hand by this point. In PostgreSQL, `PREPARE` does the first two steps and `EXECUTE` does the third, so the split is visible in the statements. A missing table fails at `PREPARE`, before any value could have been supplied. Even when a driver sends text and bound values in one round trip, the server still parses the text first and takes the values as separate data. ## Why a name cannot wait A name is not one more input to the statement. It determines what the statement is: - **Grammar.** After `FROM` the grammar requires a table reference, and a placeholder is not one. - **Columns.** Whether `total` means anything depends on which table was named. - **Types.** Types of expressions, and therefore which operators and functions apply, follow from the columns. - **Result shape.** A client can ask for a prepared statement's result columns before executing it. With a variable table, there would be nothing to report. A value has none of these effects. It only flows through a structure that is already settled. MySQL's manual states the same rule for its markers: they go where data values go, not keywords or identifiers. ## What happens in each position The failure differs by position, and the silent cases are the dangerous ones. These results were observed on PostgreSQL. | Written with `$1` bound to a name | Result | |---|---| | `SELECT * FROM $1` | Syntax error at `PREPARE` | | `SELECT id FROM orders ORDER BY $1` | Runs; sorted by a constant, so no useful order | | `SELECT $1 FROM orders` | Runs; returns the text `total` once per row | | `SELECT id FROM orders WHERE $1 = 'open'` | Runs; compares the text to `'open'`, so all rows or none | | `ORDER BY total $1`, binding `DESC` | Syntax error; `ASC` and `DESC` are keywords | `ORDER BY` deserves attention because of how it is written by hand. In PostgreSQL, a literal `ORDER BY 1` means the first output column, and a literal `ORDER BY 'created_at'` is rejected as a non-integer constant. A bound parameter gets neither treatment. It is just an expression with a fixed value, so a bound `1` is not an ordinal and a bound string raises no complaint. A bound sort key behaves the same way in SQLite. Not every engine is this quiet: SQL Server rejects a bare variable as an `ORDER BY` item. PostgreSQL's `EXPLAIN` makes the symptom easy to spot, because there is no Sort step. ```sql -- PostgreSQL: runs without error and does not sort by created_at. PREPARE by_col (text) AS SELECT id FROM orders ORDER BY $1; EXECUTE by_col('created_at'); -- PostgreSQL: rejected at PREPARE. PREPARE from_col (text) AS SELECT * FROM $1; -- ERROR: syntax error at or near "$1" ``` ## "Values can be bound" has limits too The rule is one-way: a name can never be bound, but not every value can be either. - **Statement type.** In PostgreSQL, parameters work only in `SELECT`, `INSERT`, `UPDATE`, `DELETE`, `MERGE` and some commands containing one of these. In other statements, such as DDL, even a plain data value has to be written into the text. - **One parameter, one value.** `IN ($1)` bound to `'a,b'` compares against a single string with a comma in it. ## Drivers that emulate preparing Some client libraries do not prepare on the server. They substitute each value into the text as a quoted literal before sending it. This does not rescue names: `FROM ?` becomes `FROM 'orders'`, and a string literal is not a table reference. A few libraries offer a separate placeholder for identifiers, but that is client-side quoting that produces text. The server still sees a name in the statement, never a bound one. ## What to do instead Since a run-time name cannot travel as a parameter, it has to be part of the statement text, and the values stay bound beside it. In a multi-tenant admin report that picks its table and sort column, the statement is assembled with those two names, and the tenant id remains `$1`. The name has to be quoted or chosen from a fixed list, because an unquoted name taken from input is injectable. Choosing between those is its own question; the point here is that binding was never available.

  • A PostgreSQL endpoint binds its sort column with ORDER BY $1 and tests pass, yet users report unsorted results. Why did the tests miss it?
    Nothing raises an error, because a parameter is a legal expression there. Every row gets the same sort key, so rows come back in whatever order the plan produces, which on a small test table is often insertion order and can look right. A test has to insert rows out of order and assert on the sequence.
  • What does SELECT $1 FROM orders return in PostgreSQL when bound to the text 'total'?
    It returns the text `total` once per row of `orders`, not the column's values. A parameter in the select list is a value, so the output column holds that constant and the real column is never read. The row count is right, so a quick check can miss that every value is wrong.
  • Does a driver that emulates prepares by substituting values into the text make FROM ? work?
    No. The driver substitutes a quoted literal, so the server receives `FROM 'orders'`, which is a syntax error because a string literal is not a table reference. A separate identifier placeholder, where a library offers one, is client-side quoting that produces statement text, so it is not binding.
  • In PostgreSQL, can you bind a value in a DDL statement such as ALTER TABLE?
    No. Parameter symbols work only in optimizable commands: `SELECT`, `INSERT`, `UPDATE`, `DELETE`, `MERGE` and certain commands containing them. In other statement types, called utility statements, even a plain data value has to be written into the text. So being a value is necessary for binding but does not always suffice.

A prepared statement is like a printed form. You fill in its boxes each time you use it, but which questions the form asks were settled when it was printed. Writing the word 'orders' in a box only gives you a box containing that word; it does not reprint the form.

  • A bind parameter is pasted into the text before parsing, so it can hold a table name.
  • ORDER BY $1 sorts by whichever column name gets bound at run time.
  • Engines refuse to bind names purely as a security policy against injection.
  • In PostgreSQL, a bound 2 in ORDER BY $1 means the second output column.
  • Anything that is a value can be bound, in every kind of statement.
  • If the statement ran without error, the bound sort column must have taken effect.
open as a page

In SQL, what is dynamic SQL, and how do you tell when a static statement with bind parameters is already enough?

level: juniorimportance: should knowfreq 34%

basics

~20 s

Dynamic SQL is statement text assembled at run time and then sent to the server. A static statement with bind parameters is enough whenever only values change, not the tables, columns, clauses or sort order.

open as a page

In SQL, what do the PREPARE and EXECUTE statements do, and how do their forms differ between PostgreSQL and MySQL?

level: middleimportance: should knowfreq 38%

basics

~10 s

PREPARE stores a named statement with placeholders on the current session, and EXECUTE runs it with values. PostgreSQL writes PREPARE name AS statement with $1; MySQL writes PREPARE name FROM a string with ?.

open as a page

In SQL, what does double-quoting a table or column name chosen at run time guarantee, and what does it leave for an allowlist to decide?

level: middleimportance: should knowfreq 29%

basics

~20 s

Double quotes make text read as exactly one name, preserving case and escaping embedded quotes. They do not check that the name exists or that this caller may use it; an allowlist of names held in code decides that.

open as a page

In SQL, a multi-tenant admin report has a dozen optional filters, some taking lists; how do you build the statement, and why not one catch-all predicate?

level: seniorimportance: should knowfreq 36%

basics

~20 s

Start from the fixed tenant predicate and append one bound predicate per filter present, expanding lists into placeholders or an array. A catch-all such as ($2 IS NULL OR col = $2) can waste indexes and cannot ask for NULL.

open as a page

As a lead, when would you allow dynamic SQL in a codebase at all, and what standing rules would you set around it?

level: principalimportance: nice to knowfreq 21%

basics

~20 s

Allow dynamic SQL only where the statement's shape truly varies and fixed statements cannot cover it, after asking whether the data model causes the variation. Then require one shared builder, fixed fragments, allowlisted names and bound values.

open as a page