# Database column references exist in schema

- **Pattern:** `ab-000035` (`ai-slop-hallucinations.data-references.db-columns-in-schema`)
- **Severity:** high
- **Lifecycle:** active
- **Last modified:** 2026-04-18
- **Canonical URL:** https://auditbuffet.com/patterns/ab-000035
- **License:** CC-BY-4.0 — attribute to AuditBuffet Pattern Catalog (https://auditbuffet.com/patterns/ab-000035)

## Why it matters

A hallucinated column name is subtler than a hallucinated table name and more dangerous in Supabase projects: Prisma's TypeScript client will flag unknown field names at compile time if types are regenerated, but Supabase's PostgREST API silently ignores an unrecognized column in `.select()` — returning a result set with the column omitted and no error. An unrecognized column in `.eq('avatarUrl', ...)` makes the filter a no-op, so the query returns all rows instead of the intended subset. This is an OWASP A08 data-integrity failure that can expose records to the wrong user if the broken filter was access-gating data.

## Severity rationale

High because a silent no-op filter in Supabase can expose data to unauthorized callers — the query succeeds but returns the wrong rows.

## Remediation

Match every column reference exactly to the column name in the schema. SQL column names are case-sensitive in comparisons and typically snake_case; AI-generated code frequently uses camelCase by habit.

```ts
// Prisma
await prisma.user.findMany({ where: { emailAddress: 'a@b.com' } })  // Bad
await prisma.user.findMany({ where: { email: 'a@b.com' } })          // Good

// Supabase — wrong casing makes the filter a silent no-op
await supabase.from('profiles').select('avatarUrl')   // Bad
await supabase.from('profiles').select('avatar_url')  // Good

await supabase.from('profiles').eq('displayName', 'Alice')   // Bad: filter ignored
await supabase.from('profiles').eq('display_name', 'Alice')  // Good
```

For Supabase projects, generate TypeScript types with `supabase gen types typescript` and pass them to the client constructor — column name typos then fail at compile time rather than silently at runtime.

## Detection

- **ID:** `db-columns-in-schema`
- **Severity:** `high`
- **What to look for:** Build a map of `TABLE → set of column/field names` from the schema (same source as the previous check). Then walk source files for column references:

  **Path A — ORM projects:** For Prisma: `select: { X: true }`, `where: { X: ... }`, `data: { X: ... }`, `orderBy: { X: ... }`, `include: { X: ... }`. For Drizzle: `.select({ X: table.column })`, `eq(table.column, value)`, `.from(table).where(...)`. For Kysely: `.selectFrom('X').select('Y')`. SKIP relation names that resolve to other models (Prisma `include: { posts: true }` where `posts` is a relation).

  **Path B — Supabase projects:** Extract column references from these patterns: `.select('col1, col2')` — split on comma, trim whitespace, SKIP `*` (select all), SKIP relation syntax where the name is followed by `(...)` (e.g., `author(*)` or `posts(id, title)` — these are PostgREST relation fetches, not column names). `.eq('col', val)`, `.neq('col', val)`, `.gt('col', val)`, `.gte('col', val)`, `.lt('col', val)`, `.lte('col', val)`, `.in('col', [...])`, `.contains('col', val)`, `.order('col')` — the first string argument is the column name. `.insert({col: val})`, `.update({col: val})`, `.upsert({col: val})` — object keys are column names. SKIP dynamically constructed query objects where column names come from spread operators (`...userData`) or computed keys.

  **For both paths:** SKIP JSON/JSONB column path expressions — any column reference containing `->` or `->>` (e.g., `.eq('metadata->plan', 'pro')` or Prisma `metadata: { path: ['plan'], equals: 'pro' }`). Only validate the base column name before the first `->`. SKIP Supabase reserved schemas. Count all column references inspected, total resolved, total unresolved.
- **Pass criteria:** 100% of column references resolve to a defined column in the corresponding table. Report: "X column references inspected, Y resolved, 0 unresolved."
- **Fail criteria:** At least 1 column reference does not exist in the table definition.
- **Skip (N/A) when:** No ORM detected AND no `@supabase/supabase-js` in dependencies, OR no schema/migration files present, OR the previous check (`db-models-in-schema`) reported 0 table references.
- **Detail on fail:** `"4 unresolved column references: prisma.user.findMany({ where: { emailAddress: ... } }) — User model has 'email', not 'emailAddress'. supabase.from('profiles').select('display_name, avatarUrl') — 'profiles' table has 'avatar_url', not 'avatarUrl'."`
- **Remediation:** Column reference hallucinations are caught by Prisma's TypeScript types at build time IF you use the generated client. Supabase has NO compile-time column checking — typos silently return empty results or ignored filters. Fix each reference to match the schema:

  ```ts
  // Prisma: column doesn't match schema
  await prisma.user.findMany({ where: { emailAddress: 'a@b.com' } })  // Bad
  await prisma.user.findMany({ where: { email: 'a@b.com' } })          // Good

  // Supabase: column doesn't match migration
  await supabase.from('profiles').select('avatarUrl')   // Bad (camelCase)
  await supabase.from('profiles').select('avatar_url')   // Good (snake_case from SQL)
  ```

  For Supabase projects, consider generating TypeScript types with `supabase gen types typescript` to get compile-time column checking.

## External references

- owasp A08

Taxons: reference-integrity

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