Unit 11.01: Parameterised queries and why concatenation breaks

Unit ID: SQL-M11-U02 - Estimated active time: 14-17 minutes Objective: pass values into a query safely, and demonstrate what string building does instead.

Building SQL with f-strings is a correctness bug

status = "completed"
con.execute(f"SELECT COUNT(*) FROM orders WHERE status = '{status}'").fetchone()
# (988,)

Works - until the value contains a quote. Try a value that closes the string early:

bad = "completed' OR '1'='1"
con.execute(f"SELECT COUNT(*) FROM orders WHERE status = '{bad}'").fetchone()
# (1000,)

1,000 instead of 988. The filter was bypassed entirely and every order was counted. No error, no warning - just a wrong number that looks like a right one.

Parameters treat the value as data

con.execute("SELECT COUNT(*) FROM orders WHERE status = ?", [bad]).fetchone()
# (0,)

Zero, because no order has a status literally equal to completed' OR '1'='1. The database compared the whole string as a value instead of parsing it as SQL - which is exactly right.

This is not only about attackers

The security framing ("SQL injection") makes people think it only matters for public web input. It also matters for:

• A city called O'Brien breaking your query with an apostrophe
• A date passed as a string in the wrong format
• A number arriving as text and comparing lexically (Module 3)

Parameters fix all of these, because the driver handles quoting and type conversion.

Lists and multiple parameters

statuses = ["completed", "pending"]
placeholders = ", ".join("?" for _ in statuses)
con.execute(
    f"SELECT status, COUNT(*) FROM orders WHERE status IN ({placeholders}) GROUP BY status",
    statuses,
).fetchall()
# [('completed', 988), ('pending', 12)]

The only thing interpolated is the number of placeholders, never the values. That is the safe form of dynamic SQL: structure from code, values from parameters.

Practice

Rewrite this safely and explain what could go wrong with the original.

# Whatever a user types arrives here as a string. Run it with both values.
country = "IN"                 # then try:  country = "IN' OR '1'='1"
con.execute(f"SELECT COUNT(*) FROM customers WHERE country = '{country}'").fetchone()
# 'IN'            -> (2813,)
# "IN' OR '1'='1" -> (4812,)   every customer, from a filter that asked for one country

country = "IN' OR '1'='1"
con.execute("SELECT COUNT(*) FROM customers WHERE country = ?", [country]).fetchone()
# (0,)  -- the whole string is treated as a value, so it matches no country

Takeaway

Never build SQL by concatenating values. Interpolate structure if you must; pass values as parameters always.

---

Mark complete