Skip to contentExploitQuest
Join free
LearnSecure Coding

Lesson 1 of 1 in Never Build a Query as a String

Parameterise Everything

The database should never see your data as part of the sentence. One rule, no exceptions, and the reason every clever alternative has lost.

3 min read

Not yet reviewed

SQL injection has been the same bug for twenty-five years, and it has one cause: the query and the data travel to the database in the same string, so the database has no way to tell which is which. Every fix that keeps them in one string has eventually failed.

The bug

const q = "SELECT * FROM users WHERE email = '" + email + "'"

The fix

db.query('SELECT * FROM users WHERE email = $1', [email])

In the second form the query text is fixed before your data exists. The driver sends the sentence and the values separately, and the database parses the sentence first — so nothing in email can become syntax.

Why escaping loses

The tempting alternative is to keep concatenating and escape the input first. This is where the last two decades of injection bugs live, because correct escaping depends on things your escaping function does not know.

The database's character encoding
Whether the value lands inside quotes, or bare
Whether it is a value at all, or an identifier
Which of several SQL dialects is at the far end
  1. Line 1The classic break. In some multi-byte encodings a crafted sequence consumes the backslash your escaper added, freeing the quote after it.
  2. Line 2WHERE id = 5 takes no quotes, so quote-escaping protects nothing and 5 OR 1=1 sails through.
  3. Line 3A table or column name cannot be parameterised at all. If it varies, it must come from a fixed list you wrote — never from input.
  4. Line 4An escaper written for one database is wrong on another, and nothing tells you when the driver underneath changes.

Note

Parameterisation needs to know none of that. The values never enter the sentence, so there is no context to get wrong.

The one thing you cannot parameterise

Identifiers. If a user chooses which column to sort by, no placeholder helps — a parameter is a value, and a column name is syntax. The answer is never to escape it; it is to map the input onto a list you control.

Still injectable

const q = `SELECT * FROM posts ORDER BY ${escape(sortBy)}`

Not injectable, by construction

const COLUMNS = { date: 'created_at', title: 'title' }
const column = COLUMNS[sortBy] ?? 'created_at'
const q = `SELECT * FROM posts ORDER BY ${column}`

The second cannot produce a query you did not write, whatever sortBy contains, because the only strings that reach the query are the two you typed.

How this platform enforces it

Every statement in this product goes through the driver's parameterised interface, including the content importer — which reads files written by us, reviewed by us, and applied by our own build. It is still parameterised, because a content file is the most likely thing in the system to be authored by somebody who is not us, and the day that changes nobody wants to be relying on having remembered.

A reporting page lets a user pick which column to group by. What is the safe implementation?

Tip

Search your codebase for string concatenation next to the word SELECT, INSERT, UPDATE or DELETE. On most projects this is a five-minute grep and finds either nothing or something urgent.