# 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:

| Rendering | When | Where |
| --- | --- | --- |
| Bound parameter `$n` | The value's type is unconstrained (`nil`/`:text`): text comparisons, `like`/`ilike`, regex matches, full-text query strings, raw RPC bodies, whole mutation payloads | `QueryExecutor.bind/3` (first clause), `Rpc` variadic/octet args, `Mutation` payload binding |
| Text-pinned cast parameter `($n::text)::<type>` | The value must coerce to a Postgres type (ranges, arrays, numeric/typed comparisons, quantifier arrays, RPC scalar arguments) | `QueryExecutor.bind/3` (second clause), `Rpc`'s `call_arg/2` scalar clause |

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:

```elixir
~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](guides/api.md#horizontal-filtering)). 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.