SQL injection & placeholders

Injection and parameterised queries

Lesson 16 of 20 · 13 min · Module 5: Server-side defences

Injection happens whenever data is glued into code: a search term into an SQL query, a file name into a shell command. One quote in the right place turns data into instructions.

Start this lesson →

Free. No installation: you write and run the code in your browser, and it is checked automatically.

By the end, you’ll be able to…

Explain how injection turns data into code
Pass every value through a placeholder
Keep error details away from visitors

The idea

When a query is built as text, such as "… WHERE name = '" + name + "'", input like ' OR 1=1 -- changes the logic of the query. Parameterised queries send the SQL and the values separately: the SQL contains placeholders such as ?, the values travel next to it, and they can never become SQL. Escaping quotes by hand is fragile; placeholders are the fix. This page has a small pretend database at the bottom, so you can try the attack yourself in the search box.

// Unsafe: the name becomes part of the SQL
db.query("SELECT * FROM users WHERE name = '" + name + "'");
// Safe: the SQL and the value travel separately
db.query('SELECT * FROM users WHERE name = ?', [name]);

01Data that changes the code

With ' OR 1=1 -- as the search term, the query becomes … AND name LIKE '%' OR 1=1 --%'. OR 1=1 is true for every row, and -- turns the rest into a comment, so the hidden product appears.

SELECT name, price FROM products
WHERE hidden = 0 AND name LIKE '%' OR 1=1 --%'

02Placeholders keep data as data

With a placeholder, the database receives the query and the values separately, so a quote in a value is only a character. Every major database library supports placeholders, written as ?, $1 or :name.

db.query('SELECT name FROM products WHERE name LIKE ?', ['%' + term + '%']);

03The same idea everywhere

Shell commands, HTML, addresses and other query languages all have their own kind of injection. The fix is always to keep code and data apart with the safe API for that language, such as argument arrays for commands and textContent for pages.

Your turn

Rewrite searchProducts(term) and productByName(name) so the SQL text contains a ? placeholder and never the term itself: pass '%' + term + '%' and name as values in the second argument of db.query(). Keep the condition hidden = 0, so hidden products stay hidden whatever people type. Try ' OR 1=1 -- in the search box before and after.

What the checks look for

Searching still works
Hidden products stay hidden
Quotes are just characters
The SQL never contains what people typed
Exact lookups work and cannot be tricked
A quote in the search box is harmless

Try it in the editor →

Take this with you

Never build queries from text: pass values as parameters.

Go a little further

Placeholders cannot stand for column names. Sketch how you would let people sort by name or price safely, with an allow-list of column names.