SQL Injection Prevention in Python

SQL injection is the number-one vulnerability in web applications, responsible for data breaches affecting millions of users annually. It occurs when untrusted input (like a username from a form) is concatenated directly into a SQL query, allowing an attacker to inject malicious SQL code. Parameterized queries (also called prepared statements) are the universal defense—they separate SQL code from data, making injection impossible.

Understanding SQL injection and how to prevent it is non-negotiable for professional Python developers. This tutorial shows you exactly what you're protecting against, the right way to write queries, and how different database adapters handle parameterization.

How SQL Injection Attacks Work

Here's the vulnerable pattern—never do this:

import sqlite3

username = input("Enter your username: ")
password = input("Enter your password: ")

conn = sqlite3.connect('app.db')
cursor = conn.cursor()

# VULNERABLE: String concatenation exposes you to injection
query = f'SELECT * FROM users WHERE username = "{username}" AND password = "{password}"'
cursor.execute(query)
user = cursor.fetchone()

if user:
    print(f"Welcome, {user[1]}!")
else:
    print("Invalid credentials")

An attacker enters " OR "1"="1 as the username. The query becomes:

SELECT * FROM users WHERE username = "" OR "1"="1" AND password = "..."

The condition "1"="1" is always true, so the attacker bypasses the password check and logs in as the first user. A more devastating attack:

username: "; DROP TABLE users; --

This creates a query:

SELECT * FROM users WHERE username = ""; DROP TABLE users; --" AND password = "..."

The attacker deletes the entire users table. The -- comment syntax makes the rest of the query harmless.

The Safe Solution: Parameterized Queries

Parameterized queries separate SQL structure from data. The database adapter substitutes data safely, guaranteeing no injection is possible. Here's the secure version:

import sqlite3

username = input("Enter your username: ")
password = input("Enter your password: ")

conn = sqlite3.connect('app.db')
cursor = conn.cursor()

# SAFE: Use ? placeholders for SQLite3
cursor.execute(
    'SELECT * FROM users WHERE username = ? AND password = ?',
    (username, password)
)
user = cursor.fetchone()

if user:
    print(f"Welcome, {user[1]}!")
else:
    print("Invalid credentials")

