SQL Parameterized Queries (Preventing SQL Injections)
Never use f-strings or string concatenation (`+`) to build SQL queries. Always pass user inputs as tuples using `?` placeholders so SQLite handles escaping securely 🛡️.
import sqlite3
conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE users (id INT, username TEXT)")
# Attacker input payload:
user_input = "admin' OR '1'='1"
# SECURE parameterized query with ? placeholder:
cursor = conn.execute("SELECT * FROM users WHERE username = ?", (user_input,))
print(cursor.fetchall()) # [] (Safely treated as literal string!) 🛡️
# 17. SQL Parameterized Queries (Preventing SQL Injections) Never use f-strings or string concatenation (`+`) to build SQL queries. Always pass user inputs as tuples using `?` placeholders so SQLite handles escaping securely 🛡️. ```python import sqlite3 conn = sqlite3.connect(":memory:") conn.execute("CREATE TABLE users (id INT, username TEXT)") # Attacker input payload: user_input = "admin' OR '1'='1" # SECURE parameterized query with ? placeholder: cursor = conn.execute("SELECT * FROM users WHERE username = ?", (user_input,)) print(cursor.fetchall()) # [] (Safely treated as literal string!) 🛡️ ``` F-string SQL queries (`f"SELECT * FROM users WHERE name='{user_input}'"`) are the #1 security disaster in software engineering. Always use parameterized `?` bindings! 🛡️ --- ### Question Why does passing arguments via `cursor.execute("SELECT * WHERE u = ?", (user_input,))` prevent SQL injection? 🧠 - [ ] a. SQLite treats the parameter strictly as literal data, preventing attacker input from altering query syntax 🛡️ - [ ] b. It converts all input strings to lowercase - [ ] c. It runs a regex sanitizer on the string - [ ] d. It encrypts user input with AES-256 --- ### Challenge Connect to `:memory:`. Create table `logs (msg TEXT)`. Insert message using parameterized query `cursor.execute("INSERT INTO logs VALUES (?)", (payload,))` where `payload = "Safe message"`. Commit, fetch and print. 🎯 <details> <summary>Solution</summary> ```python import sqlite3 conn = sqlite3.connect(":memory:") cur = conn.cursor() cur.execute("CREATE TABLE logs (msg TEXT)") payload = "Safe message" cur.execute("INSERT INTO logs VALUES (?)", (payload,)) conn.commit() cur.execute("SELECT msg FROM logs") print(cur.fetchone()[0]) conn.close() ``` </details>