# Queries select specific columns instead of SELECT *

- **Pattern:** `ab-000907` (`database-design-operations.query-patterns.no-select-star`)
- **Severity:** low
- **Lifecycle:** active
- **Last modified:** 2026-04-18
- **Canonical URL:** https://auditbuffet.com/patterns/ab-000907
- **License:** CC-BY-4.0 — attribute to AuditBuffet Pattern Catalog (https://auditbuffet.com/patterns/ab-000907)

## Why it matters

Returning all columns from a user table — including `password_hash`, `stripe_customer_id`, and internal flags — in a general API response is an OWASP A02 data exposure pattern (CWE-200). Even when the fields are not sent to the client by the application, they consume network bandwidth and memory, and any serialization bug (an accidentally returned field, a `JSON.stringify` on the whole object) exposes sensitive internal data. For list views, fetching large `content` columns for 50 posts when only the title and summary are displayed is a performance problem at scale.

## Severity rationale

Low because the exposure requires an additional serialization mistake to reach clients, but the data is in memory and on the wire in a form that amplifies the impact of any adjacent bug.

## Remediation

Specify explicit column lists in every query. In Prisma, use `select` to list only the fields needed for the use case. Define reusable `select` constants for commonly fetched shapes.

```ts
// Reusable select shape — excludes sensitive and large columns
const userPublicSelect = {
  id: true, email: true, name: true, avatarUrl: true, createdAt: true
  // password_hash, stripeCustomerId: excluded
} as const

const user = await prisma.user.findUnique({
  where: { id: userId },
  select: userPublicSelect
})

// List view — exclude large content column
const posts = await prisma.post.findMany({
  select: {
    id: true, title: true, slug: true, createdAt: true,
    author: { select: { id: true, name: true } }
    // content: omitted — only fetch in detail view
  }
})
```

For consistently excluded fields across the application, configure Prisma model-level `omit` defaults in `schema.prisma`.

## Detection

- **ID:** `no-select-star`
- **Severity:** `low`
- **What to look for:** Count every `SELECT *` occurrence in raw SQL query strings across the codebase. For Prisma, check `findMany()` and `findUnique()` calls without a `select` option — by default Prisma returns all columns, which may include sensitive or large fields. Specifically look for cases where: (1) API responses return fields that should not be exposed (password hashes, internal flags, deleted_at markers, admin-only metadata). (2) Queries pull large blob/text columns (e.g., post content, file data) for list views that only need a summary. (3) Auth-related models (`User`, `Account`) are fetched without excluding sensitive columns.
- **Pass criteria:** At least 80% of queries select specific columns relevant to the use case. Sensitive columns (password hashes, internal metadata, soft-delete timestamps) are excluded from general queries. Large content columns are only fetched when rendering full detail views, not list views.
- **Fail criteria:** `SELECT *` used pervasively for queries that don't need all columns. User model returned wholesale (including password hash) in non-auth contexts. Large blob columns fetched in list queries that only display titles and summaries.
- **Skip (N/A) when:** Application uses an ORM that handles column selection with a safe default schema, all tables are small (< 5 columns), and no sensitive columns exist in any table.
- **Detail on fail:** Specify where the issue is found. Example: `"In src/app/api/users/route.ts: prisma.user.findMany() with no select option — returns password_hash, deleted_at, and internal stripe_customer_id to the client."` or `"SELECT * FROM posts used in listing endpoint that only needs id, title, and created_at — also fetches large content column."`.
- **Remediation:** Specify columns explicitly:

  ```ts
  // BEFORE — returns all columns including password_hash
  const user = await prisma.user.findUnique({ where: { id: userId } })

  // AFTER — select only what's needed
  const user = await prisma.user.findUnique({
    where: { id: userId },
    select: {
      id: true,
      email: true,
      name: true,
      avatarUrl: true,
      createdAt: true,
      // password_hash: excluded — never expose this
      // stripeCustomerId: excluded — internal only
    }
  })

  // For list views — exclude large text columns
  const posts = await prisma.post.findMany({
    select: {
      id: true,
      title: true,
      slug: true,
      createdAt: true,
      author: { select: { id: true, name: true } }
      // content: excluded from list — only fetch in detail view
    }
  })
  ```

  Consider creating a Prisma model with `omit` configuration for consistently excluded fields, or define reusable `select` objects as constants to avoid repetition.

---

## External references

- cwe CWE-200
- owasp A02

Taxons: access-control, performance

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