Fix SQL injection in TypeScript and Next.js

1. Find every query whose text is built from request data in route handlers (request.url, nextUrl.searchParams, await request.json(), formData()), server actions and the data-access modules they 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. Read query-string values with searchParams.getAll() and accept exactly one string; a repeated key is not an array you should bind.
5. Add regression tests that call the route handler with a Request carrying this card's payloads (tautology, UNION, comment, sort expression), next to functional tests for names such as O'Brien and non-ASCII text.
6. Catch database errors in the handler, log them 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: DatabaseSync, customerName: string, status: string) {
  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() as OrderRow[];
}

export function ordersForCustomer(db: DatabaseSync, customerName: string, status: string) {
  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) as OrderRow[];
}

// 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: DatabaseSync, status: string, sort: string, direction: string) {
  const column = SORT_COLUMNS.get(sort);
  const order = SORT_DIRECTIONS.get(direction);
  if (column === undefined || order === undefined) {
    throw new UnsupportedSortError('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) as OrderRow[];
}

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

export function ordersForCustomer(db: DatabaseSync, customerName: string, status: string) {
  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() as OrderRow[];
}

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 () => {
    expect(await orderIds(orders, '/orders', { customer: "' OR '1'='1", status: 'shipped' })).toEqual([]);
  });

  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 --";
    expect(await orderIds(orders, '/orders', { customer, status: 'shipped' })).toEqual([]);
  });

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

  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(adminOrders, '/admin/orders', { status: 'shipped', sort, dir: 'asc' });
    expect(res.status).toBe(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(adminOrders, '/admin/orders', { status: 'shipped', sort: 'created_at', dir });
    expect(res.status).toBe(400);
  });
});

• A template literal is concatenation, not a parameter. Prisma's $queryRaw tagged template binds values, but $queryRawUnsafe does not, and passing a template literal you built to $queryRaw(Prisma.raw(...)) or Drizzle's sql.raw() puts the input into the SQL text.
• Server actions are public POST endpoints: their arguments come from the client exactly like a route handler's body, so the same binding rules apply to them.
• searchParams.get() returns the first value of a repeated key and silently drops the rest; use getAll() when you need to know the value was given once.
• database.exec() takes no parameters. Keep it for constant SQL such as schema setup and use prepare() for anything that carries input.
• ORDER BY ? binds a constant, so SQLite silently skips the sort. Identifiers need the allowlist, and the allowlist should hold only columns the caller may see: sorting by a hidden column such as email still leaks its order.
• LIKE: a bound % still matches every row. Escape \, % and _ in the value and add ESCAPE '\' to the SQL.
• An uncaught error in a route handler still becomes a 500; catch database errors, log them on the server and never put err.message in the response.
• 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 sql-injection/typescript-nextjs does this for you).
2. Against your own deployment, request /orders?customer=%27%20OR%20%271%27%3D%271&status=shipped and expect an empty list, and send a CASE expression as the sort and expect 400.
3. Static analysis found nothing here: CodeQL models App Router route handlers as sources but not node:sqlite as a sink, and the Semgrep packs model neither, so the regression tests are the control. With Prisma, also grep for $queryRawUnsafe and $executeRawUnsafe.

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/typescript-nextjsfolder.

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/typescript-nextjs runs all three modes and checks the expected results.

Download the project ZIP, 35 KB

Verified

Machine

macOS 27.0 (Darwin 27.0.0, arm64)

Toolchain

• node 26.11.1
• npm 11.20.0
• next 16.4.0
• react 19.3.0
• vitest 5.0.3 (Vite 8.3.4)
• typescript 6.0.3
• node:sqlite built in (SQLite 3.53.4)
• semgrep 1.180.0

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 npm ci

Dependency freshness

next 16.4.0 was published on 2026-10-06, react and react-dom 19.3.0 on 2026-09-09, vitest 5.0.3 on 2026-09-30 and typescript 6.0.3 on 2026-04-16. TypeScript 7.0.2 is newer, but its package no longer exports the compiler API that next build's type check loads, so the card stays on 6.0.3. npm audit found 0 vulnerabilities. The route handlers were also checked with tsc --noEmit and a next build (both pass); the tests call them directly instead. Rerun on Node.js 26.11.1, above the 26.5.1 security floor in engines.

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. Next.js: Route Handlers (App Router)
4. Next.js: route.js file conventions
5. Node.js v26.11.1: SQLite, statement.all() parameter binding
6. MDN: URLSearchParams.getAll()
7. Prisma ORM: raw queries ($queryRaw and $queryRawUnsafe)
8. Drizzle ORM: sql operator and sql.raw()
9. SQLite: SQL language expressions (string literals, parameters, LIKE ... ESCAPE)
10. CodeQL Next.js model (semmle/javascript/frameworks/Next.qll)

Attack patterns:

• CAPEC-66 at MITRE