# Filtering and sorting supported where appropriate

- **Pattern:** `ab-002201` (`saas-api-design.request-response.filtering-sorting`)
- **Severity:** low
- **Lifecycle:** active
- **Last modified:** 2026-04-18
- **Canonical URL:** https://auditbuffet.com/patterns/ab-002201
- **License:** CC-BY-4.0 — attribute to AuditBuffet Pattern Catalog (https://auditbuffet.com/patterns/ab-002201)

## Why it matters

Without server-side filtering and sorting, clients must fetch all records and filter client-side — amplifying data transfer, memory use, and backend query cost at every call. Worse, if a sort field is passed unsanitized into an `ORDER BY` clause, it becomes a CWE-89 SQL injection vector (OWASP A03:2021): an attacker can supply a crafted field name to probe column structure or trigger errors that reveal schema. Even the low-severity label here understates the injection risk on the sort parameter.

## Severity rationale

Low for the missing filter/sort feature itself, but the sort-field injection path elevates actual exploitability — an unsanitized ORDER BY is a direct injection vector.

## Remediation

Add filter and sort query parameter support to primary list endpoints in `app/api/`. Always validate sort field against an explicit allowlist — never interpolate a query parameter directly into an ORDER BY clause:

```ts
const ALLOWED_SORT_FIELDS = ['created_at', 'updated_at', 'title'] as const
type SortField = typeof ALLOWED_SORT_FIELDS[number]

const sortParam = searchParams.get('sort') ?? 'created_at'
const sortField: SortField = ALLOWED_SORT_FIELDS.includes(sortParam as SortField)
  ? (sortParam as SortField)
  : 'created_at'

const items = await db.post.findMany({
  where: { status: searchParams.get('status') ?? undefined },
  orderBy: { [sortField]: searchParams.get('order') === 'asc' ? 'asc' : 'desc' },
})
```

## Detection

- **ID:** `filtering-sorting`
- **Severity:** `low`
- **What to look for:** Enumerate all list endpoints. For those that return more than trivially small datasets, check whether query parameters for filtering and sorting are supported. Look for: query parameter parsing (`req.query`, `searchParams.get()`), `where` clauses in database queries driven by query params, `orderBy` clauses driven by query params. Also check: is user-supplied sort field sanitized against an allowlist (to prevent unintended field exposure or injection)?
- **Pass criteria:** At least the primary list endpoints (the main resources your app works with) support basic filtering (at minimum: by status or type) and sorting (at minimum: by created date). Sort fields are validated against an allowlist.
- **Fail criteria:** No filtering or sorting support on any list endpoint, requiring clients to fetch all data and filter client-side — or sort field is passed unsanitized directly into a query.
- **Skip (N/A) when:** All list endpoints return static or near-static data where filtering/sorting is not meaningful (e.g., a fixed list of categories). Signal: all list endpoints return fewer than 50 records by definition, or data is static/config-driven.
- **Detail on fail:** Example: `Primary list endpoints /api/posts and /api/users lack filter/sort support`. Note which major list endpoints lack filter/sort support and whether sort field injection risk exists. Max 500 chars.
- **Remediation:** Add query parameter support in `app/api/` route handlers for common filters on your primary list endpoints. At minimum, support `?sort=created_at&order=desc` for creation-date sorting and `?status=active` type filters where status is a field on the resource. Always validate sort field values against an explicit allowlist — never pass a sort parameter directly into `ORDER BY` or equivalent without validation, as this can expose column names or enable injection.

## External references

- cwe CWE-89
- owasp A03

Taxons: injection-and-input-trust, cost-efficiency

HTML version: https://auditbuffet.com/patterns/ab-002201