Write parameterized queries

Never build SQL by gluing request values into a string. A title like Dune'); DROP TABLE books; -- turns your insert into two statements, and quoting by hand to prevent that is a game you eventually lose. This is SQL injection, still one of the most common ways APIs get broken into.

The fix is to keep the structure and the values apart. You write the SQL once, with placeholders where values go, and the database library sends the values separately. They are never parsed as SQL, so a quote in a title is just a quote.

Placeholders

The insert for a book looks like this:

INSERT INTO books (id, title, author, published_year, created_at)
VALUES (?, ?, ?, ?, ?)

Each ? is a slot. With node:sqlite you prepare the statement once and run it with the values:

const insertBook = db.prepare(`
  INSERT INTO books (id, title, author, published_year, created_at)
  VALUES (?, ?, ?, ?, ?)
`)

insertBook.run(id, input.title, input.author, input.publishedYear ?? null, new Date().toISOString())

Reading works the same way. db.prepare('SELECT * FROM books WHERE id = ?').get(id) returns the row as an object, or undefined when nothing matches.

Test it with the nasty title above. Create the book, then list the table: the title is stored exactly as sent, quotes and all, and the table is still there.

What placeholders can’t do

Placeholders protect values. They cannot stand in for a column name, a table name or a keyword. ORDER BY ? doesn’t do what you hope: the database treats the bound value as a constant, not as a column.

So when a client can choose the sort field, never interpolate their string. Map a tiny allowlist to fixed SQL fragments:

const sortColumns = { title: 'title', created: 'created_at' }
const column = sortColumns[sort] ?? 'created_at'
const rows = db.prepare(`SELECT * FROM books ORDER BY ${column}`).all()

The interpolation is safe because column can only be one of two strings you wrote yourself. The client picked a key, never the SQL.

Translate outcomes at the boundary

The database speaks in exceptions and empty results. The API speaks in status codes. Do the translation in one place, the repository module, so handlers never see raw database errors.

A missing row becomes a 404. A CHECK or UNIQUE violation becomes a 422 or a 409 Conflict with a problem body. node:sqlite puts SQLite’s extended result code in err.errcode: 275 for a failed CHECK, 1555 for a duplicate primary key, 2067 for a UNIQUE index clash. The low byte of every constraint code is 19, so mask it and you catch the whole family. Anything else, a locked file, a disk error, a bug in your SQL, is an internal failure: log it, return a generic 500.

try {
  insertBook.run(...)
} catch (err) {
  if ((err.errcode & 0xff) === 19) return problem(c, 422, 'Book violates a constraint')
  throw err
}

The throw err at the end matters. Unexpected errors should reach app.onError() and get logged with the request ID, not get swallowed into a vague client message.

When you log, record the error class and the request ID. Don’t log the full SQL with the bound values: a title is harmless, but the same code will one day bind an email address or a token.

Replace the in-memory create and detail operations with prepared statements now. Then run the injection test one more time and check that SELECT count(*) FROM books still works.