skip to content

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.