# SQL injection prevention via parameterized queries or ORM

- **Pattern:** `ab-002407` (`security-hardening.input-validation.sql-injection`)
- **Severity:** high
- **Lifecycle:** active
- **Last modified:** 2026-04-18
- **Canonical URL:** https://auditbuffet.com/patterns/ab-002407
- **License:** CC-BY-4.0 — attribute to AuditBuffet Pattern Catalog (https://auditbuffet.com/patterns/ab-002407)

## Why it matters

SQL injection (CWE-89) is the canonical database attack and remains OWASP A03 (Injection) for good reason. String-concatenating user input into SQL queries lets attackers read or delete every table in the database, bypass authentication by injecting `OR 1=1`, and in some configurations execute OS commands. NIST 800-53 SI-10 requires input validation as a system security measure. A single unparameterized query in a public-facing endpoint is sufficient to exfiltrate an entire user table. ORM raw query methods like Prisma's `$queryRaw` with template literal interpolation carry exactly the same risk as hand-rolled string concatenation.

## Severity rationale

High because a single injectable query gives an attacker full read and often write access to the database, compromising every user's data in one request.

## Remediation

Use ORM builder methods for all queries. When raw SQL is unavoidable, use Prisma's tagged template — not a plain string:

```ts
import { Prisma } from '@prisma/client'

// Safe: ORM builder
const user = await prisma.user.findUnique({ where: { email } })

// Safe: Prisma.sql tagged template (parameterized)
const rows = await prisma.$queryRaw(
  Prisma.sql`SELECT * FROM users WHERE email = ${email}`
)

// UNSAFE — never do this:
// prisma.$queryRaw(`SELECT * FROM users WHERE email = '${email}'`)
```

For `node-postgres`, always pass values as the second array argument: `db.query('SELECT * FROM users WHERE email = $1', [email])`.

## Detection

- **ID:** `sql-injection`
- **Severity:** `high`
- **What to look for:** Enumerate every database query across all API routes and server-side code. For each query, classify it as: (a) parameterized/ORM, (b) raw string concatenation, or (c) template literal with user input. examine all database query code. Search for string concatenation or template literals that embed user-controlled values in SQL strings. Check raw query calls in `pg`, `mysql2`, `better-sqlite3` usage. ORM methods (Prisma, Drizzle, Sequelize, TypeORM) are generally safe, but look for `.queryRaw`, `$queryRaw`, `sequelize.query`, `TypeORM.query` calls that might interpolate untrusted data.
- **Pass criteria:** All database queries use parameterized placeholders, ORM methods with type-safe inputs, or prepared statements. No user-controlled values are concatenated into query strings — 100% of queries must use parameterization or ORM. Report: "X database queries found, all Y use parameterized queries or ORM."
- **Fail criteria:** Any database query concatenates user input directly into a SQL string, e.g., `` `SELECT * FROM users WHERE id = ${req.params.id}` `` without using query parameters.
- **Do NOT pass when:** ORM raw query methods (`.queryRaw`, `sequelize.query`) use string interpolation with user input, even if the rest of the codebase uses safe ORM methods.
- **Skip (N/A) when:** The application does not use a relational database.
- **Cross-reference:** The `server-side-validation` check ensures inputs are validated before reaching database queries.
- **Detail on fail:** Show the vulnerable pattern location. Example: `"Raw SQL in app/api/users/route.ts line 34 concatenates req.params.id directly into query string"` or `"Prisma $queryRaw call in lib/db.ts uses template literal interpolation instead of Prisma.sql tag"`
- **Remediation:** Always use parameterized queries or ORM builder methods:

  ```ts
  // Bad: String concatenation
  const users = await db.query(`SELECT * FROM users WHERE email = '${email}'`)

  // Good: Parameterized query (pg, node-postgres)
  const users = await db.query('SELECT * FROM users WHERE email = $1', [email])

  // Good: Prisma ORM
  const user = await prisma.user.findUnique({ where: { email } })

  // Good: Prisma raw query (if raw SQL is needed)
  import { Prisma } from '@prisma/client'
  const users = await prisma.$queryRaw(
    Prisma.sql`SELECT * FROM users WHERE email = ${email}`
  )
  // NOT: prisma.$queryRaw(`SELECT * FROM users WHERE email = '${email}'`)
  ```

## External references

- cwe CWE-89
- owasp A03
- nist SI-10

Taxons: injection-and-input-trust

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