Use parameterized database queries

SQL injection shows up whenever you glue user data into a query string. Once the value and the query travel in the same channel, a crafted value can rewrite the query. Parameters keep the value in its own channel, where it can only ever be data.

The pattern that fails

Here the search term is pasted straight into the SQL text:

// Vulnerable: input becomes part of the query structure
const rows = await db.query(
  `SELECT id, title FROM notes WHERE owner_id = ${ownerId} AND title = '${term}'`
)

Now send a term of ' OR '1'='1. The query becomes:

SELECT id, title FROM notes WHERE owner_id = 7 AND title = '' OR '1'='1'

The quote closes the string early, and OR '1'='1' is always true. The database returns every note in the table, not just the ones owned by user 7. No error, no warning. Just too many rows.

Bind values

The safe version sends the query text and the values separately. The driver never treats a value as syntax:

SELECT id, title
FROM notes
WHERE owner_id = ? AND id = ?

const rows = await db.query(
  'SELECT id, title FROM notes WHERE owner_id = ? AND id = ?',
  [ownerId, noteId],
)

Pass the same ' OR '1'='1 as a value now and the database looks for a note whose id is literally that string. It finds nothing. rows is []. That empty array is the proof the fix works.

My rule is simple: if a query string contains ${, it’s a bug until proven otherwise.

Allowlist identifiers

Parameters cover values, not identifiers. You can’t bind a column name, so ORDER BY ? doesn’t do what you hope. For a sort option, map the user’s choice through a fixed lookup instead:

const sortColumns = { title: 'title', created: 'created_at' }
const orderBy = sortColumns[req.query.sort] ?? 'created_at' // reject anything else

A request for ?sort=title gets title. A request for ?sort=owner_id;DROP TABLE notes gets created_at. The user picks from a menu you wrote, and nothing else reaches the SQL.

The same goes for table names, LIMIT values you can’t bind on some drivers, and anything else that is structure rather than data. When in doubt, put it in a lookup.

Try this on your own project: find one concatenated query and replace it with parameters. Search for a title that contains a single quote and check you get the right note back. Then send SQL control characters and an unsupported sort field, and confirm you get no extra rows and no error message that mentions SQL.