SQL Injection Prevention in Python — Core Concepts

How SQL injection happens

SQL injection occurs when an application constructs SQL queries by concatenating user input directly into the query string. The database can’t distinguish between the intended SQL structure and the attacker’s injected commands.

Consider a login form that builds a query like this:

# VULNERABLE — never do this
query = f"SELECT * FROM users WHERE username = '{username}' AND password = '{password}'"

If an attacker enters ' OR '1'='1 as the username, the query becomes:

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

The OR '1'='1' condition is always true, potentially returning all users and bypassing authentication entirely.

The scale of the problem

SQL injection has been the #1 web vulnerability for over two decades. It was behind the breach of Sony Pictures (2011, 77 million accounts), the Heartland Payment Systems breach (2008, 130 million credit cards), and countless smaller incidents. OWASP consistently ranks injection attacks as a top security risk.

The damage ranges from data theft (reading passwords, credit cards, personal information) to data destruction (dropping tables), to full server compromise (some databases allow executing operating system commands).

Parameterized queries: the primary defense

The definitive fix is parameterized queries (also called prepared statements). Instead of inserting values into the SQL string, you pass them as separate parameters:

# SAFE — parameterized query
cursor.execute(
    "SELECT * FROM users WHERE username = %s AND password = %s",
    (username, password)
)

The database driver sends the SQL structure and the data separately. The database parses the SQL structure first, then binds the data into the parameter slots. No matter what the attacker types — quotes, semicolons, SQL keywords — it’s treated as a literal string value, never as SQL commands.

Different Python database drivers use different placeholder styles:

• psycopg2 (PostgreSQL): %s or %(name)s
• sqlite3: ? or :name
• mysql-connector: %s or %(name)s
• pyodbc (SQL Server): ?

The syntax varies, but the principle is identical: data goes through parameters, never through string formatting.

ORM safety

Python ORMs like SQLAlchemy and Django ORM use parameterized queries internally:

# SQLAlchemy — safe, generates parameterized query
user = session.query(User).filter(User.username == username).first()

# Django ORM — also safe
user = User.objects.filter(username=username).first()

These automatically parameterize the username value. You’d have to deliberately use raw SQL or string concatenation to introduce a vulnerability.

Where developers still get it wrong

Raw SQL with f-strings. Even when using an ORM, developers sometimes drop to raw SQL for complex queries and use f-strings out of habit:

# VULNERABLE — even within Django/SQLAlchemy
User.objects.raw(f"SELECT * FROM users WHERE name = '{name}'")

The safe version:

User.objects.raw("SELECT * FROM users WHERE name = %s", [name])

Dynamic table or column names. Parameterized queries protect values, not identifiers (table names, column names). If you need dynamic identifiers, validate them against an allowlist:

ALLOWED_SORT_COLUMNS = {"name", "created_at", "email"}
if sort_column not in ALLOWED_SORT_COLUMNS:
    raise ValueError(f"Invalid sort column: {sort_column}")
query = f"SELECT * FROM users ORDER BY {sort_column}"

LIKE clauses. The % and _ wildcards in LIKE patterns are not handled by parameterization. If user input goes into a LIKE clause, they can craft patterns that return unexpected results or cause performance problems:

# Escape LIKE wildcards in user input
safe_pattern = user_input.replace("%", "\\%").replace("_", "\\_")
cursor.execute("SELECT * FROM products WHERE name LIKE %s", (f"%{safe_pattern}%",))

Common misconception

Some developers believe that escaping quotes (replacing ' with '') is a reliable defense. It’s not. Different databases, character encodings, and SQL dialects create edge cases where escaping fails. Parameterized queries don’t try to escape dangerous characters — they structurally separate code from data. This is a fundamentally stronger approach that doesn’t depend on getting escaping exactly right for every possible input.

Defense in depth

Parameterized queries are your primary defense, but layering additional protections makes your system more resilient:

1. Least privilege database accounts. Your web app’s database user shouldn’t have DROP TABLE or GRANT permissions.
2. Input validation. If a field should be a number, validate it as a number before it reaches any query.
3. Web Application Firewall (WAF). Catches common injection patterns at the network level.
4. Error message handling. Never expose raw SQL errors to users — they reveal database structure to attackers.

The one thing to remember: Always use parameterized queries — never string concatenation — for any SQL that includes user input, and remember that even ORM users can introduce injection through raw SQL or dynamic identifiers.