Prove SQL Injection Safeguards with Attack Tests

Inventory every query construction site, attack each with canonical payloads, and add regression tests that keep parameterization honest.

~35 minsecuritydatabasetesting

Task

Audit a codebase for SQL injection by attacking every query site and encoding the results as permanent regression tests.

When to use this

A new filter feature builds SQL by string concatenation. Code review missed it, and the question is how many other places do the same thing.

Prerequisites

A test environment with a disposable database
Access to all data-access code paths
A payload corpus of canonical SQL injection attacks

Steps

1. Inventory every place SQL strings are built.

rg -n "query\(|execute\(|\$\{|\+.*SELECT|\+.*WHERE" src/db/ --type js | grep -viE "prepared|param" | head -20

Expected output: A list of candidate concatenation sites, each one either parameterized already or worth attacking.

2. Attack the suspicious sites with canonical payloads.

const ATTACKS = [
  "' OR '1'='1",
  "1; DROP TABLE users;--",
  "' UNION SELECT password FROM users--",
  "admin'--",
];
for (const payload of ATTACKS) {
  const rows = await findUsers(payload);
  // assert: rows do not leak other users, no table dropped
}

Expected output: Parameterized sites treat payloads as inert strings; a vulnerable site returns other users rows or executes the trailing statement.

3. Fix findings by parameterizing, never by escaping.

const SAFE = "SELECT id, name FROM users WHERE name = $1";
const rows = await pool.query(SAFE, [userInput]);

Expected output: The injection surface disappears because user input never becomes SQL syntax, only a bound value.

4. Keep the attack corpus as a regression suite.

describe("injection resistance", () => {
  ATTACKS.forEach((payload, i) => {
    it(`survives payload ${i}`, async () => {
      const rows = await findUsers(payload);
      expect(rows.every((r) => r.name === payload)).toBe(true);
    });
  });
});

Expected output: Any future refactor back to concatenation fails CI with the specific payload that broke through.

5. Re-scan the codebase for new construction sites in CI.

node scripts/scan-sql-concat.mjs && echo "no new concatenation sites"

Expected output: The scan fails on new query string building, catching the next filter feature before review does.

Troubleshooting

Problem

Cannot replace concatenation for dynamic ORDER BY

Solution

Identifiers cannot be bound as values; allow-list the sortable columns and interpolate only the validated member.

Problem

ORM hides raw fragments

Solution

Raw query fragments inside ORM calls are still concatenation; grep for the raw escape hatch specific to your ORM and attack those sites too.

Problem

Payloads fail for dialect reasons

Solution

Corpus payloads target generic SQL; add dialect-specific ones, such as PostgreSQL dollar-quoting or MySQL comment tricks, for each engine you run.

Limitations

Passing the corpus proves resistance to those payloads, not to all injection techniques; the scan plus review remains necessary.
Second-order injection via stored values still needs parameterization at every read site, not just the write site.

Attack your own code before someone else does

Injection survives because it looks like a feature: string building that works for every legitimate input. The audit that finds it is offensive, not defensive: enumerate every place SQL text is assembled, fire canonical payloads at each, and watch what comes back. Parameterization makes the payloads inert; anything that returns extra rows or executes trailing statements is a finding, and every finding becomes a permanent regression test.

const ATTACKS = ["' OR '1'='1", "1; DROP TABLE users;--", "' UNION SELECT password FROM users--"];

// The fix: values are bound, never concatenated
const SAFE = "SELECT id, name FROM users WHERE name = $1";
const rows = await pool.query(SAFE, [userInput]);

Escape hatches are where ORM code hides risk

ORMs reduce injection dramatically, until someone needs a dynamic sort or a reporting query and drops into the raw-fragment API. Those escape hatches are the modern injection sites. Grep for the specific escape hatch by name, attack each occurrence, and the ORM safety story becomes true for the whole codebase rather than the happy path.

Turn every finding into a test

The corpus outlives the audit: each payload that once worked becomes an assertion that it no longer does. The next developer to refactor the query builder runs the corpus in CI, and the moment a refactor reintroduces concatenation, a named payload fails a named test. That converts security review from archaeology into a build gate.