For an ad-hoc reporting service, how much of the SQL text may be built from request input, and who owns that call?

## The question behind the question An ad-hoc reporting service is where the placeholder discipline meets product pressure. Users want to pick filters, columns and a sort order; every one of those is a structural choice that a bind parameter cannot express. The engineering question is not "can we build SQL from input" — the answer to that is settled — but **how much structural freedom the service gets, who grants it, and how anyone later proves the boundary held.** ## What is not negotiable One invariant survives every version of this design: **values are always bound, and structure comes only from fragments that exist in our source code.** A request may select among fragments; it may never supply one, and it may never be escaped into one. Every design below is a different answer to how large the fragment catalogue is, not to whether the catalogue exists. Stating this as an invariant rather than a guideline matters, because it converts an open-ended security review into a bounded one: the reviewer's job becomes reading a single catalogue. ## Shrinking the surface until it is reviewable The move that usually settles the argument is turning "arbitrary filters" into **a vocabulary**. Each supported filter is a named entry with a fragment and its own placeholders — `status IN (...)`, `created_at >= $n`, `total_cents BETWEEN $n AND $n+1`. A request names filters and supplies values; the builder appends fragments and their arguments in step. Sorts come from an allowlist. Projections come from a fixed column set per report. Limits are clamped in code, not requested. The structural decision that makes this reviewable is confinement: **one package assembles SQL, and nothing else in the service concatenates statement text.** That is what allows the security review to be a one-time exercise on a small file rather than a recurring tax on every handler. It also gives you somewhere to put the tests. ## Enforcement, since a policy nobody can check is a wish - **A single construction site**, as above, plus a review rule that any new string concatenation producing SQL outside it is a blocking comment. - **Tests over the catalogue** — every fragment executes against the test schema, so a renamed column fails the build instead of a rare report path. - **Input bounds at the API edge** — list lengths, page sizes, number of filters — so query cost is not dictated by the caller. - **The query log as the audit** — the set of distinct statement texts a report endpoint emits should be small and enumerable, with values listed as bound parameters. That check is cheap, is repeatable after any change, and is the artefact to hand a reviewer who asks how you know. ## Who owns the decision This is where the honest answer differs from the technical one. The service's lead proposes the surface and carries the product argument; **the security reviewer owns the policy and can refuse the dynamic-identifier path outright**, and that refusal is legitimate even when the implementation is correct, because the reviewer is buying down the risk of every *future* change to that code, not just this one. Treating a refusal as an obstacle to route around is how a narrow catalogue turns into a general query builder over two years of small extensions. So the lead's real job is to arrive with the narrowest surface that satisfies the actual user need, an explicit list of what is excluded, and the enforcement above — not with a flexible design plus assurances. ## Knowing when to stop Some requirements genuinely exceed what a catalogue can express: analysts who want to write their own joins, or a long tail of one-off questions. Extending the reporting service to meet those is the expensive mistake, because each extension widens a surface that the whole application shares. The alternative is to stop building and change the shape of the problem — a read-only replica with database-level authorisation, per-user roles, statement timeouts, row caps and its own audit trail, or an export the analysts query elsewhere. That trades a security surface for operational cost, and the trade should be made explicitly with the people paying for it. ## The failure this prevents The failure mode I am designing against is not one injection. It is drift: a service that started with three filters, grew a "just this one" free-text predicate for an internal tool, and now has a code path where a request fragment reaches the statement text. It is found later by a reviewer reading the query log and seeing statement texts nobody can enumerate. Every control above exists to make that drift visible while it is still one pull request.