Database list queries have LIMIT and pagination

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.

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.

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.

• 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:

  // 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.