When the user enters " OR "1"="1, the database sees it as literal text, not SQL code. The query looks for a user with username exactly matching that string (which doesn't exist), and no injection occurs.

For PostgreSQL and psycopg, use %s placeholders instead:

import psycopg

username = input("Enter your username: ")
password = input("Enter your password: ")

conn = psycopg.connect('postgresql://user:pass@localhost/myapp')
cursor = conn.cursor()

# SAFE: Use %s placeholders for PostgreSQL
cursor.execute(
    'SELECT * FROM users WHERE username = %s AND password = %s',
    (username, password)
)
user = cursor.fetchone()

if user:
    print(f"Welcome, {user[1]}!")
else:
    print("Invalid credentials")

The key principle: Always pass user input as parameters, never concatenate it into the query string.

Placeholder Syntax Across Database Adapters

Different Python database adapters use different placeholder styles:

Adapter	Placeholder	Example
sqlite3	?	cursor.execute('SELECT * FROM users WHERE id = ?', (1,))
psycopg (PostgreSQL)	%s	cursor.execute('SELECT * FROM users WHERE id = %s', (1,))
MySQLdb / mysql-connector	%s	cursor.execute('SELECT * FROM users WHERE id = %s', (1,))
Oracle (cx_Oracle)	:1, :2, ... or :name	cursor.execute('SELECT * FROM users WHERE id = :1', (1,))

Mixing placeholders (e.g., using ? with PostgreSQL) causes ProgrammingError. Check your adapter's documentation.

Parameterizing All Query Types

INSERT, UPDATE, and DELETE queries are equally vulnerable to injection:

import sqlite3

# User input
name = input("Enter name: ")
email = input("Enter email: ")

conn = sqlite3.connect('app.db')
cursor = conn.cursor()

# SAFE: Parameterized INSERT
cursor.execute(
    'INSERT INTO users (name, email) VALUES (?, ?)',
    (name, email)
)

# SAFE: Parameterized UPDATE
new_email = input("New email: ")
cursor.execute(
    'UPDATE users SET email = ? WHERE name = ?',
    (new_email, name)
)

# SAFE: Parameterized DELETE
cursor.execute(
    'DELETE FROM users WHERE id = ?',
    (user_id,)
)

conn.commit()

Every value from outside your code (user input, API parameters, form fields) must be parameterized.

Common Mistakes and How to Avoid Them

Mistake 1: Parameterizing table or column names.

You cannot parameterize identifiers (table names, column names, keywords). If your code dynamically builds column lists, validate against a whitelist:

import sqlite3

# WRONG: This doesn't work
column = user_input  # Could be "email; DROP TABLE users; --"
cursor.execute(f'SELECT {column} FROM users')  # Still vulnerable!

# RIGHT: Validate against known columns
ALLOWED_COLUMNS = ['id', 'name', 'email', 'created_at']
if column not in ALLOWED_COLUMNS:
    raise ValueError(f"Invalid column: {column}")
cursor.execute(f'SELECT {column} FROM users')  # Safe because column is validated

Mistake 2: Forgetting to parameterize a single field.

# WRONG: One field is parameterized, one is concatenated
username = input("Username: ")
password = input("Password: ")
query = f'SELECT * FROM users WHERE username = "{username}" AND password = ?'
cursor.execute(query, (password,))  # Injection still possible via username

# RIGHT: All fields are parameterized
cursor.execute(
    'SELECT * FROM users WHERE username = ? AND password = ?',
    (username, password)
)

Mistake 3: Using string formatting for dynamic WHERE clauses.

# WRONG: Concatenating the operator
sort_order = input("ASC or DESC: ")
cursor.execute(f'SELECT * FROM users ORDER BY name {sort_order}')  # Injection via sort_order

# RIGHT: Validate against known values
if sort_order not in ('ASC', 'DESC'):
    raise ValueError("Invalid sort order")
cursor.execute(f'SELECT * FROM users ORDER BY name {sort_order}')

Using ORMs to Avoid SQL Injection

Higher-level ORMs (Object-Relational Mappers) like SQLAlchemy handle parameterization automatically, reducing the risk:

from sqlalchemy import create_engine, select
from sqlalchemy.orm import Session
from models import User  # Assume User model is defined

engine = create_engine('sqlite:///app.db')

username = input("Username: ")
password = input("Password: ")

with Session(engine) as session:
    # ORM automatically parameterizes; no raw SQL required
    user = session.query(User).filter(
        User.username == username,
        User.password == password
    ).first()
    
    if user:
        print(f"Welcome, {user.name}!")
    else:
        print("Invalid credentials")

SQLAlchemy generates safe SQL internally—you never write raw queries. However, you can still fall back to raw SQL when needed, and you should parameterize:

# Even with SQLAlchemy, parameterize raw SQL
from sqlalchemy import text

query = text('SELECT * FROM users WHERE username = :username AND password = :password')
user = session.execute(query, {'username': username, 'password': password}).first()

Why Prepared Statements Are Better Than String Escaping

Some developers mistakenly think escaping quotes prevents injection:

# WRONG: Escaping is not a reliable defense
username = user_input.replace('"', '""')
cursor.execute(f'SELECT * FROM users WHERE username = "{username}"')

This is fragile. Different databases use different escape sequences. Some character encodings allow bypassing escapes entirely. Never rely on escaping; always use parameterized queries.

Key Takeaways

• SQL injection is the #1 web vulnerability; parameterized queries are the only reliable defense.
• Always use placeholders (? for SQLite3, %s for PostgreSQL) and pass user input as parameters.
• Validate and whitelist dynamic SQL components (table names, operators) that cannot be parameterized.
• ORMs like SQLAlchemy parameterize automatically, reducing the attack surface.
• Escaping quotes is unreliable; parameterization is the universal solution.

Frequently Asked Questions

Can I parameterize a table name in a SELECT?

No, identifiers cannot be parameterized. Validate against a whitelist of allowed table names or use SQLAlchemy, which handles this safely. Example:

allowed_tables = {'users', 'products', 'orders'}
table = user_input
if table not in allowed_tables:
    raise ValueError(f"Invalid table: {table}")
cursor.execute(f'SELECT * FROM {table}')

Is it safe to parameterize strings but concatenate numbers?

Only if you've validated that the input is actually a number. Even then, use parameters:

# RISKY: Assuming age_str is numeric
age_str = user_input
try:
    age = int(age_str)  # Validates it's numeric
except ValueError:
    raise ValueError("Age must be a number")
cursor.execute('SELECT * FROM users WHERE age = ?', (age,))  # Still parameterize

# SAFER: Just parameterize everything
cursor.execute('SELECT * FROM users WHERE age = ?', (age_str,))  # Database coerces or rejects

Why does my ORM generate SQL with %s instead of ??

Different databases use different placeholder syntax. SQLAlchemy detects your database and uses the appropriate syntax. If you write raw SQL, follow your adapter's convention.

What if I need to build a dynamic query with a variable number of parameters?

Use SQL IN with a parameter list:

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

This is safe because the placeholders are generated by your code, and the user IDs are parameterized.

Does HTTPS or encryption protect against SQL injection?

No. HTTPS protects data in transit, but SQL injection happens after the data arrives at your server. Parameterization is the only defense.

Further Reading

• OWASP SQL Injection Prevention Cheat Sheet
• PEP 249 – Python DB-API 2.0 Parameterization
• SQLAlchemy Security Practices
• CWE-89: Improper Neutralization of Special Elements used in an SQL Command