# All user-facing queries use parameterized inputs (no string interpolation)

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

## Why it matters

SQL injection via string interpolation is OWASP A03 and CWE-89, and it remains the most exploited database vulnerability in production applications. A single unparameterized query — `` `SELECT * FROM users WHERE email = '${email}'` `` — lets an attacker bypass authentication, extract all rows, or drop tables by submitting crafted input. AI-generated code frequently uses template literals for readability without recognizing the injection risk. Even one unparameterized query with user input is a critical vulnerability regardless of how many other queries are safe, because an attacker will find and exploit that one path.

## Severity rationale

Critical because a single unparameterized query allows full database compromise through SQL injection, with no remediation possible short of fixing the code and rotating credentials.

## Remediation

Replace all string interpolation in SQL with parameterized placeholders. For `pg`, use `$1`/`$2` positional parameters. For Prisma raw queries, use the `Prisma.sql` tagged template — never `$queryRawUnsafe` with user input.

```ts
// VULNERABLE — never do this
// const q = `SELECT * FROM users WHERE email = '${email}'`

// SAFE — pg parameterized query
const { rows } = await pool.query(
  'SELECT id, email, name FROM users WHERE email = $1',
  [email]
)

// SAFE — Prisma raw with tagged template
const users = await prisma.$queryRaw(
  Prisma.sql`SELECT * FROM users WHERE email = ${email}`
)
```

Search for gaps: `grep -rn '\$queryRawUnsafe\|sql\`.*\${' src/` to surface candidates. ORM query builder methods (`.where()`, `.findMany()`) are safe by default — scrutiny is only needed for raw SQL.

## Detection

- **ID:** `parameterized-queries`
- **Severity:** `critical`
- **What to look for:** Count every raw SQL query pattern in the codebase. Look for: (1) Template literals containing SQL with variable interpolation: `` `SELECT * FROM users WHERE id = ${userId}` `` or `` `SELECT * FROM posts WHERE title LIKE '%${searchTerm}%'` ``. (2) String concatenation in SQL: `"SELECT * FROM users WHERE email = '" + email + "'"`. (3) In Prisma, check `$queryRaw` and `$executeRaw` usage — Prisma's tagged template literal `` Prisma.sql`...` `` parameterizes correctly, but plain string interpolation inside `$queryRaw` does not. (4) In Drizzle, check `sql\`...\`` usage for unsanitized interpolation. (5) In Knex, check `.raw()` calls for string building. ORM query builder methods (`.where`, `.findMany`, `.findUnique`) parameterize automatically — only raw SQL escapes need scrutiny.
- **Pass criteria:** All SQL queries use parameterized inputs — fewer than 1 instance of string interpolation in SQL. Raw SQL uses `$1`/`$2` placeholders (Postgres) or `?` placeholders (MySQL/SQLite) with values passed as a separate array. Prisma raw queries use `Prisma.sql` tagged template. No user input is interpolated directly into SQL strings. Before evaluating, extract and quote any raw SQL patterns found in the codebase to verify parameterization.
- **Do NOT pass when:** Only some queries are parameterized — even 1 unparameterized query with user input is a critical SQL injection vulnerability.
- **Fail criteria:** User input (query params, request body fields, URL segments) is interpolated directly into SQL strings. Even one instance constitutes a critical SQL injection vulnerability.
- **Skip (N/A) when:** Application uses no database queries — static site with no server-side data access.
- **Detail on fail:** Specify the location and the vulnerable code pattern. Example: `"In src/app/api/search/route.ts: raw SQL query uses template literal with searchTerm interpolated: SELECT * FROM posts WHERE title LIKE '%\${searchTerm}%' — SQL injection vulnerability."` or `"In lib/db/users.ts: user email concatenated into query string with + operator."`.
- **Remediation:** Always use parameterized queries:

  ```ts
  // VULNERABLE — direct interpolation (SQL injection risk)
  // const query = `SELECT * FROM users WHERE email = '${email}'`  // NEVER DO THIS

  // SAFE — parameterized with pg driver
  const { rows } = await pool.query(
    'SELECT * FROM users WHERE email = $1',
    [email]
  )

  // SAFE — parameterized with multiple params
  const { rows } = await pool.query(
    'SELECT * FROM posts WHERE status = $1 AND user_id = $2 ORDER BY created_at DESC LIMIT $3',
    [status, userId, limit]
  )

  // SAFE — Prisma raw query with tagged template (auto-parameterizes)
  const users = await prisma.$queryRaw(
    Prisma.sql`SELECT * FROM users WHERE email = ${email}`
  )

  // UNSAFE — Prisma raw query with string interpolation (SQL injection risk)
  // const users = await prisma.$queryRawUnsafe(`SELECT * FROM users WHERE email = '${email}'`)
  // Only use $queryRawUnsafe when the SQL itself (not values) must be dynamic — and sanitize carefully
  ```

  Search for vulnerabilities: `grep -r '\$queryRawUnsafe\|sql\`.*\${' src/` to find potential issues.

---

## External references

- cwe CWE-89
- owasp A03

Taxons: injection-and-input-trust

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