Prevent SQL Injection in Python Database Queries

SQL injection is a critical security risk that occurs when untrusted user input is directly concatenated into database queries, allowing attackers to manipulate query logic. In Python, this vulnerability can be effectively eliminated by utilizing parameterized queries, adopting Object-Relational Mapping (ORM) frameworks, safely handling dynamic identifiers, and enforcing the principle of least privilege. This article outlines the essential, practical techniques required to secure Python applications against SQL injection when executing database commands.

1. Use Parameterized Queries (Prepared Statements)

The most effective defense against SQL injection is the use of parameterized queries, also known as prepared statements. Instead of embedding user input directly into the SQL string, placeholders are used, and the database driver handles escaping and type-casting separately.

Vulnerable Approach (String Formatting):

# NEVER DO THIS: vulnerable to SQL injection
user_input = "admin' OR '1'='1"
query = f"SELECT * FROM users WHERE username = '{user_input}'"
cursor.execute(query)

Secure Approach (Parameterized Query):

# SECURE: Input is passed as a tuple/sequence
user_input = "admin' OR '1'='1"
query = "SELECT * FROM users WHERE username = %s"  # or '?' depending on the driver
cursor.execute(query, (user_input,))

Depending on the database driver (such as sqlite3, psycopg2 for PostgreSQL, or mysql-connector-python), the placeholder syntax may vary (e.g., ?, %s, or :param), but the underlying security mechanism remains identical: the database treats the input purely as data, never as executable code.

2. Implement Object-Relational Mappers (ORMs)

Using a reputable ORM like SQLAlchemy, Django ORM, or Peewee abstracts raw SQL query construction. ORMs automatically use parameterized queries under the hood when querying through their high-level APIs.

Example with SQLAlchemy:

# SECURE: Automatically parameterized by SQLAlchemy
user = session.query(User).filter(User.username == user_input).first()

When using an ORM, avoid falling back to raw SQL execution wrappers (such as text() in SQLAlchemy) unless strictly necessary, and always parameterize them if they must be used.

3. Safely Handle Dynamic Identifiers with Whitelisting

Parameterized queries only work for values (literals), not for identifiers such as table names, column names, or sort orders (ASC/DESC). If an application requires dynamic table or column selection based on user input, parameterization will raise a syntax error.

To secure dynamic identifiers, validate the input against a hardcoded whitelist of allowed values:

ALLOWED_COLUMNS = {"username", "email", "created_at"}
user_column = get_user_requested_column()

if user_column not in ALLOWED_COLUMNS:
    raise ValueError("Invalid column selection.")

# Safe to interpolate only after strict validation against the whitelist
query = f"SELECT {user_column} FROM users WHERE id = %s"
cursor.execute(query, (user_id,))

For advanced use cases in PostgreSQL, libraries like psycopg2.sql provide specialized composing utilities designed specifically for identifiers:

from psycopg2 import sql

query = sql.SQL("SELECT * FROM {} WHERE id = %s").format(sql.Identifier(user_table))
cursor.execute(query, (user_id,))

4. Apply the Principle of Least Privilege

Database security should include defense-in-depth measures. The database user account configured within your Python application's connection string should only have the minimum privileges necessary to perform its functions:

Limiting permissions ensures that even if an injection flaw occurs, the blast radius of potential exploits is significantly reduced.