Parameterised queries and SQL injection
🏠The everyday picture
A bank form has boxes: "Pay ₹____ to ____". Whatever someone writes in the box is treated as an amount or a name, never as a new instruction to the bank. If the clerk instead copied the customer's words straight into the bank's instruction book, "₹10, and also close everyone else's accounts" would be obeyed.
📘What it really is
- SQL injection happens when user input is joined into SQL text:
// NEVER do this
db.prepare(`SELECT * FROM customers WHERE name = '${req.query.name}'`).all();
If someone sends the name x' OR '1'='1, the query returns every customer. Cleverer input can read other tables or
delete data. It has been one of the most damaging security flaws for decades.
- Parameterised queries fix it: the SQL has placeholders, and the values are sent separately, so the database always treats them as data, never as SQL:
db.prepare("SELECT * FROM customers WHERE name = ?").all(req.query.name);
db.prepare("INSERT INTO customers (name, phone, city) VALUES (@name, @phone, @city)").run(input);
- Placeholders work for values only. Table names, column names and sort directions can't be parameters; choose them from an allow-list in code (Module 47, Lesson 5):
const sortColumns = { name: "name", joined: "joined_on" } as const;
const column = sortColumns[query.sort] ?? "name";
- Every database library supports parameters; ORMs use them automatically (Module 61). If you ever see user input inside a
SQL string with
${...}or+, treat it as a serious bug, including in AI-written code.
🖱️Try it
Write a deliberately unsafe search function in a scratch file and show that the input x' OR '1'='1 returns every
customer. Then rewrite it with a placeholder and show the same input returns nothing. Add a sorted list function that uses
an allow-list for the sort column.
✅Quick check
What makes a query vulnerable to SQL injection?
Show the answer
Joining user input directly into the SQL text, so it can change the query's meaning.
Can a column name for sorting be passed as a ? parameter?
Show the answer
No. Parameters are for values; choose column names from an allow-list in code.