Designing Filter and Sort Query Parameter Schemas

uncontrolled surface area contractual, typed, gateable in CI

Step 1: Audit Existing Query Parameters

Before writing schema, inventory every filter and sort parameter currently in production. For each parameter record: field name, accepted values, which operators are actually checked server-side, what serialization format clients use, and whether it is documented anywhere. The audit almost always reveals:

• Sort parameters that accept freeform strings (?sort=name_asc, ?sort=NAME, ?orderBy=price)
• Filter parameters with undocumented operator support or operators that reach the database unvalidated
• Array filters with no maxItems cap, exposing the backing IN clause to arbitrarily large inputs
• Parameters documented as type: string in OpenAPI with no enum, pattern, or format constraint

This inventory becomes the allowlist. Anything not on the list is rejected with a 400.

Step 2: Design the OpenAPI Parameter Contract

OpenAPI 3.1 gives you two serialization styles for filter parameters:

• form + explode: true — standard for simple equality filters and repeatable values: ?status=active&status=draft
• deepObject + explode: true — required for operator-bearing filters: ?filter[price][gte]=10&filter[price][lte]=500

Declare them separately. Do not rely on a single opaque filter string parameter and parse it yourself — that recreates the problem.

# openapi.yaml — OpenAPI 3.1
paths:
  /products:
    get:
      summary: List products
      operationId: listProducts
      parameters:

        # ── Simple equality filter (form style, repeatable) ──────────────────
        - name: status
          in: query
          required: false
          style: form
          explode: true                          # ?status=active&status=draft
          schema:
            type: array
            maxItems: 5                          # bound the IN clause
            items:
              type: string
              enum: [active, draft, archived]   # exhaustive allowlist

        # ── Operator-bearing filter (deepObject style) ──────────────────────
        - name: filter[price]
          in: query
          required: false
          style: deepObject
          explode: true                          # ?filter[price][gte]=10
          schema:
            type: object
            additionalProperties: false          # reject unknown operator keys
            properties:
              gte:
                type: number
                minimum: 0
              lte:
                type: number
                minimum: 0
              eq:
                type: number
                minimum: 0

        - name: filter[createdAt]
          in: query
          required: false
          style: deepObject
          explode: true
          schema:
            type: object
            additionalProperties: false
            properties:
              gte:
                type: string
                format: date-time               # ISO 8601 — reject free text
              lte:
                type: string
                format: date-time

        # ── Tag filter (form array) ──────────────────────────────────────────
        - name: tag
          in: query
          required: false
          style: form
          explode: true                          # ?tag=electronics&tag=sale
          schema:
            type: array
            maxItems: 10
            items:
              type: string
              maxLength: 50
              pattern: '^[a-z0-9-]+$'           # slug format; rejects spaces/symbols

        # ── Sort (single grammar-driven string) ─────────────────────────────
        - name: sort
          in: query
          required: false
          schema:
            type: string
            default: "-createdAt"
            # Leading - = descending. Comma-separated for multi-column.
            # Allowlisted fields only — unknown fields fail the pattern.
            pattern: '^-?(price|createdAt|title|stock)(,-?(price|createdAt|title|stock))*$'
          description: >
            Comma-separated sort fields. Prefix a field with - for descending order.
            Example: sort=-price,createdAt means price descending, then createdAt ascending.
            Maximum two fields. Unknown fields are rejected with 400.

      responses:
        '200':
          description: Paginated product list
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/ProductListResponse'
        '400':
          description: Invalid filter, sort, or pagination parameter
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/Problem'

Three design decisions worth noting:

• additionalProperties: false on the filter[price] object means any operator not in [gte, lte, eq] — for example ?filter[price][between]=10,50 — is rejected by validators and Prism without writing a single line of custom validation code.
• The sort pattern encodes both the grammar (leading -, comma separator) and the allowlist (field names in the alternation group). A field not in that group cannot satisfy the pattern, so it never reaches the query builder.
• format: date-time on date filters means a validator rejects ?filter[createdAt][gte]=last-week with a type error, not a database error.

Step 3: Build a Zod Parser with a Field Allowlist

