Preventing Filter Injection in Query Parsers

This page belongs to Advanced Filtering Operators in the Query Patterns & Data Shaping Strategies reference. A filter parameter is the largest attack surface a read endpoint has: it accepts a small language, and every language accepted from an untrusted source needs a parser, an allowlist and a cost budget rather than string concatenation and hope.

The decision trigger

Symptom Risk
Filter values interpolated into SQL strings classic SQL injection
Sort or filter field names used as column names directly identifier injection — parameters cannot help
A filter can reference a field not in the response schema data exposure through inference
Deeply nested AND/OR accepted without limit parser or planner exhaustion
LIKE '%term%' allowed on any text column full scans on demand
Database error text returned to the caller schema disclosure

Spec snippet: declare what is filterable

# openapi.yaml (OpenAPI 3.1.0) — the allowlist is part of the contract
paths:
  /invoices:
    get:
      operationId: listInvoices
      parameters:
        - name: filter
          in: query
          description: >
            RSQL expression over the filterable fields. Supported fields:
            `status`, `currency`, `total_minor`, `created_at`, `customer_id`.
            Supported operators: `==`, `!=`, `=gt=`, `=ge=`, `=lt=`, `=le=`,
            `=in=`, `=out=`, combined with `;` (and) and `,` (or).
          schema:
            type: string
            maxLength: 512                # a hard bound before parsing begins
          examples:
            simple:  { value: "status==paid" }
            complex: { value: "status=in=(paid,open);total_minor=gt=1000" }
      responses:
        "422":
          $ref: "#/components/responses/ValidationFailed"

maxLength on the parameter schema is the cheapest control available: it bounds the parser’s input before any parsing begins, so a megabyte of nested parentheses never reaches the tokeniser.

Defence stages between the query string and the database The raw filter passes a length limit, is tokenised into a tree, has its fields and operators checked against an allowlist, has its values coerced to declared types, is scored against a cost budget, and only then is compiled into parameterised SQL. raw filter untrusted 1 · length ≤ 512 chars 2 · parse typed tree 3 · allowlist field +operator 4 · coerce by declaredtype 5 · cost budget then bind params no stage is optional — each blocks a different class of attack 1 blocks parser exhaustion · 3 blocks identifier injection · 4 blocks type confusion · 5 blocks denial of service parameter binding alone blocks only value injection

Step-by-step

Step 1 — Define the allowlist as data, next to the resource

// filterable.ts — the single source of truth for what may be filtered
export interface FilterableField {
  /** The column expression. NEVER derived from user input. */
  column: string;
  type: "string" | "integer" | "money" | "uuid" | "timestamp" | "enum";
  operators: ReadonlyArray<Operator>;
  enumValues?: readonly string[];
  /** Relative cost; expensive operators need a budget. */
  cost?: number;
}

export const INVOICE_FILTERS: Record<string, FilterableField> = {
  status:      { column: "i.status",       type: "enum",      operators: ["==", "!=", "=in="], enumValues: ["draft", "open", "paid", "void"] },
  currency:    { column: "i.currency",     type: "string",    operators: ["==", "=in="] },
  total_minor: { column: "i.total_minor",  type: "integer",   operators: ["==", "!=", "=gt=", "=ge=", "=lt=", "=le="] },
  created_at:  { column: "i.created_at",   type: "timestamp", operators: ["=gt=", "=ge=", "=lt=", "=le="] },
  customer_id: { column: "i.customer_id",  type: "uuid",      operators: ["==", "=in="] },
  // Deliberately absent: internal_notes, risk_score, deleted_at.
};

Keeping the map next to the resource makes the omissions reviewable. A field that is not listed cannot be filtered, cannot be inferred by binary search over the result count, and cannot appear in a WHERE clause by accident — which is a stronger property than “we validated the input”.

Step 2 — Parse, then validate the tree

// compile-filter.ts — AST in, parameterised SQL out
type Node =
  | { kind: "and" | "or"; children: Node[] }
  | { kind: "cmp"; field: string; op: Operator; value: string | string[] };

const MAX_DEPTH = 4;
const MAX_TERMS = 20;
const COST_BUDGET = 40;

