Fix SQL injection in Node.js and Express

1. Find every query whose text is built from request data (req.query, req.params, req.body) with template literals or +, including in the data-access modules your routes call.
2. Make the SQL text a constant with ? (or $name, :name, @name) placeholders and pass the values to statement.all(), get() or run().
3. For the sort column and direction, map each public option to a fixed SQL fragment with a Map and answer 400 for anything else; never put the raw value in the SQL.
4. Check that each query-string value is a single string before you bind it; Express gives you an array when a key repeats.
5. Add regression tests with this card's payloads (tautology, UNION, comment, sort expression) next to functional tests for names such as O'Brien and non-ASCII text.
6. Log database errors on the server and return a generic 500; error text helps an attacker tune a payload.

The three implementations share one API and one test suite; only the code below changes. The tests load the one that IMPL names.

export function ordersForCustomer(db, customerName, status) {
  const sql = `
    SELECT o.id, o.status, o.total_cents, o.created_at
    FROM orders AS o
    JOIN customers AS c ON c.id = o.customer_id
    WHERE c.name = '${customerName}' AND o.status = '${status}'
    ORDER BY o.id`;
  return db.prepare(sql).all();
}

export function ordersForCustomer(db, customerName, status) {
  const sql = `
    SELECT o.id, o.status, o.total_cents, o.created_at
    FROM orders AS o
    JOIN customers AS c ON c.id = o.customer_id
    WHERE c.name = ? AND o.status = ?
    ORDER BY o.id`;
  return db.prepare(sql).all(customerName, status);
}

// A placeholder can only stand in for a value. Column names and ASC/DESC are
// part of the SQL text, so map the caller's choice onto fixed fragments.
const SORT_COLUMNS = new Map([
  ['created_at', 'o.created_at'],
  ['total_cents', 'o.total_cents'],
  ['customer', 'c.name'],
]);
const SORT_DIRECTIONS = new Map([
  ['asc', 'ASC'],
  ['desc', 'DESC'],
]);

export function searchOrders(db, status, sort, direction) {
  const column = SORT_COLUMNS.get(sort);
  const order = SORT_DIRECTIONS.get(direction);
  if (column === undefined || order === undefined) {
    throw new RangeError('Unsupported sort column or direction');
  }
  const sql = `
    SELECT o.id, c.name AS customer, o.status, o.total_cents, o.created_at
    FROM orders AS o
    JOIN customers AS c ON c.id = o.customer_id
    WHERE o.status = ?
    ORDER BY ${column} ${order}, o.id`;
  return db.prepare(sql).all(status);
}

function escapeSqlString(value) {
  return value.replaceAll('\\', '\\\\').replaceAll("'", "\\'");
}

export function ordersForCustomer(db, customerName, status) {
  const sql = `
    SELECT o.id, o.status, o.total_cents, o.created_at
    FROM orders AS o
    JOIN customers AS c ON c.id = o.customer_id
    WHERE c.name = '${escapeSqlString(customerName)}' AND o.status = '${escapeSqlString(status)}'
    ORDER BY o.id`;
  return db.prepare(sql).all();
}

The exploit tests send the attack and check that it fails; functional tests next to them keep ordinary requests working, so a fix that rejects everything fails too.

describe(`exploit (IMPL=${implName})`, () => {
  test("a tautology in the name does not return other customers' orders", async () => {
    assert.deepEqual(await orderIds('/orders', { customer: "' OR '1'='1", status: 'shipped' }), []);
  });

  test("a UNION payload cannot read another customer's orders", async () => {
    const customer = "' UNION SELECT id, status, total_cents, created_at FROM orders WHERE customer_id = 2 --";
    assert.deepEqual(await orderIds('/orders', { customer, status: 'shipped' }), []);
  });

  test('a comment cannot cut off the status filter', async () => {
    assert.deepEqual(await orderIds('/orders', { customer: "Alice Martin'--", status: 'shipped' }), []);
  });

  test('a sort expression is rejected instead of becoming a data oracle', async () => {
    // Row order would reveal whether each customer's hidden email starts with "b".
    const sort = 'CASE WHEN substr(c.email, 1, 1) = char(98) THEN 0 ELSE 1 END';
    const res = await get('/admin/orders', { status: 'shipped', sort, dir: 'asc' });
    assert.equal(res.status, 400);
  });

  test('the direction cannot smuggle in a second sort expression', async () => {
    const dir = 'ASC, CASE WHEN substr(c.email, 1, 1) = char(98) THEN 0 ELSE 1 END';
    const res = await get('/admin/orders', { status: 'shipped', sort: 'created_at', dir });
    assert.equal(res.status, 400);
  });
});

