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?
answer
- exactly one name, however spelled
- quoting changes case matching
- well-formed is not permitted
- schema.table is two names
- map the choice to a fixed name
basics
~20 sDouble 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.
solid answer
~50 sBecause a name cannot be bound, it goes into the text, and double quotes, or backticks in MySQL's default mode, make that text read as exactly one identifier: embedded quotes are doubled, case is preserved, and a space or reserved word becomes legal. That also changes matching. PostgreSQL folds unquoted names to lower case, so a table created as `Orders` is really `orders` and `"Orders"` is not found, while Oracle folds to upper case. Quoting says nothing about whether this caller may read that name: `"users"` is perfectly quoted. It also cannot express anything that is not one name, so `"sales.orders"` is a single name with a dot in it and `"DESC"` is not a keyword. An allowlist closes that gap. Code maps the caller's choice to a fixed name, so the caller's text never reaches the statement, and quoting is left to handle only that name's own spelling.
code
sql · 6 linesCREATE TABLE Orders (id int);
SELECT count(*) FROM orders; -- works
SELECT count(*) FROM ORDERS; -- works
SELECT count(*) FROM "orders"; -- works
SELECT count(*) FROM "Orders"; -- ERROR: relation "Orders" does not existgo deeper
Recall that names go in the text, that double quotes make the text one name with case preserved, and that embedded quotes are doubled.
Predict the edges: a mixed-case quoted name that stops matching, a dotted name read as one object, and a keyword that quoting cannot produce. Then explain why the allowlist is a map.
Separate the two jobs clearly. An allowlist decides which names may appear and quoting decides how they are spelled. Say why a catalog check answers neither question for a given caller.
Put allowlists beside the report they belong to, so adding a sortable column is a reviewed, one-line decision, not something that follows automatically from a schema change.
## What a quoted identifier is An **identifier** is the name of a table, column or other object. A **quoted identifier** is one wrapped in the engine's identifier quotes, and the quotes change how the parser reads it: - **Exactly one token.** Everything between the quotes is the name, including spaces, punctuation and reserved words. `"order"` is a legal name; bare `order` is not. - **Embedded quotes are doubled.** A name holding a quote character is written `"a""b"`, and that doubling is what stops text from ending the name early. - **Case is kept.** On engines that fold unquoted names, such as PostgreSQL and Oracle, a quoted name is matched as written. Run-time names need this because a name cannot be a bind parameter, so it has to go into the statement text, and an unquoted one taken from input is injectable. ## The quote character varies Double quotes are the SQL standard's identifier quotes, but the engines disagree in the ways that matter. | Engine | Identifier quotes | Case matching | |---|---|---| | PostgreSQL | `"name"` | unquoted names fold to lower case | | Oracle | `"name"` | unquoted names fold to upper case | | MySQL | `` `name` ``, and double quotes only with `ANSI_QUOTES` on | table names depend on server settings, quoted or not | | SQL Server | `[name]`, and double quotes with `QUOTED_IDENTIFIER` on | depends on collation, quoted or not | Two traps follow from this. Quoting can **break** a match that worked unquoted: in PostgreSQL, a table created as `CREATE TABLE Orders` is stored as `orders`, so `orders` and `ORDERS` both find it while `"Orders"` does not. And in MySQL's default mode a double-quoted word is a **string**, so `ORDER BY "total"` sorts by a constant rather than failing. ## PostgreSQL's helpers Do not hand-roll the doubling. PostgreSQL provides two helpers, and both can be called from plain SQL. ```sql SELECT quote_ident('orders'); -- orders (no quotes needed) SELECT quote_ident('Orders'); -- "Orders" (would be case-folded) SELECT quote_ident('a"b'); -- "a""b" (embedded quote doubled) SELECT format('%I.%I', 'sales', 'orders'); -- sales.orders ``` `quote_ident` adds quotes only when they are needed: when the text contains non-identifier characters or would be case-folded. `format`'s `%I` does the same job inside a template. ## What quoting leaves open Quoting answers one question: will this text be read as one name? Everything else stays open. - **Is the caller allowed this name?** `"users"` is quoted correctly. It is also a table this report has no business reading. - **Is it the right tenant's?** With per-tenant tables, `"orders_tenant_b"` is a valid name for someone in tenant A. - **Does the name exist?** A well-formed name can still be unknown, which surfaces as a run-time error the caller sees. - **Is it more than one name?** `quote_ident('sales.orders')` gives `"sales.orders"`, one name with a dot in it, and the lookup fails. Quote each part separately. - **Is it a name at all?** `ASC` and `DESC` are keywords, so `ORDER BY total "DESC"` is a syntax error. An expression fares no better: `"lower(name)"` asks for a column literally called that. ## What an allowlist decides An **allowlist** is a fixed set of permitted names held in code. The important form is a map, not a yes-or-no check: 1. The caller sends a token such as `created`. 2. Code looks it up in a map owned by that report: `created` to `created_at`, `total` to `total_amount`. 3. A miss is rejected as bad input. It never turns into a query. 4. A hit writes the **map's** value into the statement, so the caller's text never reaches it. Sort direction works the same way: the token `desc` maps to the fixed keyword `DESC`. Checking the name against the catalog, such as `information_schema.columns`, is **not** the same thing. The catalog tells you a column exists, not that this caller may sort on it. Sorting by a `password_hash` column reveals information through the order of the rows even if the column is never displayed. ## How the two fit together They do different jobs and are not alternatives. An allowlist decides **which** names may appear. Quoting ensures the chosen name is **spelled** properly in the text. If every allowlisted name is a plain lower-case identifier, quoting adds little, though it stays harmless. If one is `"Order Total"`, the quotes belong in the map's value or the builder. In a multi-tenant admin report that picks its table and sort column, this leaves two small maps per report and a statement in which only their values appear in the text, with every filter value bound beside them.
- In MySQL's default mode, what does ORDER BY "total" do?With `ANSI_QUOTES` off, which is the default, double quotes make a string literal, so `"total"` is a constant. The statement runs and the rows are not sorted by the column. MySQL's identifier quote is the backtick, so a builder ported from PostgreSQL can lose its sort without any error.
- Is checking a requested column against information_schema a good enough allowlist?No. It proves that the column exists, not that this caller may use it. Every column of the table passes, including ones the report never displays, and sorting on a hidden column reveals its order. A real allowlist is a list per report, chosen on purpose, so a new column stays out until someone adds it deliberately.
- How do you write a run-time schema-qualified table name in PostgreSQL?Quote each part on its own: `format('%I.%I', schema_name, table_name)`. Passing `sales.orders` to `quote_ident` in one piece yields `"sales.orders"`, which names a single object with a dot in it, so the lookup fails with a missing relation error.
- Quoting a name proves that the caller is allowed to read it.
- Adding double quotes can never make a working name stop matching.
- quote_ident('sales.orders') produces a schema-qualified table reference.
- Double quotes mean an identifier on every engine, MySQL's default mode included.
- Validating a name against the catalog is the same thing as an allowlist.
- Sort direction can be handled by quoting the caller's ASC or DESC.