export function compileFilter(ast: Node, fields = INVOICE_FILTERS) {
  const params: unknown[] = [];
  let terms = 0;
  let cost = 0;

  function walk(node: Node, depth: number): string {
    if (depth > MAX_DEPTH) throw new FilterError("/filter", "too_deep", { max: MAX_DEPTH });

    if (node.kind === "and" || node.kind === "or") {
      const joined = node.children.map(c => walk(c, depth + 1)).join(node.kind === "and" ? " AND " : " OR ");
      return `(${joined})`;
    }

    if (++terms > MAX_TERMS) throw new FilterError("/filter", "too_many_terms", { max: MAX_TERMS });

    const field = Object.prototype.hasOwnProperty.call(fields, node.field) ? fields[node.field] : undefined;
    if (!field) throw new FilterError(`/filter/${node.field}`, "unknown_field");        // allowlist
    if (!field.operators.includes(node.op)) {
      throw new FilterError(`/filter/${node.field}`, "operator_not_allowed", { allowed: field.operators });
    }

    cost += field.cost ?? 1;
    if (cost > COST_BUDGET) throw new FilterError("/filter", "query_too_expensive", { budget: COST_BUDGET });

    const coerced = coerce(node.value, field);            // throws on type mismatch
    if (node.op === "=in=") {
      const list = (coerced as unknown[]).slice(0, 100);   // bound IN list size
      const placeholders = list.map(v => `$${params.push(v)}`).join(", ");
      return `${field.column} IN (${placeholders})`;      // column from the allowlist ONLY
    }
    return `${field.column} ${SQL_OP[node.op]} $${params.push(coerced)}`;
  }

  return { where: walk(ast, 0), params };
}

The column expression comes from the allowlist and the value from a bound parameter. At no point is user text concatenated into SQL — the field name is a lookup key, not a string that reaches the database.

Step 3 — Coerce values by declared type

# coerce.py — type coercion is a security control, not a convenience
from datetime import datetime
from uuid import UUID

def coerce(raw: str, field: dict):
    t = field["type"]
    try:
        if t == "integer":
            value = int(raw)
            if not -2**63 <= value < 2**63:
                raise ValueError("out of range")
            return value
        if t == "money":
            value = int(raw)                      # minor units only; never float
            if value < 0:
                raise ValueError("negative")
            return value
        if t == "uuid":
            return str(UUID(raw))                 # rejects anything not a UUID
        if t == "timestamp":
            return datetime.fromisoformat(raw.replace("Z", "+00:00"))
        if t == "enum":
            if raw not in field["enumValues"]:
                raise ValueError("not an allowed value")
            return raw
        if t == "string":
            if len(raw) > 200:
                raise ValueError("too long")
            return raw
    except (ValueError, TypeError) as exc:
        raise FilterError(field["column"], "invalid_value") from exc

Coercion closes the type-confusion class of bug, where a string where an integer is expected reaches a query planner that coerces it differently, or where an oversized integer overflows in a backend that does not check. It also short-circuits a surprising amount of probing: an attacker cannot test operators against a UUID column if nothing but a UUID parses.

Step 4 — Return errors that reveal nothing

{
  "type": "urn:api:errors:v1:validation-failed",
  "title": "Filter expression is not valid",
  "status": 422,
  "violations": [
    { "pointer": "/filter", "code": "unknown_field", "message": "Field 'internal_notes' cannot be filtered.",
      "params": { "allowed": ["status", "currency", "total_minor", "created_at", "customer_id"] } }
  ]
}

Listing the allowed fields is safe and helpful — they are already documented — while echoing the raw expression or a database error message is neither. A driver-level error string discloses table names, column types and sometimes fragments of other queries, which turns a rejected filter into a schema dump. The violation shape follows Validation Error Payloads.

Unsafe versus safe handling of a hostile filter An unsafe handler concatenates the field name into SQL and returns the database error, disclosing schema. A safe handler rejects the unknown field at the allowlist stage and returns a structured violation listing the permitted fields. unsafe safe ?filter=internal_notes==x' OR 1=1-- concatenated into the WHERE clause database evaluates it rows leaked, or 500 with the driver error text disclosing table and column names ?filter=internal_notes==x' OR 1=1-- parsed to a tree; field looked up in the allowlist — absent 422 · unknown_field lists the permitted fields only no SQL was ever constructed

Standard compliance

