Injection safety

How user-controlled input reaches SQL. Every request produces a single parameterized statement, and every user value travels as a bound parameter. This page consolidates the binding strategy that is otherwise commented at each site (Bier.QueryExecutor.bind/3, Bier.Rpc's call_arg/2, and the SQL builders in Bier.Mutation), so security reviewers can audit the model in one place.

The rule

A user value is rendered in exactly one of two ways:

Nothing of a request's values is ever interpolated into the SQL text. limit/offset are integers produced by the query parser; JSON-path array indices are interpolated only after matching ^-?\d+$ (pg_literal_or_index/1); the full-text-search language modifier is the one small-vocabulary exception, rendered as an escaped '<lang>'::regconfig literal through pg_literal/1.

Why the cast is in the SQL text

PostgreSQL coerces text into ranges, arrays, and other structured types via its I/O-conversion casts, which require an explicit cast: col && $1 with a text parameter fails where col && ($1::text)::int4range succeeds. The inner ::text pins the parameter's inferred type to text (a bare $n::<type> would make the server type the parameter itself, forcing the driver to binary-encode the raw string as that type), and the outer cast runs the same input-conversion code — accepting the same values and raising the same errors — as an unknown '<v>'::<type> literal. The contexts that need this:

• Ranges and arrays — the structural operators (cs, cd, ov, sl, sr, nxr, nxl, adj) cast the value to the introspected column type.
• Typed comparisons — eq/gt/… against a non-text column, isdistinct, and the any/all quantifier forms, which cast to <coltype>[].
• RPC arguments — a function call argument must coerce to the declared argument type.

Binding instead of inlining also keeps the SQL text identical across requests that differ only in their values, which is what makes db_prepared_statements (and PostgreSQL's own plan cache) effective, and it mirrors what PostgREST executes.

Only the cast after the parameter is templated — see below.

What constrains the cast: quote_type/1

The ::type suffix is not user text either. QueryExecutor.quote_type/1 validates every cast against a conservative charset:

~r/^(?:[A-Za-z0-9_ \[\]\".]|\(\d+(?:,\d+)*\))+$/

anything else throws {:bad_request, :bad_cast} (HTTP 400). The charset admits schema-qualified, quoted, spaced, and array type names ("my schema".mytype[], timestamp with time zone) but excludes ', ; and - — a cast can neither re-open a string, terminate the statement, nor start a comment.

Parentheses are admitted in exactly one shape: a fully-formed digit list, \(\d+(?:,\d+)*\). That is PostgreSQL's own type-modifier syntax, and it is what lets a parameterized type through — numeric(4,2), character varying(255), timestamp(3) without time zone — both from introspection and from an explicit select=abv::numeric(4,2) cast (see the API reference). Because the group's contents are constrained to digits and commas, it cannot smuggle a function call: int4(version()) and int4(1);drop both fail the match, since the former's group holds non-digits and the latter has trailing text outside any group. A comma is reachable only inside such a group, never at top level.

It guards both trusted and untrusted type sources:

• the explicit select=col::cast from the query string (untrusted);
• introspected column types reaching bind/3 and Mutation's type_cast/1 (trusted output of format_type, constrained anyway).

The one cast site that bypasses quote_type/1 is Rpc's call_arg/2, whose types come verbatim from pg_proc introspection — never from the request. (The set-returning RPC path routes its arguments through QueryExecutor.bind/3 and is therefore covered.)

Site-by-site

QueryExecutor.bind/3 — the single funnel for read-path filter values. nil/:text types bind $n; everything else binds ($n::text)::<type>. bind_filter_value/3 picks the type from the introspected column (or the JSON-path arrow: ->> is :text, -> is jsonb), and in lists bind each element individually. Domain columns with a text data representation bind $n and parse it through the domain's cast function.

Rpc, call_arg/2 — variadic arguments and raw (octet-stream) bodies bind $n::type (the driver encodes them as the declared type); named scalar arguments bind ($n::text)::<argtype> so Postgres coerces the raw string server-side. Argument names are rendered with "name" => … keyword-call syntax through identifier quoting.

Bier.Mutation (insert_sql/upsert_sql/set_clause/where_clause) — payload values never appear in the SQL text at all: the whole JSON body is encoded and bound as one $1::text::jsonb parameter, and each target column is extracted per row by extract_expr/4 as (_e ->> '<col>')::<type> (-> without a cast for json/jsonb columns; the write-representation cast function for domains). The extraction key goes through pg_literal/1 and the cast through quote_type/1. where_clause/3 reuses QueryExecutor.render_node/2, so mutation filters follow the read-path rules above. The only verbatim interpolation is the column DEFAULT used by missing=default — taken from pg_catalog, not the request.

Where pg_literal/1 remains

QueryExecutor.pg_literal/1 (single quotes, embedded ' doubled — a complete escape under standard_conforming_strings, the server default since PostgreSQL 9.1 and never disabled by Bier) still renders a handful of structural strings that are not request values: JSON-path object keys, column names inside jsonb_build_object, and the full-text-search language modifier ('<lang>'::regconfig).

Identifiers

Every identifier — schema, relation, column, alias, RPC argument name — is rendered through QueryExecutor.quote_ident/1 (" doubled, wrapped in "…"), including names that were validated against the schema cache anyway. The per-request auth preamble (the role switch and the request.* GUCs) is one parameterized SELECT set_config($1, $2, true), … statement — the role travels as a bound value, never as SQL text.

Future work

Because every untrusted value funnels through bind/3, call_arg/2, or the bound-jsonb mutation payload, a property/fuzz test can target the model directly: generate adversarial filter values (quotes, casts, )/; splices) across operators and assert the built SQL parameterizes or escapes them — anchoring this document in CI rather than in review.