Regression-test a SQL injection fix

We’ll reproduce a SQL injection in a disposable in-memory database, then keep a regression test for the fix. The failure leaks another owner’s note, which gives us a concrete result to prevent.

If parameter binding is new to you, read the SQL driver lesson first. It explains placeholders and dynamic identifiers. This exercise checks the security behavior after that syntax change.

Reproduce the leak safely

Use a current Node.js release with node:sqlite. Save this as injection.test.mjs. It uses only fabricated local notes:

import assert from 'node:assert/strict'
import { test } from 'node:test'
import { DatabaseSync } from 'node:sqlite'

test('a concatenated search bypasses the owner filter', () => {
  const db = new DatabaseSync(':memory:')
  try {
    db.exec(`
      CREATE TABLE notes (id INTEGER PRIMARY KEY, owner_id INTEGER, title TEXT);
      INSERT INTO notes VALUES (1, 7, 'Plan the week'), (2, 8, 'Private note');
    `)
    const term = "' OR '1'='1"
    const rows = db.prepare(`
      SELECT id, title FROM notes
      WHERE owner_id = 7 AND title = '${term}'
    `).all()
    assert.deepEqual(rows.map(row => row.id), [1, 2])
  } finally {
    db.close()
  }
})

Run node --test injection.test.mjs. This test passes when the deliberately vulnerable query exposes both owners’ notes. Its purpose is to reproduce the bug before fixing it.

The injected quote ends the title string. OR '1'='1' makes the whole filter true. The database sees a valid statement, so an error handler cannot detect this leak.

Add the security regression

Append this test. The owner ID and search term are both bound values:

test('a bound search returns only matching authorized notes', () => {
  const db = new DatabaseSync(':memory:')
  try {
    db.exec(`
      CREATE TABLE notes (id INTEGER PRIMARY KEY, owner_id INTEGER, title TEXT);
      INSERT INTO notes VALUES (1, 7, 'Plan the week'), (2, 8, 'Private note');
    `)
    const search = db.prepare(`
      SELECT id, title FROM notes WHERE owner_id = ? AND title = ?
    `)

    assert.deepEqual(search.all(7, 'Plan the week').map(row => row.id), [1])
    assert.equal(search.all(7, 'Private note').length, 0)
    assert.equal(search.all(7, "' OR '1'='1").length, 0)

    db.prepare('INSERT INTO notes VALUES (?, ?, ?)')
      .run(3, 7, "Tomorrow's tasks")
    assert.deepEqual(search.all(7, "Tomorrow's tasks").map(row => row.id), [3])
  } finally {
    db.close()
  }
})

Run both tests. Then, as a temporary mutation, replace the bound query in the second test with the concatenated version and adjust its calls to pass the term. The hostile-input assertion should fail. Restore the safe statement.

The quote-heavy title checks normal input too. A fix that rejects every apostrophe would break valid searches.

Carry the checks to your application

In the real route, derive ownerId from the authenticated session. Taking a safely bound owner ID from the request still lets a user ask for someone else’s notes. The object authorization lesson covers that boundary.

Keep a route-level regression that sends the hostile term as a query parameter, checks the HTTP status and response rows, and confirms there is no foreign-owner result. Avoid returning raw driver errors to the client.

For an optional sort field, use a fixed mapping such as { title: 'title', created: 'created_at' }. Parameters cannot substitute column names. Test an unsupported value like owner_id;DROP TABLE notes and confirm the route rejects it or uses its documented default.

Once you have captured the vulnerable behavior, keep the safe regression in the application’s suite. Retain the deliberately vulnerable demonstration only in this isolated practice file.