Concern Reference Note
Query parameter syntax RFC 3986 §3.4 Percent-decode before parsing, once
422 for an invalid filter RFC 9110 §15.5.21 The expression parsed but broke a rule
400 for an unparseable filter RFC 9110 §15.5.1 Syntax error in the expression itself
Violation reporting RFC 9457 §3.2 Extension members carrying the offending position
Parameter length bound OpenAPI 3.1 §4.8.12 maxLength on the parameter schema

One subtlety on the first row: decode percent-encoding exactly once, at the framework boundary. Double-decoding is its own vulnerability class, because %2527 becomes %27 on the first pass and ' on the second — slipping a quote past a validator that ran in between.

The allowlist is also a data-exposure control, not only an injection control:

Should this field be filterable? A field that is not in the response schema should not be filterable, because filtering on it lets a caller infer its value by binary search over result counts. is the field alreadyvisible in the response schema? yes no it may be filterable add it with an operator list and a cost, and document it in theparameter description leave it out filtering on a hidden field leaks it — result counts reveal thevalue by binary search the filterable-field map is part of the published contract, not an implementation detail

Cost, denial of service and safety

An allowlisted filter can still be ruinous. LIKE '%term%' on a hundred-million-row table is a full scan, and a caller may issue it a hundred times a second. Assign each operator a cost — equality on an indexed column is 1, a range scan is 3, a leading-wildcard match is 20 — sum the parsed expression, and reject above a budget. Back it with a statement timeout so anything that slips through cannot run indefinitely, and with the per-endpoint quotas from Rate Limiting & Quota Contracts. The three controls fail independently, which is the point.

SDK / codegen downstream effect

  // Generated client: the filter is a string, so the SDK cannot validate it
- await client.invoices.listInvoices({ filter: userSuppliedString });

  // A typed builder in the wrapper layer makes invalid filters unrepresentable
+ await client.invoices.listInvoices({
+   filter: f.and(
+     f.eq("status", "paid"),                  // field names are a union type
+     f.gt("total_minor", 1000),               // value type checked per field
+   ).toString(),
+ });

Deriving the builder’s field union from the documented allowlist means a client cannot even express a filter on internal_notes — the mistake becomes a compile error instead of a 422. The wrapper-layer pattern is the same one used for retries and pagination in Client SDK Generation Pipelines.

Common mistakes

Mistake Correct approach
Concatenating filter values into SQL Parse to a tree; bind values as parameters
Using the client’s field name as a column name Look the column up in an allowlist
Escaping input instead of allowlisting Allowlist fields, operators and types
No depth or term limits Cap depth, term count and IN list size
Unbounded expensive operators Assign costs and enforce a budget
Returning the database error message Return a structured violation with no internals
Double-decoding percent-encoded input Decode exactly once, at the boundary
Filterable fields not documented Document them; the allowlist is part of the contract

FAQ

Do parameterised queries alone prevent filter injection?

They prevent value injection, which is the most famous half of the problem, and they do nothing for the other half. Field names, sort columns, table aliases and operators cannot be bound as parameters — SQL simply does not allow an identifier placeholder — so any design that takes those from user input is concatenating strings no matter how careful it is about values. The fix is structural: the API field name is a key into an allowlist that yields a column expression you wrote, and anything not in that map is rejected before a query is built.

Is escaping user input a sufficient defence?

No, and reaching for it is usually a sign the design took a wrong turn earlier. Escaping is a denylist wearing a disguise: it requires you to know every metacharacter for every backend you might target, to apply it in the right order relative to decoding, and to keep that knowledge correct as backends change. One missed case is a vulnerability. Parsing into a typed tree and validating against an allowlist inverts the burden — the parser accepts only the grammar you defined, and every field, operator and value type is explicitly permitted rather than merely not-yet-forbidden.

How do I stop an expensive but legitimate filter from taking down the database?

Price it. Assign each operator a cost reflecting its likely plan — indexed equality 1, range 3, IN with a large list 5, leading-wildcard match 20 — sum the costs of the parsed expression, and reject anything above a budget with a clear 422. Add a statement timeout at the database connection so a query that slips through has a hard ceiling, and a per-endpoint rate limit so a caller cannot issue expensive-but-legal queries continuously. Each control catches what the others miss, and none of them requires guessing which queries an attacker will send.

Back to Advanced Filtering Operators