The OpenAPI spec documents the contract for clients and tools. The Zod parser enforces it at runtime. They must agree exactly — divergence between the two is its own class of contract bug, covered in the runtime validation with Zod discipline.

// query-schema.ts — Zod 3.23 filter and sort parser

import { z } from "zod";

// ── Allowed sort fields (single source of truth) ─────────────────────────────
const SORT_FIELDS = ["price", "createdAt", "title", "stock"] as const;
type SortField = (typeof SORT_FIELDS)[number];

// Regex built from the allowlist — avoids duplication with the OpenAPI pattern
const sortFieldPattern = SORT_FIELDS.join("|");
const SORT_REGEX = new RegExp(
  `^-?(${sortFieldPattern})(,-?(${sortFieldPattern}))*$`
);

// ── Operator schemas per filterable field ────────────────────────────────────
const priceFilter = z
  .object({
    gte: z.coerce.number().min(0).optional(),
    lte: z.coerce.number().min(0).optional(),
    eq:  z.coerce.number().min(0).optional(),
  })
  .strict()                          // reject ?filter[price][between]=…
  .optional();

const createdAtFilter = z
  .object({
    gte: z.string().datetime().optional(),
    lte: z.string().datetime().optional(),
  })
  .strict()
  .optional();

// ── Root query schema ────────────────────────────────────────────────────────
export const productListQuery = z
  .object({
    // Simple equality filters
    status: z
      .array(z.enum(["active", "draft", "archived"]))
      .max(5)
      .optional(),

    tag: z
      .array(z.string().max(50).regex(/^[a-z0-9-]+$/))
      .max(10)
      .optional(),

    // Operator-bearing filters — Express parses deepObject into nested objects
    "filter[price]":     priceFilter,
    "filter[createdAt]": createdAtFilter,

    // Sort
    sort: z
      .string()
      .regex(SORT_REGEX, {
        message: `sort must be comma-separated fields from [${SORT_FIELDS.join(", ")}], each optionally prefixed with -`,
      })
      .default("-createdAt"),

    // Pagination (owned by the pagination contract — imported here for completeness)
    limit:  z.coerce.number().int().min(1).max(100).default(20),
    cursor: z.string().regex(/^[A-Za-z0-9_-]+$/).optional(),
  })
  .strict();  // unknown query params → 400, not silent drop

export type ProductListQuery = z.infer<typeof productListQuery>;

The .strict() call at the root schema level is the runtime mirror of additionalProperties: false — an unrecognized query parameter is a contract violation, not noise to filter out silently. This matters: it catches client bugs early, and it closes a class of information-leakage attacks where an attacker probes for undocumented parameters.

Step 4: Wire the Parser as Express Middleware

Mount the query parser before the route handler. Parse and coerce once, then pass the typed result downstream so the query builder never touches raw strings.

// products-router.ts — Express 4.x, Zod 3.23

import { Router, Request, Response } from "express";
import { productListQuery, ProductListQuery } from "./query-schema";
import { buildProductQuery } from "./query-builder";

const router = Router();

router.get("/products", (req: Request, res: Response) => {
  // Parse and coerce in one step; safeParse never throws
  const result = productListQuery.safeParse(req.query);

  if (!result.success) {
    // Return structured errors — see Designing Robust Error Response Contracts
    res.status(400).json({
      type: "https://example.com/errors/invalid-query",
      title: "Invalid query parameters",
      status: 400,
      errors: result.error.flatten().fieldErrors,
    });
    return;
  }

  const query: ProductListQuery = result.data;

  // Build query safely — sort fields come from the schema, not the raw string
  const dbQuery = buildProductQuery(query);
  const products = await dbQuery.execute();

  res.json({ data: products, meta: { has_more: products.length === query.limit } });
});

// query-builder.ts — safe SQL construction from typed query

import type { ProductListQuery } from "./query-schema";

// Allowlist Map: sort field → safe SQL fragment (never interpolate user input)
const SORT_COLUMN: Record<string, string> = {
  price:     "p.price",
  createdAt: "p.created_at",
  title:     "p.title",
  stock:     "p.stock_count",
};

