SQL injection¶

Mitigation options

Parameters bind values. Table names, column names and sort direction can never be parameterised, so those need an allowlist.

Vulnerable — value interpolated

const { rows } = await db.query(
  `SELECT * FROM products WHERE category = '${req.query.category}'`,
)

Vulnerable — parameterised value, interpolated identifier

const { rows } = await db.query(
  `SELECT * FROM products WHERE category = $1 ORDER BY ${req.query.sort}`,
  [req.query.category],
)

This is the common near-miss: it looks safe because it uses $1.

Mitigated — parameterised query

const { rows } = await db.query(
  'SELECT * FROM products WHERE category = $1',
  [req.query.category],
)

Docs: node-postgres parameterised queries

Mitigated — allowlist the identifier

const SORTABLE = { name: 'name', price: 'price', newest: 'created_at' }
const DIRECTION = { asc: 'ASC', desc: 'DESC' }

const column = SORTABLE[req.query.sort] ?? 'name'
const direction = DIRECTION[String(req.query.dir).toLowerCase()] ?? 'ASC'

const { rows } = await db.query(
  `SELECT * FROM products WHERE category = $1 ORDER BY ${column} ${direction}`,
  [req.query.category],
)

The interpolated text comes from the map's values, never from the request, so user input selects a key rather than supplying SQL.

Mitigated — an IN list without string building

const ids = z.array(z.string().uuid()).max(100).parse(req.body.ids)
const { rows } = await db.query(
  'SELECT * FROM products WHERE id = ANY($1::uuid[])',   // one parameter, whole array
  [ids],
)

Bypasses to check

• An IN (...) list built by joining values, or LIMIT/OFFSET pasted in rather than bound.
• LIKE patterns built from input: % and _ change the result set, and a leading % can be a denial of service on a large table.
• Second order: input stored safely, then read back and concatenated into a later query — a report, a migration, a background job.
• Hand-rolled escaping (replace(/'/g, "''")) instead of binding.
• Check then change: the value is validated, then a different variable (the raw body, or a re-parsed copy) reaches the query.
• Placeholder numbering and the values array drifting apart when the query is assembled conditionally.
• client.query calls inside helpers, migrations and seed scripts, which often skip the conventions the route handlers follow.

What to grep for

• db.query(`, client.query(, pool.query(
• ${ inside a SQL string, + req., .join(',') near a query
• ORDER BY, LIMIT, OFFSET, TABLE next to a variable

Mitigation options

Vulnerable — interpolation inside a raw fragment

await knex('users').whereRaw(`email = '${req.body.email}'`)

Vulnerable — sort column straight from the request

await knex('products')
  .where({ category: req.query.category })      // this part is bound
  .orderByRaw(req.query.sort)                   // this part is not

The bound where makes the query look safe; orderByRaw takes raw SQL.

Mitigated — builder methods

await knex('users').where({ email: req.body.email })

await knex('products')
  .where({ category: req.query.category })
  .whereIn('id', ids)                            // array bound for you

Mitigated — raw with bindings

await knex.raw('select * from users where email = ?', [req.body.email])

// named bindings, and :name: for an identifier Knex will quote
await knex.raw('select * from :table: where email = :email', {
  table: 'users',
  email: req.body.email,
})

? and :name bind values; :name: is the identifier form, and Knex quotes it rather than pasting it in.

Docs: Knex raw bindings

Mitigated — allowlist the sort column

const SORTABLE = { name: 'name', price: 'price', newest: 'created_at' }

await knex('products')
  .where({ category: req.query.category })
  .orderBy(SORTABLE[req.query.sort] ?? 'name', req.query.dir === 'desc' ? 'desc' : 'asc')

orderBy takes an identifier, not SQL, so a value from the allowlist is safe where orderByRaw would not be.

Bypasses to check

• Any *Raw method with a template literal: whereRaw, orderByRaw, havingRaw, joinRaw, selectRaw, knex.raw.
• A bound where next to an unbound orderByRaw in the same chain — the safe half hides the unsafe one.
• knex.raw used inside a builder call, which reintroduces raw SQL into an otherwise safe chain.
• Objects passed to where built from req.query wholesale, letting the caller choose which columns are filtered.
• Check then change: the value is validated, then the raw request value is interpolated into the fragment.
• Migrations and seeds, which commonly use knex.raw with string building.
• orderBy given a user-supplied string that contains a direction ("price desc"), depending on version behaviour — pass column and direction as separate arguments.

What to grep for

• whereRaw, orderByRaw, havingRaw, joinRaw, knex.raw
• `${ inside any of the above
• .where(req.query, .where(req.body

Mitigation options

Vulnerable — the unsafe raw variant

const users = await prisma.$queryRawUnsafe(
  `SELECT * FROM users WHERE id = ${req.params.id}`,
)

$queryRawUnsafe and $executeRawUnsafe take a plain string: nothing is bound.

Vulnerable — a fragment built by hand, then passed to a tagged template

const filter = `category = '${req.query.category}'`
const rows = await prisma.$queryRaw`SELECT * FROM products WHERE ${Prisma.raw(filter)}`

The tagged template is safe, but Prisma.raw puts the string back in verbatim.

Mitigated — the client API

const products = await prisma.product.findMany({
  where: { category: req.query.category, ownerId: req.user.id },
  orderBy: { createdAt: 'desc' },
  take: 50,
})

Docs: Prisma CRUD

Mitigated — $queryRaw tagged template

const rows = await prisma.$queryRaw`
  SELECT * FROM products WHERE category = ${req.query.category}
`

Written as a tagged template, each ${} becomes a bound parameter. The same text passed to $queryRawUnsafe would not be.

Mitigated — assembled raw SQL

const ids = z.array(z.string().uuid()).max(100).parse(req.body.ids)

const rows = await prisma.$queryRaw(
  Prisma.sql`SELECT * FROM products WHERE id IN (${Prisma.join(ids)})`,
)

Prisma.join binds each element, so an IN list needs no string building.

Docs: Prisma raw queries

Mitigated — allowlist a dynamic sort

const SORTABLE = new Set(['name', 'price', 'createdAt'])
const field = SORTABLE.has(req.query.sort) ? req.query.sort : 'name'

const products = await prisma.product.findMany({
  orderBy: { [field]: req.query.dir === 'desc' ? 'desc' : 'asc' },
})

Bypasses to check

• $queryRawUnsafe / $executeRawUnsafe anywhere — treat every call as a finding until the argument is proven constant.
• Prisma.raw() inside an otherwise safe $queryRaw, which is the documented escape hatch and the usual near-miss.
• $queryRaw called as a function with a built string rather than as a tagged template: prisma.$queryRaw(...${x}...) binds nothing.
• A where object spread from req.query or req.body, letting the caller filter on columns you did not intend, or inject relation filters.
• orderBy keys taken from input without an allowlist.
• Check then change: the value is validated, then the raw request value is interpolated into the SQL.
• Prisma protects against SQL injection, not against missing authorisation — a safe query with no ownerId/tenantId filter is still a finding.

What to grep for

• $queryRawUnsafe, $executeRawUnsafe, Prisma.raw(
• $queryRaw( with a parenthesis rather than a backtick
• where: { ...req., orderBy: { with a variable key