SQL Injection Prevention
Tiffin's menu has a search box. A curious visitor types ' OR '1'='1 into it, and the page suddenly lists every dish, including unreleased ones. With a slightly different string they could read the customer table. This is SQL injection, one of the oldest web bugs and still one of the most damaging. The fix is simple and absolute.
The rule
Send the query and the data separately; never glue input into SQL.
The bug happens when code builds SQL by pasting input into the query text:
# UNSAFE: the input becomes part of the SQL itself
cur.execute(f"SELECT name, price FROM dishes WHERE name = '{term}'")If term is ' OR '1'='1, the database receives WHERE name = '' OR '1'='1', which is true for every row. The input escaped its quotes and changed the structure of the query.
A parameterized query (also called a prepared statement) sends the SQL with a placeholder, and the value separately:
# SAFE: the driver sends the value as data, never as SQL
cur.execute("SELECT name, price FROM dishes WHERE name = %s", (term,))Now ' OR '1'='1 is just a strange dish name that matches nothing. The placeholder style varies by library (%s, ?, :name), but the idea is the same. ORMs parameterize for you, until you drop into a raw SQL string.
Two edge cases:
- Identifiers can't be parameters. A column name for
ORDER BYor a table name must come from an allow-list in your code: map"price"toprice, reject anything else. - Escaping by hand is not a fix. It is easy to miss a case. Use parameters.
A least-privilege database account (read-only where possible) limits the damage if a hole slips through, but it does not close the hole.
The shortcut
Scan the code for input touching the SQL string: +, f-strings, .format(), % formatting on the query text. Any of these with user input is a finding.
Find the query
"SELECT * FROM orders WHERE id = " + order_id
Drills
Answer fast — trust the rule.
Which line is safe from SQL injection?
With WHERE name = '{term}' built by string formatting, what does ' OR '1'='1 do?
A user picks the sort column from a dropdown. How should the query handle it?
Tiffin's app connects with a read-only database user. What does that achieve?
Remember
- Send the query and the data separately; never glue input into SQL.
- Look for input touching the SQL string:
+, f-strings,.format(),%. - Identifiers go through an allow-list; least privilege limits damage but is not the fix.