export function buildProductQuery(q: ProductListQuery) {
  const conditions: string[] = [];
  const params: unknown[] = [];

  // Status filter (array → IN clause, safely parameterized)
  if (q.status && q.status.length > 0) {
    const placeholders = q.status.map((_, i) => `$${params.length + i + 1}`).join(", ");
    conditions.push(`p.status IN (${placeholders})`);
    params.push(...q.status);
  }

  // Price filter (operator → pre-written SQL fragment via allowlist)
  const priceOps: Record<string, string> = { gte: ">=", lte: "<=", eq: "=" };
  const pf = q["filter[price]"];
  if (pf) {
    for (const [op, sql] of Object.entries(priceOps)) {
      if (pf[op as keyof typeof pf] !== undefined) {
        params.push(pf[op as keyof typeof pf]);
        conditions.push(`p.price ${sql} $${params.length}`);
      }
    }
  }

  // Sort (field extracted from allowlist Map — raw string never touches SQL)
  const orderClauses = q.sort.split(",").map((token) => {
    const desc  = token.startsWith("-");
    const field = desc ? token.slice(1) : token;
    const col   = SORT_COLUMN[field];        // always defined — Zod pattern guarantees it
    return `${col} ${desc ? "DESC" : "ASC"}`;
  });
  orderClauses.push("p.id DESC");            // unique tiebreaker — see cursor-vs-offset guide

  const where = conditions.length > 0 ? `WHERE ${conditions.join(" AND ")}` : "";
  return db.raw(
    `SELECT * FROM products p ${where} ORDER BY ${orderClauses.join(", ")} LIMIT $${params.length + 1}`,
    [...params, q.limit + 1]                 // fetch +1 to compute has_more
  );
}

The SORT_COLUMN Map is the injection barrier. The raw client string (-price) is parsed, split, and matched against a key in a static object. No part of token ever appears in the SQL fragment — only the pre-written value p.price DESC does.

Before / After

Before — ad-hoc, unvalidated:

// ❌ raw query string reaches the ORM
router.get("/products", async (req, res) => {
  const { sort, status, "filter[price][gte]": priceGte } = req.query;
  const rows = await db("products")
    .where(status ? { status } : {})
    .where(priceGte ? db.raw(`price >= ${priceGte}`) : {})  // injection vector
    .orderByRaw(sort || "created_at DESC")                   // sort injection
    .limit(100);                                             // unbounded
  res.json(rows);
});

Problems: ?sort=1;DROP TABLE products-- is structurally possible. ?filter[price][gte]=0 OR 1=1 bypasses the price filter. ?status=superadmin passes silently. No OpenAPI documentation means generated clients type everything as string.

After — governed, typed:

// ✓ Zod parses and coerces; buildProductQuery uses only allowlisted columns
router.get("/products", async (req, res) => {
  const result = productListQuery.safeParse(req.query);
  if (!result.success) return res.status(400).json({ errors: result.error.flatten().fieldErrors });
  const rows = await buildProductQuery(result.data).execute();
  res.json({ data: rows.slice(0, result.data.limit), meta: { has_more: rows.length > result.data.limit } });
});

The route handler is 4 lines. All validation, coercion, and SQL construction live in typed functions with a clear allowlist boundary.

Verification

Lint the spec to confirm all parameters carry constraints:

$ npx spectral lint openapi.yaml --ruleset .spectral.yaml --fail-severity warn
OpenAPI 3.x detected
No results with a severity of 'error' or above found!

Probe the Prism mock server for contract violations:

$ npx prism mock openapi.yaml --port 4010 &

# Unknown sort field → 400
$ curl -s -o /dev/null -w "%{http_code}\n" "http://localhost:4010/products?sort=password"
400

# Unknown operator → 400 (additionalProperties: false on filter object)
$ curl -s -o /dev/null -w "%{http_code}\n" \
  "http://localhost:4010/products?filter%5Bprice%5D%5Bbetween%5D=10%2C50"
400

# Valid multi-sort → 200
$ curl -s -o /dev/null -w "%{http_code}\n" \
  "http://localhost:4010/products?sort=-price%2CcreatedAt&filter%5Bprice%5D%5Bgte%5D=10"
200

# Invalid status value → 400
$ curl -s -o /dev/null -w "%{http_code}\n" \
  "http://localhost:4010/products?status=superadmin"
