Python Practice › SQLite Programs › Program 19
Why parameters prevent SQL injection
Write a Python program that shows how an f-string query is unsafe and a parameter is safe.
Output
unsafe query returned: [('asha',), ('ravi',)]
safe query returned: []Approach
- Use a malicious input with both styles.
Solution
import sqlite3 conn = sqlite3.connect(":memory:")conn.execute("CREATE TABLE users (name TEXT, secret TEXT)")conn.executemany("INSERT INTO users VALUES (?, ?)", [("asha", "s1"), ("ravi", "s2")]) user_input = "nobody' OR '1'='1"unsafe = conn.execute(f"SELECT name FROM users WHERE name = '{user_input}'").fetchall()safe = conn.execute("SELECT name FROM users WHERE name = ?", (user_input,)).fetchall()print("unsafe query returned:", unsafe)print("safe query returned:", safe)Output
unsafe query returned: [('asha',), ('ravi',)]
safe query returned: []Line by line
Line 7
The input closes the quote and adds OR '1'='1, so the unsafe query returns every user.
Line 8
With a parameter the whole input is treated as a name, which matches nobody.
Learn the concept
New to this topic? Read the lesson, then come back.