SQLDailyDrills

SQL injection: never glue user text into a query

The queries on this site are typed by you on purpose. In an application, some of the text in a query comes from a form, a URL or an API. If that text is pasted into the SQL, a visitor can change what the statement does. This page shows the mistake on the bakery database, then the fix. It is not a guide to attacking other systems.

The query you meant

Suppose an app looks up a customer by last name. The intended SQL is a filter on that name only.

SELECT first_name, last_name
FROM customers
WHERE last_name = 'Lopez'
ORDER BY customer_id;

Result: Ana Lopez, one row.

What glued-in text can do

If the app builds the statement as `WHERE last_name = '` plus whatever the visitor typed plus `'`, then typing extra SQL inside that string changes the filter.

The query below is the same statement after someone types a last name that never ends the quote and adds `OR 1 = 1`. `1 = 1` is always true, so the WHERE no longer restricts rows. The same class of bug on UPDATE or DELETE without a tight WHERE can change or remove every row.

SELECT first_name, last_name
FROM customers
WHERE last_name = 'Lopez' OR 1 = 1
ORDER BY customer_id;

Result: All eight customers. The name filter no longer limits the result.

The fix: bound parameters

Do not join user text into SQL. Send the statement with a placeholder and pass the value separately. PostgreSQL uses `$1`, `$2`, and so on. The database treats the value as data, never as syntax, so a last name that looks like SQL stays a last name.

The query you write is then always `WHERE last_name = $1`. Libraries and ORMs call this a parameterized query or a prepared statement. You still have to use it: an ORM that concatenates strings has the same bug.

SELECT first_name, last_name
FROM customers
WHERE last_name = 'Lopez'
ORDER BY customer_id;

Result: Ana Lopez again: the lookup with a bound value, shown here with a literal because this editor has no application layer.

Common questions

Is quoting the value with extra apostrophes enough?
No. People forget an escape, encodings differ, and numbers are not quoted at all. Bound parameters skip that whole class of mistakes.
Does an ORM mean I can ignore this?
Only if every query goes through its parameter API. Raw SQL strings inside an ORM are still concatenated SQL. Prefer the query builder or `execute(sql, [value])`.
What about UPDATE and DELETE?
The same rule: the WHERE must use parameters, never glued text. A missing or broken WHERE on those statements is how a whole table is changed at once.

Practise it