CY3: Secure code: fixing flaws with proof
Injection and parameterised queries
🎯You'll be able to
Recognise a query built from raw text and rewrite it with placeholders so that input is always treated as data.
🏠The everyday picture
A form with boxes, "Name: ___", can't change what the form asks, whatever is written in the box. A blank sheet where someone writes both the question and the answer can say anything.
📘What it really is
-
The flaw: building a query by pasting input into text, such as
f"... WHERE name LIKE '%{name}%'". A quote mark in the input ends the text early, and what follows is read as part of the query. -
The sign it's there: an apostrophe, as in D'Souza, makes the search fail with an error.
-
The fix: placeholders. In Python's
sqlite3, write"... WHERE name LIKE ?"and pass the value separately:(f"%{name}%",). The database receives the query and the value apart, so the value can't change the query. -
Every language has this:
?or%splaceholders, or named ones like:name. Object-relational mappers use them for you, unless you write raw queries. -
Some parts can't be placeholders, such as column names in
ORDER BY. Choose those from a fixed list of allowed values. - Least privilege helps too: the app's database account shouldn't be able to drop tables.
📝A worked example
Input: the starter's search_patients, and the search text x' OR '1'='1' --.
Steps:
- Read the starter code: the text is pasted into the query.
- Predict the result: the quote closes the text,
OR '1'='1'is true for every row, and--turns the rest into a comment. Every patient is returned. - Rewrite with a placeholder and pass
f"%{name}%"as a value. The same input is now just a strange name, and no patient matches.
Expected output: the fixed function returns [] for that input, and finds Test Patient D'Souza for D'Souza.
🌍Where it's used
- Large breaches in the 2000s and 2010s often began with injection in a single search or login form.
- Static analysis tools, such as Bandit for Python, flag queries built with string formatting.
- Code review: any query built with
+,%or an f-string gets a second look.
📝Case scenario
A coaching institute's student portal failed whenever a student with an apostrophe in their name searched for their results. The developer added code to delete apostrophes, which broke names. A reviewer replaced the query with placeholders, and both the error and the hidden injection flaw disappeared.
🖱️Try it
In the kit's starter folder, run python -m pytest tests/test_fixes.py -k "01 or 02". Success: both tests fail, and you can say from
the failure messages which line of app/bookings.py causes them.
⚠️Common mistakes
- Escaping by hand. Sign: code that doubles quote marks. Fix: placeholders.
- Placeholders for some values only. Sign: one value still pasted in. Fix: every value from outside goes through a placeholder.
- Putting the
%inside the query text. Sign:LIKE '%?%'. Fix: add the%to the value you pass.
🛠️For your project
Tests 1 and 2 in tests/test_fixes.py check that the injection input returns nothing and that D'Souza is found.
✅Quick check
Why does x' OR '1'='1' -- return every row from a query built by pasting text?
Show the answer
The quote ends the text, the OR makes the condition always true, and -- hides the rest.
Where does the % go in a parameterised LIKE search?
Show the answer
In the value you pass, not in the query text.