SQL injection prevention via parameterized queries or ORM

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.

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

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]).

• ID: security-hardening.input-validation.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:

  // 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}'`)