FlawOpen

flawopen.com/sql-injection/Javascript

● CWE-89 · Critical
FlawOpen Security Research

SQL Injection in JavaScript (mysql2, pg, Prisma, Knex)

A template literal or + inside pool.query() lets a request parameter rewrite your SQL and return every row (CWE-89). The safe call for mysql2, node-postgres, Prisma and Knex, why ${} is safe in one Prisma method and dangerous in the next, and how to sort without orderByRaw.[1], [2], [3], [4], [5], [6]

💡 Plain English Explainer (ELI5)

Picture a parcel hub: the route sheet is printed and locked before any box arrives. A name on a label goes into the named slot; it cannot add a new stop to the route. SQL parameters work the same way: mysql2 execute(sql, [userId]) and pg query(sql, [status]) bind values, while Prisma's tagged $queryRaw binds each ${} hole. Build SQL first with a template literal or +, or use $queryRawUnsafe, and the customer's text is written into the route sheet itself. Knex orderByRaw has the same problem for sort instructions; map a requested key to a fixed column and call orderBy.[1], [2], [3], [4], [5], [6]

Core Concepts & Subsystem Terms

Template literals in query()
A backtick string with ${userId} is evaluated by JavaScript before the driver is called, so the driver receives finished SQL with the value already inside. The syntax looks like a placeholder but binds nothing.[7], [1], [6]
mysql2 execute() vs query()
execute(sql, values) uses a server-side prepared statement. query(sql, values) escapes values on the client and substitutes them into ? markers. Both are safe when values are in the array; neither helps when the SQL string was built with + or a template literal. mysql2 disables multipleStatements by default.[3], [6], [9]
pg query(text, values)
node-postgres sends values separately from text, so $1 is filled in by PostgreSQL. With no values, pg uses the simple query protocol, which also accepts several semicolon-separated statements in one call.[2], [6]
Prisma $queryRaw vs $queryRawUnsafe
$queryRaw is a tagged template: Prisma turns each ${} into a bound parameter. $queryRawUnsafe(string) takes a finished string, so the same ${} inside a normal template literal is pasted into the SQL. The names differ by one word and the risk is opposite.[4], [6]
Knex whereRaw / orderBy
whereRaw('email = ?', [email]) binds values, but whereRaw and orderByRaw with interpolated strings are raw SQL. orderBy(column) quotes the column as an identifier; feed it only names from an allowlist.[5], [6]

Framework mappings

Step-by-Step Attack Flow

Step 1

Request value reaches the data layer

An Express route reads req.query.id and passes it to getUser(). Query parameters are strings or arrays, never numbers, and nothing validates them.[8], [6]

Step 2

A template literal builds the SQL

` ... WHERE id = ${userId} is evaluated first. With the benign test value 0 OR 1=1, the string becomes WHERE id = 0 OR 1=1` before mysql2 sees it.[7], [1], [6]

Step 3

The driver receives no values

pool.query(sql) has an empty values array, so there is nothing to escape or bind. MySQL parses OR 1=1 as part of the WHERE clause.[3], [2], [9], [6]

Step 4

The route returns the table

The condition is true for every row. The route that should return one user returns all of them, and the same happens through $queryRawUnsafe or an orderByRaw fed from the query string.[1], [6]

Source Code: Flaw vs. Secure Implementation

UNPATCHED FLAW
// users.js: each query below builds SQL text from request data

// mysql2: the template literal is evaluated before the driver is called
export async function getUser(pool, userId) {
  const [rows] = await pool.query(`SELECT id, email FROM users WHERE id = ${userId}`);
  return rows[0];
}

// pg: concatenation, and no values array to bind
export async function findOrders(pg, status) {
  const { rows } = await pg.query("SELECT id FROM orders WHERE status = '" + status + "'");
  return rows;
}

// Prisma: the Unsafe variant takes a finished string
export function findByEmail(prisma, email) {
  return prisma.$queryRawUnsafe(`SELECT id FROM "User" WHERE email = '${email}'`);
}

// Knex: orderByRaw inserts the request value as raw SQL
export function listUsers(knex, sortColumn) {
  return knex('users').select('id', 'email').orderByRaw(sortColumn);
}
HARDENED SECURE PATCH
// users.js: values go in the values array, columns come from an allowlist
const SORTABLE = new Map([['id', 'id'], ['email', 'email'], ['created', 'created_at']]);

// mysql2: server-side prepared statement, value bound to ?
export async function getUser(pool, userId) {
  const [rows] = await pool.execute('SELECT id, email FROM users WHERE id = ?', [userId]);
  return rows[0];
}

// pg: $1 filled in by PostgreSQL from the values array
export async function findOrders(pg, status) {
  const { rows } = await pg.query('SELECT id FROM orders WHERE status = $1', [status]);
  return rows;
}

// Prisma: tagged template, each ${} becomes a bound parameter
export function findByEmail(prisma, email) {
  return prisma.$queryRaw`SELECT id FROM "User" WHERE email = ${email}`;
}

// Knex: orderBy quotes the allowlisted column as an identifier
export function listUsers(knex, sortKey) {
  const column = SORTABLE.get(sortKey) ?? 'id';
  return knex('users').select('id', 'email').orderBy(column);
}

Engineering & System Hardening Checklist

Sources