• database.exec() takes no parameters. Keep it for constant SQL such as schema setup and use prepare() for anything that carries input.
• node:sqlite reads an object or array passed as the first argument as named parameters. Express 5's default 'simple' query parser returns an array for ?customer=a&customer=b, so check typeof value === 'string' first.
• A template literal is concatenation, not a parameter. Tagged-template APIs such as Prisma's $queryRaw do bind values; Prisma's $queryRawUnsafe does not.
• Knex raw() and whereRaw() bind values with ? and identifiers with ??, but building the SQL string yourself first throws that away. ?? quotes any column the caller names, so keep the allowlist.
• LIKE: a bound % still matches every row. Escape \, % and _ in the value and add ESCAPE '\' to the SQL.
• IN lists: node:sqlite cannot bind an array. Generate one ? per value (SQLite allows 32,766 host parameters by default from version 3.32.0) or bind a JSON array and use IN (SELECT value FROM json_each(?)).
• ORDER BY ? binds a constant, so SQLite silently skips the sort (observed with node:sqlite on Node 26.11.1). Identifiers need the allowlist.
• Allowlist only columns the caller may see: sorting by a hidden column such as email still leaks its order.
• Node 26.11.0 renamed DatabaseSync to Database and StatementSync to Statement; the old names remain as documentation-only deprecated aliases (DEP0210, DEP0211).

1. Run the tests in all three modes: IMPL=fixed must pass; IMPL=vulnerable and IMPL=wrong must fail (node ../../run-all.mjs node-express does this for you).
2. Against your own endpoint, send customer=' OR '1'='1 and expect an empty result, and send a CASE expression as the sort and expect 400.
3. Static analysis is weak for node:sqlite today: CodeQL does not model it and the Semgrep rule above found nothing in this card's layout, so the regression tests are the control.

What each rule reported when it was run on this card's code, or what its source shows it covers where it was not run. A rule that exists for the weakness does not always catch it.

Download the project, install its dependencies once (this step needs the network), then run the tests against each implementation from the sql-injection/node-expressfolder.

npm ci

# Expected: every test passes
IMPL=fixed npm test --silent

# Expected: an exploit test fails
IMPL=vulnerable npm test --silent

# Expected: a test fails
IMPL=wrong npm test --silent

Each run writes a JUnit report to reports/junit.xml. From the top folder of the download, node run-all.mjs sql-injection/node-express runs all three modes and checks the expected results.

Download the project ZIP, 25 KB

Verified

Machine

macOS 27.0 (Darwin 27.0.0, arm64)

Toolchain

• node 26.11.1
• express 5.3.0
• node:sqlite built in (SQLite 3.53.4)

Matrix

fixed: 11 of 11 tests pass; vulnerable: 7 of 11 tests fail (5 exploit tests); wrong: 6 of 11 tests fail (4 exploit tests)

Test results

fixed 11/11 pass; vulnerable 7/11 fail (5 exploit tests); wrong 6/11 fail (4 exploit tests), offline after install

Versions and results hold for the toolchain above. The card's tests are the check: run them with your own versions before relying on the fix.

References

1. OWASP SQL Injection Prevention Cheat Sheet
2. OWASP Query Parameterization Cheat Sheet
3. Node.js v26.11.1: SQLite, statement.all() parameter binding
4. Node.js v26.11.1: SQLite, type conversion between JavaScript and SQLite
5. Node.js deprecations: DEP0210 sqlite.DatabaseSync
6. Express: Migrating to Express 5 (req.query and the default query parser)
7. SQLite: SQL language expressions (string literals, parameters, LIKE ... ESCAPE)
8. SQLite: maximum number of host parameters
9. SQLite: json_each() table-valued function
10. Prisma ORM: raw queries
11. Knex.js: raw queries and bindings

Attack patterns:

• CAPEC-66 at MITRE