SQL injection and parameterized queries
See how string-built queries let input change the query, and how placeholders prevent it.
On this page
SQL SELECT and Python's sqlite3 module.
- See how string-built queries let input change the query, and how placeholders prevent it.
- Predict the sample output and explain the state changes.
- Complete the practice task and check the boundary cases.
The concept
SQL injection happens when untrusted input is pasted into a SQL command so that the input changes the structure of the query. It has been among the most serious and common web vulnerabilities for decades, and is covered by the injection category in the OWASP Top 10. A successful attack can read, change, or delete data.
The vulnerable pattern is building SQL with string concatenation or formatting. With the input ' OR '1'='1 the query becomes WHERE name = '' OR '1'='1', whose condition is true for every row, so every user is returned. The cause is that the database cannot tell which part of the string was meant to be code and which was data.
The fix is a parameterized query. The SQL text keeps placeholders, and the values are sent separately, so the database always treats them as data. Use this for every value that comes from outside. Placeholders cannot be used for table or column names, so for those, choose from a fixed allow-list. Also run the application with a database account that has only the permissions it needs, so a mistake does less damage.
Code example
import sqlite3
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE users (name TEXT, secret TEXT)")
db.executemany("INSERT INTO users VALUES (?, ?)", [("ada", "s3cret-a"), ("bob", "s3cret-b")])
def unsafe(name):
return db.execute("SELECT name FROM users WHERE name = '" + name + "'").fetchall()
def safe(name):
return db.execute("SELECT name FROM users WHERE name = ?", (name,)).fetchall()
attack = "' OR '1'='1"
print(unsafe("ada"))
print(unsafe(attack))
print(safe(attack))[('ada',)]
[('ada',), ('bob',)]
[]Follow the code
Open the walkthrough to inspect the example one step at a time.
Show guided walkthrough
import sqlite3 db = sqlite3.connect(":memory:")db.execute("CREATE TABLE users (name TEXT, secret TEXT)")db.executemany("INSERT INTO users VALUES (?, ?)", [("ada", "s3cret-a"), ("bob", "s3cret-b")]) def unsafe(name): return db.execute("SELECT name FROM users WHERE name = '" + name + "'").fetchall() def safe(name): return db.execute("SELECT name FROM users WHERE name = ?", (name,)).fetchall() attack = "' OR '1'='1"print(unsafe("ada"))print(unsafe(attack))print(safe(attack))Follow the example
Start to inspect the source, values, and output. These prepared steps explain the published sample; grouped loop steps describe multiple iterations.
View the complete dry-run table
| Step / line | What happens | State after step | Output so far |
|---|---|---|---|
| The input is glued into the SQL text, so it can change the meaning of the query. | — | — | |
| With ordinary input the vulnerable query seems to work correctly. | — | [('ada',)] | |
| The input turns the condition into name = '' OR '1'='1', which is true for every row: all users are returned. | — | [('ada',)]
[('ada',), ('bob',)] | |
| The placeholder sends the value separately from the SQL, so it can only ever be data. | — | [('ada',)]
[('ada',), ('bob',)] | |
| The attack string is treated as a literal name that matches no user, so no rows come back. | — | [('ada',)]
[('ada',), ('bob',)]
[] |
Behind the scenes
How Python processes this code
Python runs this source through its interpreter. It creates objects for values and resolves names as each statement runs.
Cost and common mistakes
Parameterization costs nothing extra; the database can even reuse the parsed statement across calls.
- Escaping quotes by hand is error-prone. Use parameterized queries instead.
- Placeholders cannot be used for identifiers such as table or column names. Validate those against an allow-list.
- ORMs help, but raw SQL fragments inside an ORM can still be injectable. Check any place where strings are concatenated.
Try it yourself
Rewrite unsafe(name) to also filter on a numeric id passed by the caller.
Reveal answer and reasoning
Use two placeholders: db.execute("SELECT name FROM users WHERE name = ? AND rowid = ?", (name, user_id)). Both values are bound as data.