# Database list queries have LIMIT and pagination

- **Pattern:** `ab-000237` (`ai-slop-cost-bombs.unbounded-operations.db-list-queries-have-limit`)
- **Severity:** critical
- **Lifecycle:** active
- **Last modified:** 2026-04-18
- **Canonical URL:** https://auditbuffet.com/patterns/ab-000237
- **License:** CC-BY-4.0 — attribute to AuditBuffet Pattern Catalog (https://auditbuffet.com/patterns/ab-000237)

## Why it matters

Unbounded database list queries are a textbook CWE-770 (Allocation of Resources Without Limits) failure: a single unauthenticated request can pull every row in your users or orders table into memory, saturating your database connection pool and spiking both compute and egress costs. On a Hacker News moment, this turns from a slow endpoint into a complete service outage. Any SaaS operating under GDPR or SOC 2 also faces audit risk: returning unbounded customer records exposes more PII than the request requires. At $0.09/GB of Postgres egress, a table with 500K rows of user profiles can cost several dollars per cache miss — and there is no cache for a query with no LIMIT.

## Severity rationale

Critical because a single viral traffic spike can exhaust the database connection pool and incur unbounded egress charges with no circuit breaker.

## Remediation

Add `take` and cursor-based pagination to every list query in non-admin API handlers. The pattern below also validates page size so callers cannot request arbitrarily large slices.

```ts
const url = new URL(req.url)
const cursor = url.searchParams.get('cursor') ?? undefined
const pageSize = Math.min(50, parseInt(url.searchParams.get('pageSize') ?? '20'))

const users = await prisma.user.findMany({
  take: pageSize,
  ...(cursor ? { skip: 1, cursor: { id: cursor } } : {}),
  orderBy: { createdAt: 'desc' },
})
```

Prefer cursor pagination over offset for tables larger than ~10k rows to avoid the O(offset) scan penalty.

## Detection

- **ID:** `db-list-queries-have-limit`
- **Severity:** `critical`
- **What to look for:** Walk all API handler files. Count all database list queries: `prisma.X.findMany(`, `db.select(...).from(`, `Model.find(`, `repository.find(`, `db.query('SELECT ... FROM`. For each, before evaluating, extract and quote the call's options object (or the next 3 lines of code). Verify the call includes a `take`, `limit`, `LIMIT`, `first`, or `top` parameter with a value of 1000 or fewer. EXCLUDE count queries (`count(`, `_count(`). EXCLUDE queries that filter by an indexed unique field like `where: { id: ... }` (those return at most 1 row). EXCLUDE files under `lib/admin/` or routes under `/admin/` (admin queries legitimately fetch larger result sets). Count: total list queries, total with LIMIT ≤ 1000, total unbounded.
- **Pass criteria:** 100% of list queries in non-admin API handlers have a `take`/`limit`/`LIMIT` value of at most 1000.
- **Report even on pass:** Always report the count and the largest LIMIT seen. Example: "12 list queries inspected, all 12 bounded (largest LIMIT: take=100)."
- **Fail criteria:** At least 1 list query in a non-admin handler has no LIMIT clause OR has a LIMIT exceeding 1000.
- **Do NOT pass when:** A query has `take: 10000` or similar — a limit that's effectively unbounded for a viral moment is not a real limit.
- **Skip (N/A) when:** No database client is detected in the project (no Prisma/Drizzle/TypeORM/Sequelize/Mongoose/Kysely/raw SQL imports).
- **Cross-reference:** For deeper database design analysis, the Database Design & Operations audit (`database-design-operations`) covers indexing strategy and query patterns.
- **Detail on fail:** `"3 unbounded list queries: prisma.user.findMany() in src/app/api/users/route.ts (no take), db.select().from(posts) in src/app/api/feed/route.ts (no limit), prisma.order.findMany({ take: 10000 }) in src/app/api/orders/route.ts (effectively unbounded)"`
- **Remediation:** Unbounded list queries are the canonical "first viral moment" cost bomb — your homepage works fine until someone shares it on Hacker News and 10,000 concurrent users each pull the entire users table. Add pagination:

  ```ts
  // Bad: returns every row
  const users = await prisma.user.findMany()

  // Good: paginated
  const url = new URL(req.url)
  const page = Math.max(1, parseInt(url.searchParams.get('page') ?? '1'))
  const pageSize = Math.min(50, parseInt(url.searchParams.get('pageSize') ?? '20'))
  const users = await prisma.user.findMany({
    take: pageSize,
    skip: (page - 1) * pageSize,
    orderBy: { createdAt: 'desc' },
  })
  ```

  Use cursor-based pagination for large tables to avoid the offset performance penalty.

## External references

- cwe CWE-770
- iso-25010 performance-efficiency

Taxons: cost-efficiency, performance

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