In PL/pgSQL, how do you build a dynamic statement whose table name and filter value come from parameters without opening an SQL injection hole?
answer
- variables are values, never names
- planned on every run
- identifiers versus values
- two quoting specifiers and one hole
- utility statements take no $n
basics
~20 sUse EXECUTE with format(): put table or column names in with %I, pass every data value as a $n placeholder bound by USING, and never concatenate raw input or use %s. EXECUTE plans each time and does not set FOUND.
solid answer
~40 sStatic SQL in PL/pgSQL can't take a table name from a variable, because variables become query parameters, which stand only for values. So I build the string and run it with `EXECUTE`. Identifiers go in through `format('%I.%I', ...)`, which double-quotes them; values go in as `$1`, `$2` bound with `USING`, so they are never parsed as SQL. `%L` is the fallback for utility statements such as `COMMENT ON`, which take no parameters; `%s` and plain `||` concatenation of input are the holes. I still check the name against the catalog or a known list, because quoting stops injection but not access to the wrong table. After `EXECUTE` I read `GET DIAGNOSTICS ... = ROW_COUNT`, since `FOUND` isn't set.
code
sql · 24 linesCREATE FUNCTION debit_partner_points(p_partner text, p_member bigint, p_points bigint)
RETURNS bigint
LANGUAGE plpgsql
AS $$
DECLARE
v_schema text := 'partner_' || p_partner;
v_rows bigint;
BEGIN
IF NOT EXISTS (SELECT 1 FROM pg_namespace WHERE nspname = v_schema) THEN
RAISE EXCEPTION 'unknown partner %', p_partner;
END IF;
-- unsafe: 'UPDATE ' || v_schema || '.member_balance ... = ' || p_member
EXECUTE format('UPDATE %I.member_balance SET points = points - $1 WHERE member_id = $2',
v_schema)
USING p_points, p_member;
GET DIAGNOSTICS v_rows = ROW_COUNT; -- FOUND is not set by EXECUTE
IF v_rows = 0 THEN
RAISE EXCEPTION 'member % not enrolled with %', p_member, p_partner;
END IF;
RETURN v_rows;
END;
$$;go deeper
Recall that table names cannot come from variables in static SQL and that EXECUTE runs a string. Know that values are passed with USING, not glued in.
Explain the split between %I for identifiers and $n with USING for values, what %L and %s do with NULL, and that EXECUTE re-plans every time.
Show the traps a review misses: dotted names under %I, quote_literal nulling the string, utility statements without parameters, FOUND not set, and validating names against the catalog.
Weigh dynamic SQL against schema design: a schema per partner forces it everywhere. Argue when a shared, partitioned table removes the need, and how to audit what remains.
## Why static SQL cannot do it Ordinary SQL inside a PL/pgSQL function is prepared once per session, and PL/pgSQL substitutes its variables as **query parameters**. Parameters can stand only for data values, so a variable in table position is not read as a table name: the tables and columns are fixed when the statement is prepared. When the schema, table or column must vary at run time, you need **`EXECUTE`**: ```sql EXECUTE command_string [ INTO [STRICT] target ] [ USING expression [, ...] ]; ``` `EXECUTE` takes a `text` expression, performs **no PL/pgSQL variable substitution** inside it, and **plans the command every time** it runs, with no plan caching. `INTO` and `INTO STRICT` work as they do for a static `SELECT INTO`. ## Values through USING, identifiers through %I The safe recipe has two halves, one for each kind of thing being inserted: 1. **Data values** become `$1`, `$2` placeholders filled by `USING`. They travel as typed parameters, never as SQL text, so a hostile value cannot change the statement's structure; the manual adds that it also avoids converting values to text and back. 2. **Identifiers** are spliced into the string with `format()`'s `%I`, which double-quotes the argument whenever it is not a plain lower-case identifier and doubles any embedded double quote, so the argument can only ever be read as one name. In the loyalty scheme each partner programme keeps balances in its own schema, `partner_<code>.member_balance`: ```sql EXECUTE format('UPDATE %I.member_balance SET points = points - $1 WHERE member_id = $2', 'partner_' || p_partner) USING p_points, p_member; ``` ## The format() specifiers | Specifier | Inserts the argument as | NULL argument | Same as | |---|---|---|---| | `%s` | raw text, no quoting | empty string | nothing: the injection hole | | `%I` | an identifier, double-quoted if needed | error "null values cannot be formatted as an SQL identifier" | `quote_ident` | | `%L` | a literal, single-quoted | the word `NULL`, unquoted | `quote_nullable` | ## Where USING stops and %L takes over `$n` placeholders work only in optimisable statements: `SELECT`, `INSERT`, `UPDATE`, `DELETE`, `MERGE` and commands containing one. **Utility statements** such as `COMMENT ON`, `CREATE TABLE` or `ALTER TABLE` take no parameters, so their data values must be embedded textually, with `%L` or `quote_nullable`. In DML, values should still go through `USING`. ## Traps that pass code review - **A dotted name passed to %I.** `format('%I', 'partner_acme.member_balance')` yields `"partner_acme.member_balance"`, a single identifier containing a dot, which names no table. Quote each part separately: `format('%I.%I', schema_name, table_name)`. - **`quote_literal` and NULL.** `quote_literal` returns NULL for NULL input, so concatenating it makes the whole command string NULL, and `EXECUTE` fails with "query string argument of EXECUTE is null". Use `quote_nullable`, `%L` or, better, `USING`. - **`= %L` with a NULL value** renders `= NULL`, which is never true. Write `IS NOT DISTINCT FROM` if NULL must match. - **`%s` for convenience.** It inserts the argument verbatim, so a crafted partner code can close the identifier and append statements of its own. - **`FOUND` after `EXECUTE`.** `EXECUTE` does not set it; read `GET DIAGNOSTICS v_rows = ROW_COUNT;` instead. ## Quoting is not authorisation `%I` guarantees the input is read as a name; it does not decide which names are allowed. A caller who passes another partner's code still reaches that partner's balances, if the function's role can read them. So validate the identifier too: look it up in `pg_namespace` or in a table of enrolled partners before building the string, and raise a clear error otherwise. That matters most when the function runs with its owner's privileges, which brings its own `search_path` hardening on the function definition.
- Why is USING preferred over %L for values in an UPDATE built with EXECUTE?With `USING`, values reach the executor as typed parameters, so there is no text round trip and no quoting step at all; the manual calls it both safer and more efficient. `%L` is also correct, quoting like `quote_nullable`, but it turns every value into text and back. Keep `%L` for utility statements, which accept no `$n` parameters.
- The dynamic SELECT runs inside a loop over thousands of partners; what does EXECUTE cost compared with static SQL?`EXECUTE` parses, analyses and plans its string on every execution, whereas static SQL in PL/pgSQL is prepared once per session and can reuse a cached plan. For a few calls that is noise; in a tight loop it adds up. Reduce the number of dynamic statements, for instance one statement per partner covering all its members rather than one per member.
- Doubling single quotes with replace() makes a concatenated EXECUTE string safe.
- A USING placeholder like $1 can stand for a table or column name.
- format('%I', 'schema.table') produces a schema-qualified table reference.
- %s and %I are interchangeable when the input comes from our own application.
- Once a name is quoted with %I there is no need to validate which table it is.
- FOUND tells you whether an EXECUTE'd UPDATE touched any rows.