In this lesson, you will learn how to neutralize SQL injection attacks by using parameterized queries to safely handle user-supplied data in your database interactions.
The Problem: String Interpolation
When building database-backed APIs, it is tempting to build SQL strings using Python’s f-strings or .format(). For example, consider this unsafe pattern:
# DANGEROUS: Never do this!
user_input = "admin' --"
query = f"SELECT * FROM users WHERE username = '{user_input}'"
cursor.execute(query)
By injecting ' --, an attacker can prematurely terminate the SQL statement and comment out the rest, potentially bypassing authentication or leaking data. This is SQL Injection.
The Solution: Parameterized Queries
Parameterized queries (also called prepared statements) decouple the SQL command from the data. Instead of placing the user input directly into the query string, you use a placeholder (like ? or %s, depending on your driver) and pass the data as a separate tuple to the execute() method.
The database driver treats these values strictly as data, never as executable code, ensuring that even if a user provides malicious SQL commands, they are stored or processed merely as literal text.
import sqlite3
# SAFE: Using placeholders
username = "admin' --"
query = "SELECT * FROM users WHERE username = ?"
# The driver handles escaping and separation safely
cursor.execute(query, (username,))
Why This Works
When you provide the parameters separately, the database engine compiles the SQL command structure first. Because the command structure is already finalized, the data provided later—regardless of its content—cannot alter the logic of the query. The malicious ' -- sequence is treated simply as the literal name of a user, which will correctly fail to match anything in your database instead of altering the query's behavior.
Always treat external data as untrusted input. By utilizing the library's built-in parameterization features, you effectively close the primary vector for SQL injection in your API.
Key Takeaway: Never construct SQL queries using string formatting; always use parameterized placeholders to ensure your input is treated as data rather than executable code.