Prevent SQL injection

Your application is one bad SQL string away from a full database takeover. SQL injection (SQLi) remains one of the most critical web vulnerabilities — the OWASP Top 10 has ranked it among the top risks for years — and it happens when you trust user input and glue it straight into a SQL query. The classic ' OR '1'='1 trick can bypass authentication, and '; DROP TABLE users; -- could destroy your data. The cure? Parameterized queries — a simple, robust way to separate SQL logic from data so user input can never become executable code. This lesson shows you exactly how to prevent SQL injection with parameterized queries in Python, step by step.

The problem this lesson solves

Imagine you write this code to fetch a user by name:

username = request.form["username"]
query = f"SELECT * FROM users WHERE username = '{username}'"
cursor.execute(query)

It looks innocent — until someone types ' OR '1'='1 as the username. The query becomes:

SELECT * FROM users WHERE username = '' OR '1'='1'

That condition is always true, so the query returns every row in the table. In a login form, the attacker is now logged in as the first user — often the admin. Worse, some database drivers let you run multiple statements, so an attacker could append ; DROP TABLE users; -- and wipe your table.

The root cause isn't the database driver or the SQL language — it's the developer. Concatenating strings to build SQL treats data as code. SQL injection is not a bug in your database; it's a bug in how you build your queries. Parameterized queries fix that at the deepest level: they make it structurally impossible for user input to be interpreted as SQL syntax.

Core concept / mental model

Think of a SQL query as a cake recipe. The recipe has fixed instructions — “mix flour and eggs” — and variable ingredients — “add 2 cups of sugar.” If you let a customer directly edit the recipe sheet, they can write “add poison” instead of “add sugar.” Parameterized queries are like giving the baker a fixed recipe card and placing the sugar in a labeled jar: the baker knows exactly where to pour it, and the customer never touches the instructions.

Formally, a parameterized query (also called a prepared statement) has two parts:

• Query template: the SQL with placeholders (? in sqlite3, %s in psycopg2) that are never filled by string concatenation.
• Separate parameters: the data values passed to the database driver, which handles escaping, quoting, and type conversion.

The database parses the query template once and substitutes the parameters as literal values. Since the data is never part of the SQL syntax, malicious content like ' OR '1'='1 becomes just a string value, not logic.

Key definition: SQL injection is when an attacker controls part of the SQL statement syntax. Parameterized queries are a defense that ensures user input is only ever treated as data.

How it works step by step

You can prevent SQL injection with parameterized queries in four logical steps. This is the same pattern across every Python database driver, so once you learn it, you can apply it everywhere.

1. Identify every place you build SQL from user input — form fields, query strings, headers, cookies, even JSON payloads. Any value that came from outside your trust boundary is suspect.
2. Replace all dynamic values with placeholders in your SQL string. Use your driver's placeholder syntax (e.g., ? for sqlite3, %s for psycopg2, :name for named parameters).
3. Pass the user input as a separate argument to the driver's execute() method. The driver automatically escapes and quotes the value.
4. Never, ever string-concatenate user input into the SQL template. This includes f-strings, % formatting, and + concatenation.

The cause-and-effect chain is: string concatenation → user input becomes syntax → injection. Parameterization breaks that chain: data stays data, every time.

Hands-on walkthrough

Let's prevent SQL injection with parameterized queries using Python's built-in sqlite3 module. First, set up a small database:

import sqlite3

conn = sqlite3.connect(":memory:")
cur = conn.cursor()
cur.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, username TEXT, email TEXT)")
cur.executemany("INSERT INTO users (username, email) VALUES (?, ?)", [
    ("alice", "alice@example.com"),
    ("bob", "bob@example.com"),
])
conn.commit()

Now write a function that safely looks up a user by name using placeholders:

def get_user(username):
    cur.execute("SELECT * FROM users WHERE username = ?", (username,))
    return cur.fetchone()

# Safe: the ? placeholder makes user input a literal value
print(get_user("alice"))
print(get_user("' OR '1'='1"))  # Returns None, not every user!

Output:

(1, 'alice', 'alice@example.com')
None

Notice the second call safely returns None — the SQL injection payload is treated as a harmless string that matches no username. The same technique works with psycopg2 for PostgreSQL:

import psycopg2

conn = psycopg2.connect("dbname=test user=postgres")
cur = conn.cursor()

# psycopg2 uses %s placeholders
cur.execute("SELECT * FROM users WHERE username = %s", (username,))
rows = cur.fetchall()

Pro tip: Never concatenate values into a query string — even “trusted” ones you fetched from your own database. Treat all dynamic values as untrusted until proven otherwise.

One more example: inserting a new user safely with a parameterized INSERT:

def add_user(username, email):
    cur.execute("INSERT INTO users (username, email) VALUES (?, ?)", (username, email))
    conn.commit()

add_user("charlie", "charlie@example.com")
print(get_user("charlie"))

Output:

(3, 'charlie', 'charlie@example.com')

Compare options / when to choose what

Parameterized queries are not your only defense, but they are the strongest and simplest. Here's how they compare to other approaches:

When to choose what: Always prefer parameterized queries as your primary defense. Use ORMs when you need rapid development — they hide parameterization, but still enforce it if you avoid raw SQL. Add input validation as a second layer, not a replacement. Avoid manual escaping at all costs; you will miss an edge case.

Troubleshooting & edge cases

“I used placeholders but my query doesn't work.” Check your placeholder syntax. sqlite3 uses ?, psycopg2 uses %s. If you mix them, you get errors like TypeError: not all arguments converted during string formatting. Also, with psycopg2, do not use % formatting on the query string before passing it — execute() does it for you.

“My query with LIKE fails.” Using ? with LIKE is safe, but if you try to concatenate % wildcards into the parameter, it becomes unsafe. Instead, pass the wildcards as part of the parameter value:

cur.execute("SELECT * FROM users WHERE username LIKE ?", (f"%{search_term}%",))

“I need to build IN clauses dynamically.” You can't bind a list directly — you must generate placeholders dynamically, but still pass the list as separate parameters:

ids = [1, 2, 3]
placeholders = ",".join("?" for _ in ids)
cur.execute(f"SELECT * FROM users WHERE id IN ({placeholders})", ids)

This is safe because the number of placeholders is fixed by your code, not by user input.

“My code uses stored procedures — am I safe?” Only if you call them with parameters. Concatenating input into a CALL statement is still injectable.

Edge case: identifiers (table/column names) can't be parameterized. If you need to let users pick a column name, you must validate against a whitelist, not bind it as a parameter.

What you learned & what's next

You now know why SQL injection happens, how parameterized queries defeat it by keeping data and code separate, and how to apply them in sqlite3 and psycopg2. You can convert any raw query into a safe, parameterized version, and you know it's the primary defense against SQLi in any Python stack.

You also saw that ORMs and escaping are useful supplements, but parameterization is non-negotiable — don't ship code that concatenates user input into SQL.

Next in the Secure development track, you'll build on this by learning how to validate and sanitize inputs at the boundaries — because parameterization is a huge win, but defense-in-depth means you also validate before data ever reaches your queries. Stay sharp and keep the data separate from the code.

Now get hands-on: take one of your existing scripts that builds SQL with f-strings and convert it to use parameterized queries. Run it against an intentionally malicious input and confirm it's safe. You'll never go back to string concatenation.