Day 3 of 1060 minShell WalkerHands-on lab
Day 3: Catching Insecure Generated Code: SQL Injection
Taught by Mr Elite Updated
By the end of today you can
- Ask an assistant for a login handler and review it with the day 2 checklist
- Recognise string-built SQL queries in generated code
- Bypass a vulnerable login in the sqli-login-1 lab to prove the flaw is real
- Rewrite a vulnerable query as a parameterised query and test it
Warm-up
Answer from memory first, then tap to check. Recalling yesterday is what makes it stick.
In the day 2 checklist, which four things do you run on every changed file?
Input, auth, errors and dependencies.
Should you treat a PR description as evidence or as a claim?
A claim. The diff is the evidence.
You ask your assistant for a login handler. It answers in four seconds with tidy code, a friendly comment and a reassuring line saying it handles authentication securely. The tests pass. You are tempted to merge it.
Today we slow that moment down. You will review that code with your checklist, see exactly how one line lets an attacker walk in as admin, prove it in a lab, and then fix it properly. This is the single most common serious flaw in generated code, so it is worth learning to spot on sight.
What SQL injection is and why assistants write it
SQL injection happens when user input is glued into a database query as text, so the database cannot tell where your query ends and the attacker's input begins. The attacker's typing becomes part of the command.
Assistants produce this often because string-building is the shortest path to working code, and old tutorials full of it were part of what they learned from. The code runs, the happy-path test passes, and nothing looks wrong unless you look for it.
- SQL injection (SQLi)
- Input that changes the meaning of a database query because it was concatenated into it.
- Parameterised query
- A query where the SQL is fixed and user values are passed separately, so they are only ever treated as data.
- Concatenation
- Joining strings together, for example with +, f-strings or template literals.
query = "SELECT * FROM users WHERE username = '" + username + "' AND password = '" + password + "'"
user = db.execute(query).fetchone()If the username is admin'-- the query becomes: SELECT * FROM users WHERE username = 'admin'--' AND password = ''. The two dashes start a comment, so the password check vanishes.
Quick check
Which line is vulnerable?
The f-string places the input into the SQL text itself, while the other two pass it as a separate parameter.
Watch me do it: generate, review, break, fix
I will do this with Python and SQLite because both ship with Python and need nothing installed. Everything runs on your own machine. Use Node if you prefer, the pattern is identical.
import sqlite3
db = sqlite3.connect("app.db")
db.execute("DROP TABLE IF EXISTS users")
db.execute("CREATE TABLE users (username TEXT, password TEXT)")
db.execute("INSERT INTO users VALUES ('admin', 'S3cret-demo')")
db.commit()import sqlite3
from login_ai import login
db = sqlite3.connect("app.db")
print(login(db, "admin", "S3cret-demo"))
print(login(db, "admin'--", "wrong"))def login(db, username, password):
row = db.execute(
"SELECT 1 FROM users WHERE username = ? AND password = ?",
(username, password),
).fetchone()
return row is not NoneNotice the fixed code still stores a plain password. A real system would store a salted hash from bcrypt or argon2. Parameterising fixes injection, not every login problem, so keep your checklist open.
Quick check
After the fix, why does admin'-- fail to log in?
The placeholder sends the input as a value, so the quote and dashes are just characters in a username.
Your turn in the lab
You have seen the flaw on code you controlled. Now prove you can exploit it on an application you did not write. The lab below is a safe, deliberately vulnerable site. Use it only through the SecurityElites lab interface, and never try these inputs on real sites.
Why the fix works, and what else to check
With a parameterised query the database receives two separate things: the SQL text, parsed once, and the values. Nothing in the values can change the structure of the statement. That is why escaping by hand is the weaker option. It relies on you remembering every special character, every time.
Ask for the same fix in your review notes each time: name the file and line, state the category, and give the one-line replacement. That is the finding format from day 2.
Your turn
SQL Injection
MiniMail's login page builds its SQL by concatenating the username and password, exactly like the vulnerable code you just reviewed. Using it now lets you see the same flaw from the attacker's side, which makes it much easier to spot in a diff later.
What to try
What to notice
- The password check is skipped even though you never knew the password.
- Small changes to the quote or comment characters change the error message, which reveals the query structure.
- Nothing on the page warns you. The app behaves as if the login were legitimate.
If it did not work
Trusting generated code because the tests pass
Tests usually check valid logins only. Add one hostile input such as admin'-- and make it part of the test suite.
Fixing the quote character only, for example by stripping apostrophes
Blocklists miss cases and break legitimate names like O'Brien. Use parameterised queries.
Assuming an ORM is always safe
ORMs are safe when you use their query builders. Raw query helpers with string formatting are still injectable, so search for raw, execute and query calls.
Accepting the assistant's claim that the code is secure
A claim in a comment is not evidence. Run the attack and record the result.
Recap
- SQL injection happens when user input becomes part of the SQL text.
- Assistants often write string-built queries because they are short and work on the happy path.
- Prove the flaw with an attack input, not just by reading the code.
- Parameterised queries keep input as data and are the primary fix.
- Parameterising does not fix everything, so keep running the full checklist.
- Add a hostile-input test so the flaw cannot quietly return.
Cheat sheet
' OR '1'='1- Classic always-true condition used to test a quote-closing flaw (own systems and labs only).
admin'--- Closes the string and comments out the rest of the query.
db.execute(sql, (value,))- Python sqlite3 parameterised form using ? placeholders.
%s with a params list- Placeholder style for many other Python database drivers.
Search terms: execute(, query(, f", +- Quick grep targets for string-built SQL in a diff.
Allow-list- Fixed list of permitted names for things that cannot be parameterised, like column names.
Before the next day
Mission: Login query review
Ask your assistant for a simple login query, review it with your day 2 checklist, and write up whether it was safe and why. This builds the habit of testing generated code rather than trusting it.
About 30 min
You are done when: A file login-review.md with the assistant name, the original code, your test results and a verdict of safe or unsafe with reasons.
Want more? Ask the assistant to add a password hashing step, then review that new code for weaknesses such as weak hashes or timing-unsafe comparisons.
Quiz
4 questions. Your score is saved with your progress.
1. Why does admin'-- bypass a concatenated login query?
The apostrophe ends the string value and the dashes turn the remaining query text into a comment.
2. An assistant says its code is secure and the tests pass. What is the best next step?
Claims and happy-path tests are not evidence. A hostile input gives you proof either way.
3. Which fix is most reliable against SQL injection in a login query?
Parameterisation separates SQL structure from values, so no input can change the query.
4. A function builds ORDER BY from a user-supplied column name. What is the right approach?
Identifiers cannot be parameterised, so you must only accept names you have approved in advance.
Finish the day
Mark the day complete to save your progress.
Next: Day 4 moves from the code the assistant writes to the material you send it. You will learn to find secrets in text before you paste it into a prompt, and to redact them safely.
Your notes
Private to this device. Write what you want to remember from today.