Queries select specific columns instead of SELECT *

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.

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.

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

• ID: database-design-operations.query-patterns.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:

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