Injection safety

How user-controlled input reaches SQL. Every request produces a single parameterized statement, and almost every user value travels as a bound parameter — but not all of them can. This page consolidates the typed-literal 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 else of a request's values is ever interpolated. limit/offset are integers produced by the query parser; JSON-path array indices are interpolated only after matching ^-?\d+$ (pg_literal_or_index/1).

Why a literal at all

PostgreSQL coerces text into ranges, arrays, and other structured types only from an unknown-typed literal. A bound parameter arrives already typed as text, and text does not implicitly convert to int4range, tsrange, typed arrays, and friends — so col && $1 with a text parameter fails where col && '[1,5)'::int4range succeeds. 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 build '{…}'::<coltype>[].
• RPC arguments — a function call argument must coerce to the declared argument type; call_arg/2 inlines '<escaped>'::<argtype> for scalars (the full-text-search language modifier gets the same treatment, as '<escaped>'::regconfig).

In each case the value is passed through QueryExecutor.pg_literal/1, which wraps it in single quotes and doubles every embedded '. Under standard_conforming_strings (the server default since PostgreSQL 9.1, never disabled by Bier) backslash has no escape meaning inside a '…' literal, so quote-doubling is a complete escape: no value can terminate the literal. Only the cast after it 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 emits the escaped typed literal. 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; named scalar arguments inline '<escaped>'::<argtype> so Postgres coerces from an unknown literal. 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.

Identifiers

Every identifier — schema, relation, column, alias, RPC argument name, the SET LOCAL ROLE role — is rendered through QueryExecutor.quote_ident/1 (" doubled, wrapped in "…"), including names that were validated against the schema cache anyway. Request GUCs (request.jwt.claims, headers, cookies, …) are set via parameterized set_config($1, $2, true) calls.

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.