Master Safe SQL Queries in Python: Parameterized cursor.execute Techniques

Parameterized cursor.execute() Queries (Recommended)

This is the safest and most common method, suitable for libraries such as sqlite3, psycopg2 (PostgreSQL), pymysql, mysql-connector-python, etc.

Example using SQLite:

import sqlite3
# Connect to database
conn = sqlite3.connect('example.db')
cursor = conn.cursor()
# Create table (example only)
cursor.execute("CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)")
# Define SQL template and parameters
sql = "INSERT INTO users (name, age) VALUES (?, ?)"
params = ("Alice", 25)
# Execute SQL
cursor.execute(sql, params)
conn.commit()

Explanation: ? is SQLite's placeholder. This approach automatically escapes parameters and prevents SQL injection.

Using Named Placeholders (For Complex SQL)

Applicable to drivers that support named parameters, such as PostgreSQL's psycopg2 or certain MySQL drivers.

Example with psycopg2:

import psycopg2
conn = psycopg2.connect(database="testdb", user="postgres", password="pass")
cursor = conn.cursor()
sql = "INSERT INTO users (name, age) VALUES (%(name)s, %(age)s)"
params = {"name": "Bob", "age": 30}
cursor.execute(sql, params)
conn.commit()

❌ Not recommended: building SQL strings via string formatting, which can lead to injection.

name = input("Enter your name: ")
sql = f"SELECT * FROM users WHERE name = '{name}'"
cursor.execute(sql)

If a user enters something like ' OR '1'='1, it may cause an SQL injection attack.

Using SQLAlchemy (ORM‑style SQL Construction)

If you use an ORM like SQLAlchemy, you can also bind parameters safely.

from sqlalchemy import create_engine, text
engine = create_engine("sqlite:///example.db")
with engine.connect() as conn:
    sql = text("SELECT * FROM users WHERE age > :age")
    result = conn.execute(sql, {"age": 18})
    for row in result:
        print(row)

Custom SQL Template + Parameter Substitution (For Logging or Debugging)

When you need the final SQL string for logging while keeping parameters safe, you can use cursor.mogrify() (psycopg2) or manually construct it.

import psycopg2
conn = psycopg2.connect(database="testdb", user="postgres", password="pass")
cursor = conn.cursor()
sql = "SELECT * FROM users WHERE name = %(name)s AND age = %(age)s"
params = {"name": "Charlie", "age": 28}
final_sql = cursor.mogrify(sql, params)
print(final_sql.decode())  # Outputs the actual SQL to be executed

Summary

Use parameterized queries—either positional or named placeholders—to let the driver handle escaping and avoid SQL injection. ORM tools like SQLAlchemy also support safe parameter binding, and utilities like mogrify help generate the final SQL for logging.