These are the notes for Cybersecurity Fundamentals (defensive), episode 38. Episode 37 taught the server to check who may touch what. Today, Neha types one quote mark into a shop's search box and the database throws an error. From there we invent the idea of injection, meet the kinds of SQL injection that defenders recognise, and build the real fix, parameterised queries (also called prepared statements). Then we test the shortcuts teams reach for (escaping, "we use an ORM", "we use stored procedures"), see a second-order injection strike weeks later, and finish with least-privilege database accounts that limit the damage. Every term is explained the first time it appears, and the Glossary at the end defines them all again.
Neha, Ravi, Asha, the shop and every value are made up. Everything runs locally, on small in-memory SQLite databases the programs create themselves. Never try these inputs on a website or system you do not own or are not explicitly authorised to test: doing so can be a crime. Every code sample runs with Python 3.13 (SQLite 3.53; the ORM demo uses SQLAlchemy 2), and every output below is copied from a real run.
The problems you'll solve
| Problem | Idea it teaches |
|---|---|
| One quote mark breaks the shop search | Interpreter, injection, SQL injection, the string concatenation flaw |
| Hidden rows appear; errors describe the query | In-band and error-based injection; blind (boolean and time-based) as concepts |
A login with asha' -- and no password |
Why it's code, not "bad characters": SQL comments |
| How to keep data out of the code | Parameterised queries, prepared statements, placeholders |
| "Just escape the quotes" | Escaping and its limits; allowlists for column names |
| A user name that strikes weeks later | Second-order SQL injection; no trusted strings |
| "We use an ORM / stored procedures" | ORM raw-query pitfalls; when stored procedures help |
| What if a mistake still slips through? | Least-privilege database accounts, defence in depth |
Problem 1: one quote mark
Neha searches an online shop for chai and gets two products. Then she types chai' (with an apostrophe, the single quote mark) and the page shows a database error instead. A search box should never be able to break a database.
First, two basic words:
- Database: an organised store of data that programs can query. SQLite is a small database that lives in one file (or, in our demos, only in memory).
- SQL (Structured Query Language): the language used to ask a relational database for data, for example
SELECT name FROM products WHERE price < 200. A query is one such request. A table is a grid of rows and columns, like a spreadsheet sheet.
Demo 1: a search built by gluing strings
# A toy shop search. The query is built by GLUING text together.
import sqlite3
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE products(name TEXT, price INT, hidden INT)")
db.executemany("INSERT INTO products VALUES(?,?,?)", [("masala chai", 180, 0),
("chai cups", 250, 0), ("ginger tea", 120, 0), ("staff-only test item", 1, 1)])
def search(text):
sql = "SELECT name, price FROM products WHERE hidden = 0 AND name LIKE '%" + text + "%'"
print("SQL:", sql)
try:
for row in db.execute(sql): print(" ", row)
except sqlite3.Error as e:
print(" ERROR:", type(e).__name__, "-", e)
search("chai")
search("chai'")
Output:
SQL: SELECT name, price FROM products WHERE hidden = 0 AND name LIKE '%chai%'
('masala chai', 180)
('chai cups', 250)
SQL: SELECT name, price FROM products WHERE hidden = 0 AND name LIKE '%chai'%'
ERROR: OperationalError - unrecognized token: "'"
Look at the second printed SQL: Neha's quote closed the text early ('%chai'), and the leftover %' confused the database. SQLite raised OperationalError: unrecognized token: "'".
Why? The program mixed two things in one string: the code we wrote (SELECT ... LIKE '%) and the data Neha typed. The database cannot tell where one ends and the other begins.
Real-life example: the station announcer
A railway announcer reads a script with a blank: "Train to ____ is late." Anyone may fill in the city. Someone writes "Pune. All trains are cancelled", and the announcer reads the whole thing aloud: the extra sentence becomes an announcement. That's injection: data read as instructions. The fix (coming soon) is a form box that is only ever read as a city name.
What injection is
| Term | Plain meaning | Example |
|---|---|---|
| Interpreter | A program that reads text and acts on it | SQLite reading SQL; a shell reading commands |
| Untrusted data | Anything a user (or another system) can influence | A search box, a URL, a cookie, an uploaded file name |
| Injection | Untrusted data reaches an interpreter as part of a command | chai' changes the SQL's structure |
| SQL injection (SQLi) | User input changes the meaning of a SQL query | ' OR 1=1 -- makes a condition always true |
| String concatenation flaw | Building a query by joining strings with user input | "... LIKE '%" + text + "%'" |
| Concatenation | Joining strings end to end | "ab" + "cd" gives "abcd" |
The database never sees our intentions. It sees only the final string, and runs whatever SQL it says.
Problem 2: what an attacker learns
Demo 2: three inputs, three behaviours
-- starts a comment in SQL: everything after it on the line is ignored. The function also shows the raw database error to the user, as careless apps do.
# Same glued query. Three kinds of input change what the database DOES.
import sqlite3
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE products(name TEXT, price INT, hidden INT)")
db.executemany("INSERT INTO products VALUES(?,?,?)", [("masala chai", 180, 0),
("ginger tea", 120, 0), ("staff-only test item", 1, 1)])
def search(text):
sql = "SELECT name, price FROM products WHERE hidden = 0 AND name LIKE '%" + text + "%'"
try:
return [r[0] for r in db.execute(sql)]
except sqlite3.Error as e:
return "ERROR shown to the user: " + str(e)
print("normal :", search("tea"))
print("OR 1=1 :", search("' OR 1=1 --")) # WHERE becomes always true
print("bad name :", search("' AND secret_col = 1 --")) # the error message talks
Output:
normal : ['ginger tea']
OR 1=1 : ['masala chai', 'ginger tea', 'staff-only test item']
bad name : ERROR shown to the user: no such column: secret_col
' OR 1=1 --turned the condition intohidden = 0 AND name LIKE '%' OR 1=1. BecauseANDbinds tighter thanOR, that is(...) OR 1=1, which is true for every row, so the hidden staff-only item appears.' AND secret_col = 1 --producedno such column: secret_col: the error message told an outsider something about the query's insides.
The kinds of SQL injection (concepts)
Defenders name these so they can recognise them in logs and reports. You do not need attack code to understand them.
| Kind | How information comes back | Seen in the demo? |
|---|---|---|
| In-band | Through the same channel as normal results, right in the page | Yes: the hidden row appeared in results |
| Error-based (a kind of in-band) | Through database error messages shown to the user | Yes: no such column: secret_col |
| Blind, boolean-based | No data or errors shown, but the page looks different when an injected condition is true vs false | Concept only |
| Blind, time-based | The reply is slower when an injected condition is true (the database is made to wait) | Concept only |
| Second-order | Input is stored safely, then used unsafely later in a different query | Yes, in Problem 5 |
"Blind" means the attacker cannot see data directly; they learn one yes/no at a time from behaviour, which is slow but still works. The defences that follow from this:
- Never show raw database errors to users. Show a generic message ("something went wrong") and log the details on the server.
- The most important point: every kind has the same single cause (data mixed into code), so one fix stops them all.
Problem 3: login without a password
Demo 3: the comment trick
In real systems, passwords are checked with a slow password-hashing function in application code (episode 14), not compared inside SQL. This toy only shows what gluing does.
# A glued LOGIN query. The text "--" starts a comment in SQL.
import sqlite3
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE users(name TEXT, password_hash TEXT, role TEXT)")
db.execute("INSERT INTO users VALUES('asha', 'argon2id$...', 'admin')")
db.execute("INSERT INTO users VALUES('neha', 'argon2id$...', 'customer')")
def login(name, pw_hash):
sql = ("SELECT name, role FROM users WHERE name = '" + name +
"' AND password_hash = '" + pw_hash + "'")
print("SQL:", sql)
row = db.execute(sql).fetchone()
return f"logged in as {row}" if row else "wrong name or password"
print(login("asha", "wrong-guess"))
print(login("asha' --", "anything"))
Output:
SQL: SELECT name, role FROM users WHERE name = 'asha' AND password_hash = 'wrong-guess'
wrong name or password
SQL: SELECT name, role FROM users WHERE name = 'asha' --' AND password_hash = 'anything'
logged in as ('asha', 'admin')
The quote closed the name, and -- turned the whole password check into a comment. Nothing went wrong from the database's point of view, so generic error messages would not help here: the query was simply different.
The fix: parameterised queries (prepared statements)
Real-life example: a cheque has boxes
The printed words on a bank cheque are fixed by the bank. You fill in boxes, and the amount box is only ever read as an amount. Writing "and also..." inside it is still just text in the box.
| On a cheque | In SQL |
|---|---|
| Printed text, fixed | The SQL string, fixed: WHERE name = ? |
| The amount box | A placeholder (?) |
| Whatever you write stays in the box | A bound parameter: the value can't change the query's shape |
How it works
- The app sends the SQL with placeholders.
- The database compiles it: works out its structure (which table, which columns, which conditions) before any user data arrives.
- The app sends the values separately; they are filled in as data only.
- A parameterised query is a query written with placeholders, with values passed separately.
- A prepared statement is the database-side idea: a statement compiled once and then executed with values, possibly many times.
- Placeholder spellings depend on the library:
?(sqlite3, JDBC),:name(SQLAlchemy, Oracle),%s(psycopg, MySQL drivers; note: this is not Python%formatting),$1(PostgreSQL native).
Rule: never build SQL with +, f-strings, % formatting or .format(). Pass values as parameters.
Demo 4: the same shop, fixed
# The fix: a PARAMETERISED query. The SQL is fixed; the input travels separately.
import sqlite3
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE products(name TEXT, price INT, hidden INT)")
db.executemany("INSERT INTO products VALUES(?,?,?)", [("masala chai", 180, 0),
("ginger tea", 120, 0), ("staff-only test item", 1, 1)])
db.execute("CREATE TABLE users(name TEXT, role TEXT)")
db.execute("INSERT INTO users VALUES('asha', 'admin')")
SEARCH = "SELECT name FROM products WHERE hidden = 0 AND name LIKE ?"
def search(text):
return [r[0] for r in db.execute(SEARCH, ("%" + text + "%",))]
USER = "SELECT name, role FROM users WHERE name = ?"
def find_user(name):
return db.execute(USER, (name,)).fetchone()
for text in ["tea", "chai'", "' OR 1=1 --", "' AND secret_col = 1 --"]:
print(f"{text!r:26} -> {search(text)}")
print("asha' --".ljust(26), "->", find_user("asha' --"))
print("asha".ljust(26), "->", find_user("asha"))
Output:
'tea' -> ['ginger tea']
"chai'" -> []
"' OR 1=1 --" -> []
"' AND secret_col = 1 --" -> []
asha' -- -> None
asha -> ('asha', 'admin')
Every attack is now just a strange search: the database looked for products literally named chai' and found none. Notice that the % wildcards for LIKE are part of the value, not glued into the SQL.
Problem 4: "just escape the quotes"
Escaping means changing special characters so they are read literally. In SQL, a quote inside a text value is written as two quotes (''). A teammate suggests escaping instead of rewriting queries.
Demo 5: escaping works for text, fails for a number; names need an allowlist
# "Just escape the quotes!" ' becomes '' ... works for text, fails for numbers and names
import sqlite3
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE orders(id INT, owner TEXT, total INT)")
db.executemany("INSERT INTO orders VALUES(?,?,?)", [(1, "neha", 180), (2, "ravi", 950)])
esc = lambda s: s.replace("'", "''")
def by_owner(name):
return db.execute("SELECT id FROM orders WHERE owner = '" + esc(name) + "'").fetchall()
def by_id(order_id):
return db.execute("SELECT id, owner FROM orders WHERE id = " + esc(order_id)).fetchall()
print("text, escaped :", by_owner("neha' OR 1=1 --")) # stays inside the quotes: safe
print("number, escaped:", by_id("1 OR 1=1")) # no quote needed to break out!
ALLOWED_SORT = {"price": "total", "newest": "id"} # column names can't be parameters
def sorted_orders(choice):
col = ALLOWED_SORT.get(choice)
if col is None: return "400 unknown sort"
return db.execute(f"SELECT id, total FROM orders ORDER BY {col}").fetchall()
print("sort=price :", sorted_orders("price"))
print("sort=injected :", sorted_orders("total; DROP TABLE orders"))
Output:
text, escaped : []
number, escaped: [(1, 'neha'), (2, 'ravi')]
sort=price : [(1, 180), (2, 950)]
sort=injected : 400 unknown sort
- The text query stayed safe: the escaped input stayed inside its quotes.
- The number context has no quotes at all, so
1 OR 1=1needs no quote to change the query, and escaping did nothing. It returned Ravi's order too. - Column and table names (identifiers) cannot be parameters: a placeholder is always a value. (In SQLite,
ORDER BY ?is accepted but sorts by a constant, so it silently does nothing.) For names, map the user's choice through an allowlist: a fixed list of allowed values, like{"price": "total", "newest": "id"}. Anything else gets400 Bad Request.
OWASP's 2025 Injection page treats escaping as a last resort for the rare dynamic query that can't be parameterised, warns that escaping is error-prone, and notes that structure names like table and column names can't be escaped, which it says is common in reporting software.
Problem 5: second-order SQL injection
Someone signs up with a strange name. The sign-up code uses parameters, so the name is stored safely, exactly as typed. Weeks later, a nightly report reads that name back from the database and glues it into a new query, assuming "it came from our own database, so it's safe".
Demo 6: stored safely, used unsafely
# Second-order injection: stored SAFELY, used UNSAFELY later
import sqlite3
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE users(name TEXT, city TEXT)")
db.execute("CREATE TABLE orders(owner TEXT, item TEXT)")
db.executemany("INSERT INTO orders VALUES(?,?)", [("neha", "chai"), ("ravi", "charger")])
signup = "x' OR '1'='1" # a strange user name...
db.execute("INSERT INTO users VALUES(?, ?)", (signup, "Pune")) # step 1: safe
# step 2, weeks later: a nightly report trusts what is ALREADY in the database
name = db.execute("SELECT name FROM users WHERE city = 'Pune'").fetchone()[0]
report = "SELECT owner, item FROM orders WHERE owner = '" + name + "'"
print("glued :", db.execute(report).fetchall())
fixed = "SELECT owner, item FROM orders WHERE owner = ?"
print("params :", db.execute(fixed, (name,)).fetchall())
Output:
glued : [('neha', 'chai'), ('ravi', 'charger')]
params : []
The glued report returned everyone's orders for one strange user. That is second-order SQL injection: the injection fires later, in a different place from where the data entered. The lesson: there is no trusted string. Data from your own database, files, other services, or admin screens is still data. Parameterise every query.
Problem 6: "we use an ORM" and "we use stored procedures"
ORMs
An ORM (object-relational mapper) lets you work with database rows as programming-language objects and generates parameterised SQL for you. SQLAlchemy (Python), Hibernate (Java), Entity Framework (.NET) and Django's ORM are examples. Normal ORM calls are safe. But every ORM also lets you write raw SQL, and an f-string there brings back the gluing.
Demo 7: SQLAlchemy, raw f-string vs bound parameter
from sqlalchemy import create_engine, text
eng = create_engine("sqlite://")
with eng.connect() as c:
c.execute(text("CREATE TABLE products(name TEXT, hidden INT)"))
c.execute(text("INSERT INTO products VALUES('masala chai',0),('staff-only test item',1)"))
q = "' OR 1=1 --"
bad = c.execute(text(f"SELECT name FROM products WHERE hidden = 0 AND name = '{q}'")).fetchall()
good = c.execute(text("SELECT name FROM products WHERE hidden = 0 AND name = :q"), {"q": q}).fetchall()
print("raw f-string :", bad)
print("bound param :", good)
Output:
raw f-string : [('masala chai',), ('staff-only test item',)]
bound param : []
Same library, different result. In code review, search for raw queries (text(, .raw(, execute( with f-strings, +, % or .format nearby) in every language.
Stored procedures
A stored procedure is a named block of SQL saved inside the database that the app calls by name, like a function. Called with parameters, it behaves like a prepared statement. But OWASP warns that a stored procedure is still injectable if it builds a SQL string inside itself and executes it (for example with EXECUTE IMMEDIATE or exec()).
Input validation (checking input against rules, ideally an allowlist such as "an order ID is digits only") helps, and OWASP recommends server-side allowlist validation, but says it is not a complete defence on its own.
Problem 7: least-privilege database accounts
Real-life example: the hotel key card
If every hotel guest got a master key, one lost card would open every room. So each card opens only one room. For databases, that's a least-privilege account (episode 3): each feature connects with only the rights it needs. Search needs to read products; it never needs DELETE, DROP or access to the users table.
Demo 8: a read-only connection
# Least privilege: the search feature gets a READ-ONLY connection
import sqlite3, os
if os.path.exists("shop.db"): os.remove("shop.db")
setup = sqlite3.connect("shop.db")
setup.execute("CREATE TABLE IF NOT EXISTS products(name TEXT)")
setup.execute("INSERT INTO products VALUES('masala chai')"); setup.commit(); setup.close()
reader = sqlite3.connect("file:shop.db?mode=ro", uri=True) # what the search page uses
print("read :", reader.execute("SELECT name FROM products").fetchall())
for evil in ["DELETE FROM products", "DROP TABLE products"]:
try:
reader.execute(evil)
except sqlite3.OperationalError as e:
print(f"{evil:22}-> {type(e).__name__}: {e}")
print("still:", reader.execute("SELECT count(*) FROM products").fetchone())
Output:
read : [('masala chai',)]
DELETE FROM products -> OperationalError: attempt to write a readonly database
DROP TABLE products -> OperationalError: attempt to write a readonly database
still: (1,)
In database servers like PostgreSQL, MySQL or SQL Server, you do this with separate database users and grants (permissions), for example a shop_reader user that may only SELECT from products. SQLite has no users, so it does it per connection with mode=ro. This is defence in depth: not instead of parameters, but in addition. Never let a web application connect as the database owner or administrator.
How common is SQL injection?
From the OWASP Top 10:2025, A05 Injection page (fetched 9 Nov 2026):
- Injection is A05:2025, down from #3 in 2021.
- 37 CWEs are mapped to it; maximum incidence rate 13.77%, average 3.08%; 1,404,249 total occurrences.
- 62,445 CVEs, the most of any category; OWASP says 100% of the applications tested had some form of injection.
- SQL injection is described as low frequency, high impact, with more than 14,000 CVEs.
From MITRE's 2025 CWE Top 25 Most Dangerous Software Weaknesses (updated 15 Dec 2025): CWE-89, SQL Injection, is #2 (cross-site scripting, CWE-79, is #1).
The whole chain
- A quote mark broke search → injection: data became code.
- Hidden rows and talking errors → in-band, error-based, and the blind kinds.
- A login with
--→ parameterised queries / prepared statements. - Escaping failed on a number → escaping is a last resort; allowlist names.
- A stored name struck weeks later → second-order; parameterise everything.
- ORM raw SQL and stored procedures → bind values there too.
- Mistakes still happen → least-privilege accounts.
Common mistakes (and what they print)
| Mistake | Real message / result | Why | Fix |
|---|---|---|---|
| Gluing input into SQL | sqlite3.OperationalError: unrecognized token: "'" |
Data becomes code | db.execute(sql, (value,)) |
| Showing raw database errors | no such column: secret_col |
The error explains your query | Generic message; log details on the server |
| Escaping a number | number, escaped: [(1, 'neha'), (2, 'ravi')] |
No quote is needed to break out | A parameter, or int(order_id) first |
| One value without a trailing comma | sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 4 supplied. |
("neha") is just the string "neha", which counts as 4 values |
("neha",) with a comma |
| A parameter for a column name | ORDER BY ? silently sorts by a constant |
Placeholders are values, not names | Allowlist mapping |
| Trusting data from your own database | Demo 6: everyone's orders | Second-order injection | Parameterise every query |
Mini project: a small, safe data layer
A search function that combines everything: parameters, an allowlisted sort, a read-only connection, and generic errors with server-side logging.
# Mini project: a small, safe data layer for the toy shop
import sqlite3, logging, os
logging.basicConfig(level=logging.INFO, format="LOG %(levelname)s %(message)s")
if os.path.exists("shop.db"): os.remove("shop.db")
admin = sqlite3.connect("shop.db") # used only for setup/migrations
admin.execute("CREATE TABLE products(name TEXT, price INT, hidden INT)")
admin.executemany("INSERT INTO products VALUES(?,?,?)", [("masala chai", 180, 0),
("ginger tea", 120, 0), ("chai cups", 250, 0), ("staff-only test item", 1, 1)])
admin.commit(); admin.close()
reader = sqlite3.connect("file:shop.db?mode=ro", uri=True) # least privilege for the web app
SORTS = {"price": "price", "name": "name", "price_desc": "price DESC"} # allowlist
def search(text, sort="name"):
order = SORTS.get(sort)
if order is None:
return "400 unknown sort"
sql = f"SELECT name, price FROM products WHERE hidden = 0 AND name LIKE ? ORDER BY {order}"
try:
return reader.execute(sql, ("%" + text + "%",)).fetchall()
except sqlite3.Error as e:
logging.error("search failed: %r (text=%r)", e, text) # details stay on the server
return "500 something went wrong"
print(search("chai"))
print(search("", sort="price_desc"))
print(search("' OR 1=1 --"))
print(search("tea", sort="price; DROP TABLE products"))
print(reader.execute("SELECT count(*) FROM products").fetchone())
Output:
[('chai cups', 250), ('masala chai', 180)]
[('chai cups', 250), ('masala chai', 180), ('ginger tea', 120)]
[]
400 unknown sort
(4,)
Extend it: add a max_price filter as a parameter, and a test that tries every input from this page and asserts that the hidden item never appears.
Practice
- Fix demo 1 with a parameter, so
chai'simply finds nothing. - Add a "sort by name" option to demo 5's allowlist and show that
name; DROP TABLE ordersis refused. - Make demo 6's nightly report safe. Bonus: do it with a single
JOINquery. - In one sentence: why is escaping a "last resort"?
Practice 1 solution
# Practice 1 solution: demo 1 with a parameter
import sqlite3
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE products(name TEXT, price INT, hidden INT)")
db.executemany("INSERT INTO products VALUES(?,?,?)", [("masala chai", 180, 0),
("chai cups", 250, 0), ("ginger tea", 120, 0), ("staff-only test item", 1, 1)])
def search(text):
sql = "SELECT name, price FROM products WHERE hidden = 0 AND name LIKE ?"
return db.execute(sql, ("%" + text + "%",)).fetchall()
print(search("chai"))
print(search("chai'"))
print(search("' OR 1=1 --"))
Output:
[('masala chai', 180), ('chai cups', 250)]
[]
[]
Practice 2 solution
The mini project above already does this with SORTS = {"price": ..., "name": ..., "price_desc": ...}: an unknown value such as price; DROP TABLE products returns 400 unknown sort, and the table still has 4 rows.
Practice 3 solution
# Practice 3 solution: the nightly report, safe (a parameter, or better, a JOIN in SQL)
import sqlite3
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE users(name TEXT, city TEXT)")
db.execute("CREATE TABLE orders(owner TEXT, item TEXT)")
db.executemany("INSERT INTO orders VALUES(?,?)", [("neha", "chai"), ("ravi", "charger")])
db.executemany("INSERT INTO users VALUES(?,?)", [("x' OR '1'='1", "Pune"), ("neha", "Pune")])
for (name,) in db.execute("SELECT name FROM users WHERE city = ?", ("Pune",)).fetchall():
rows = db.execute("SELECT item FROM orders WHERE owner = ?", (name,)).fetchall()
print(f"{name!r:16} -> {rows}")
joined = db.execute("""SELECT u.name, o.item FROM users u JOIN orders o ON o.owner = u.name
WHERE u.city = ?""", ("Pune",)).fetchall()
print("one JOIN query ->", joined)
Output:
"x' OR '1'='1" -> []
'neha' -> [('chai',)]
one JOIN query -> [('neha', 'chai')]
Practice 4 answer
Because correct escaping depends on the exact context (text, number, identifier, LIKE pattern, database brand and settings), it is easy to get wrong and breaks when the system changes, while parameters keep data and code separate by design.
Quick quiz
# q comes from the user
# A
db.execute("... name = '%s'" % q)
# B
db.execute(f"... name = '{q}'")
# C
db.execute("... name = '" + esc(q) + "'")
# D
db.execute("... name = ?", (q,))
Which one is safe?
Answer
D. Only D keeps q out of the SQL text: it is sent as a parameter. A and B are just string building (% formatting and f-strings). C escapes, which is fragile and context-dependent.
Interview questions
- What is SQL injection, in one sentence? User input changes the meaning of a SQL query because it was mixed into the query text.
- What is the primary defence? Parameterised queries (prepared statements): fixed SQL with placeholders, values passed separately.
- Why doesn't escaping fully solve it? It depends on context: numbers and identifiers have no quotes to escape, and escaping rules differ between databases and settings.
- How do you handle a user-chosen sort column? Map it through an allowlist of known column names; never put the raw value into SQL.
- What is second-order SQL injection? Data stored safely is later read back and concatenated into another query, where it injects.
- Is an ORM automatically safe? Its normal API is; raw SQL with string building inside an ORM is not.
- Are stored procedures safe? Only if called with parameters and if they don't build and execute dynamic SQL internally.
- What is blind SQL injection? Injection where no data or errors are shown, but true/false conditions change the page or its timing, leaking one bit at a time.
- How does least privilege help? Even if an injection happens, a read-only or narrowly granted account can't delete data, drop tables or read other tables.
- Why hide database error messages? They reveal query structure, table and column names (error-based injection); log them server-side instead.
Glossary
| Term | Meaning | Example |
|---|---|---|
| Database | An organised store of data that programs can query | The shop's products and orders |
| SQLite | A small database engine that keeps a database in one file (or in memory) | sqlite3.connect(":memory:") |
| SQL | Structured Query Language, for querying relational databases | SELECT name FROM products |
| Query | One request to a database | SELECT ... WHERE price < 200 |
| Table | A grid of rows and columns in a database | products(name, price, hidden) |
| Interpreter | A program that reads text and acts on it | SQLite, a shell, a template engine |
| Untrusted data | Any input a user or outside system can influence | Search text, URL, cookie |
| Injection | Untrusted data reaching an interpreter as part of a command | chai' breaks the SQL |
| SQL injection (SQLi) | User input changing the meaning of a SQL query | ' OR 1=1 -- |
| String concatenation flaw | Building a query by joining strings with input | "... '" + text + "'" |
| Concatenation | Joining strings end to end | "ab" + "cd" |
| SQL comment | -- makes the database ignore the rest of the line |
'asha' --' AND ... |
| In-band SQL injection | Results come back through the normal response | Hidden row in search results |
| Error-based SQL injection | Information leaks through database error messages | no such column: secret_col |
| Blind SQL injection | No data shown, but behaviour leaks yes/no answers | Page differs or slows down |
| Boolean-based blind | The page differs when a condition is true or false | "Found" vs "not found" |
| Time-based blind | The reply is slower when a condition is true | A delayed response |
| Second-order SQL injection | Stored safely, injected later in another query | Demo 6's nightly report |
| Parameterised query | SQL with placeholders; values passed separately | execute("... = ?", (name,)) |
| Prepared statement | A statement compiled once, then run with values | Database-side parameterised query |
| Placeholder | A marker where a value goes | ?, :q, %s, $1 |
| Bound parameter | A value attached to a placeholder, sent separately | {"q": q} |
| Compile (a query) | The database works out a query's structure | Before values arrive |
| Escaping | Changing special characters so they're read literally | ' becomes '' |
| Identifier | The name of a table or column | total, orders |
| Allowlist | A fixed list of allowed values; everything else is refused | {"price": "total"} |
| ORM | Object-relational mapper: rows as objects, SQL generated for you | SQLAlchemy, Hibernate |
| Raw query | Hand-written SQL inside an ORM | text("SELECT ...") |
| Stored procedure | A named block of SQL saved in the database | CALL get_orders(?) |
| Dynamic SQL | SQL built as a string at run time and executed | EXECUTE IMMEDIATE |
| Input validation | Checking input against rules before use | Order ID must be digits |
| Least privilege | Give each part only the rights it needs | Read-only search account |
| Database user / grant | An account in a database server and its permissions | shop_reader may only SELECT |
| Defence in depth | Several independent layers of protection | Parameters + read-only account |
| OperationalError | A sqlite3 exception for failed database operations | attempt to write a readonly database |
| OWASP Top 10 | OWASP's list of the most critical web app risks | A05:2025 Injection |
| CWE | Common Weakness Enumeration: a catalogue of weakness types | CWE-89 SQL Injection |
| CVE | A public identifier for one specific vulnerability | One of 62,445 injection CVEs |
Summary
- Injection happens when data is mixed into code and sent to an interpreter. SQL injection is the database version.
- All kinds (in-band, error-based, blind, second-order) share one cause and one fix: parameterised queries / prepared statements.
- Escaping is a last resort; it fails for numbers and can't handle names. Allowlist column and table names.
- No trusted strings: parameterise data from your own database too, and raw ORM queries and stored procedures.
- Show generic errors, log details server-side, and connect with least-privilege accounts.
- Next episode: other injection (command, NoSQL, LDAP, template and header injection).