400

Unit-test the Zod parser directly — no HTTP overhead:

// query-schema.test.ts — Vitest or Jest
import { productListQuery } from "./query-schema";

test("rejects unknown sort field", () => {
  const r = productListQuery.safeParse({ sort: "password" });
  expect(r.success).toBe(false);
  expect(r.error?.flatten().fieldErrors.sort).toBeDefined();
});

test("accepts multi-column descending sort", () => {
  const r = productListQuery.safeParse({ sort: "-price,createdAt" });
  expect(r.success).toBe(true);
  expect(r.data?.sort).toBe("-price,createdAt");
});

test("rejects unknown query param in strict mode", () => {
  const r = productListQuery.safeParse({ unknownParam: "x" });
  expect(r.success).toBe(false);
});

test("rejects unknown operator on price filter", () => {
  const r = productListQuery.safeParse({ "filter[price]": { between: "10,50" } });
  expect(r.success).toBe(false);
});

test("coerces price gte to number", () => {
  const r = productListQuery.safeParse({ "filter[price]": { gte: "10" } });
  expect(r.success).toBe(true);
  expect(r.data?.["filter[price]"]?.gte).toBe(10);
});

Edge Cases and Caveats

Injection safety with deepObject style. Express parses ?filter[price][gte]=10 into req.query["filter[price]"] = { gte: "10" } — the bracket notation is collapsed into a nested key, not an array. The Zod priceFilter schema with .strict() means any key beyond {gte, lte, eq} causes a parse failure. The buildProductQuery function then maps valid operator strings to pre-written SQL fragments via a static object — never interpolating the operator string itself into SQL. The two layers (Zod allowlist + Map lookup) make operator injection structurally impossible regardless of what the client sends.

Deep object style and framework compatibility. Not all HTTP frameworks parse filter[price][gte] the same way. Express with qs (the default query string parser) handles it correctly. Node’s built-in URLSearchParams treats it as a literal key string "filter[price][gte]", not a nested object. If you change frameworks or add a gateway that transforms query strings, validate that nested parameter parsing behavior is preserved end-to-end before deploying a deepObject-style filter contract.

Default values and documented behavior. The sort parameter defaults to "-createdAt" in both the OpenAPI schema and the Zod parser. That default must be consistent, indexed, and documented. If the default sort field is later removed from the allowlist — because the column is dropped — requests with no explicit sort will start returning 400. Treat the default as part of the public contract and version it deliberately. The same applies to limit defaults: changing a default from 20 to 50 is a breaking change for clients who depend on response size for rendering budgets or pagination UI.

Frequently Asked Questions

What is the safest way to expose comparison operators like gte and lte in query parameters?

Use deep object style (filter[price][gte]=10) declared explicitly in OpenAPI with style: deepObject and a closed set of operator keys. Never accept free-form operator strings from the client — enumerate exactly which operators each field supports so unknown operators are rejected with a 400 before reaching the database.

How do I prevent SQL injection through filter and sort parameters?

Validate every filter field and sort field against a server-side allowlist before building a query. Never interpolate a raw query-parameter value into SQL. With an allowlist-only approach the sort field becomes a safe index into a Map of pre-written ORDER BY fragments, making injection structurally impossible.

Can I support multi-column sorting in OpenAPI without a custom pattern?

Yes. Declare sort as a single string with a regex pattern that encodes the grammar: a leading minus for descending, field names separated by commas, e.g. pattern: '^-?(price|createdAt|title)(,-?(price|createdAt|title))*$'. OpenAPI 3.1 supports this directly in the parameter schema; validators and Prism enforce it at the parameter level.

Should filter parameters use form style or deepObject style in OpenAPI?

Use form style with explode: true for simple equality filters (?status=active&status=draft), and deepObject style for operator-bearing filters (?filter[price][gte]=10). Mixing both on the same endpoint is valid — just declare each parameter with its own style and explode combination and document which style applies to which parameter family.

What happens if a client sends an unknown filter field?

With Zod .strict() on the query schema, unknown fields produce a ZodError immediately and the handler returns a 400 before any database interaction. Without strict mode, unrecognized fields are silently dropped, hiding contract violations. Always reject rather than ignore.