Parameterize Python SQL queries without string interpolation

Pass data values separately from SQL text through the database driver's parameter interface. This lets the driver treat a customer name or date as a value, even if its text contains quotes or resembles SQL syntax. Building a query by inserting raw values into an f-string mixes the query structure with its data.

Parameter syntax varies by driver. This tutorial uses Python's standard-library sqlite3 module and its question-mark placeholders; do not assume that every PostgreSQL, MySQL or warehouse driver uses the same notation.

Run a complete local example

Save and run this Python script. It creates only an in-memory database:

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE customers(customer_id TEXT PRIMARY KEY, name TEXT)")
db.executemany("INSERT INTO customers VALUES (?, ?)", [
    ("C1", "O'Brien"), ("C2", "Asha")
])

query = "SELECT customer_id FROM customers WHERE name = ?"
assert db.execute(query, ("O'Brien",)).fetchall() == [("C1",)]
assert db.execute(query, ("' OR 1=1 --",)).fetchall() == []
print("Both parameter-binding checks passed")
db.close()

The apostrophe in O'Brien is ordinary data. The second test supplies SQL-looking text, but it does not alter the query's logic or return every customer. It searches for a literal name that is absent.

The trailing comma in ("O'Brien",) creates a one-element tuple. Without it, the parentheses merely surround a string, which is not the intended one-value parameter sequence. Python's sqlite3 documentation describes supported placeholder styles and parameter binding.

Apply the same rule to analytical extracts

In the advanced SQL lab, operational_checks.py executes a March extract using two bound date values and a customer lookup using a bound customer ID. It verifies both normal input and an SQL-looking literal. Expected March purchase IDs are P06, P07 and P08.

Validate the meaning of inputs as well. Binding a date-shaped string prevents it from becoming SQL structure; it does not prove the string is a valid date, that the lower bound precedes the upper bound or that the caller may access the requested customer.

Use explicit authorization filters and input contracts in applications. Parameterization is one necessary query-construction control, not a replacement for access checks or sensible query limits.

Handle a variable number of values

An IN filter requires one placeholder for each value. Construct only the placeholder punctuation dynamically, keeping the actual values separate:

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE purchases(purchase_id TEXT, customer_id TEXT)")
db.executemany("INSERT INTO purchases VALUES (?, ?)", [
    ("P1", "A"), ("P2", "B"), ("P3", "C")
])

def purchases_for(customer_ids):
    if not customer_ids:
        return []
    placeholders = ",".join("?" for _ in customer_ids)
    sql = f"SELECT purchase_id FROM purchases WHERE customer_id IN ({placeholders}) ORDER BY purchase_id"
    return db.execute(sql, tuple(customer_ids)).fetchall()

assert purchases_for(["A", "C"]) == [("P1",), ("P3",)]
assert purchases_for([]) == []
assert purchases_for(["A' OR 1=1 --"]) == []
print("Variable-length filter checks passed")
db.close()

The f-string includes only generated question marks and commas. It never includes customer values. The empty-list policy is explicit: return no purchases. For very large lists, consider a staging table or an engine-specific bulk method rather than an unbounded placeholder list.

Treat identifiers differently from values

A parameter placeholder usually cannot stand in for a table name, column name or ASC/DESC keyword. If a user chooses a sort field, map a small set of accepted UI choices to fixed, developer-controlled SQL fragments. Reject unknown choices; do not interpolate arbitrary identifier text.

Keep the mapping near the query and test every supported choice. If the driver provides identifier-composition helpers, use them according to its documentation. Escaping rules for identifiers and values are different.

Make the test meaningful

Check that ordinary values, quoted names and SQL-looking strings all produce the expected rows. Verify empty lists and invalid sort choices. Record result keys, not just “query did not crash,” because a query can execute successfully while returning too much data.

Exercise: add a name containing a percent sign. Compare equality with a deliberately chosen LIKE search, explaining that parameter binding does not remove LIKE wildcard semantics. Define whether your application wants literal matching or pattern matching.

NeuraPath's Data Analytics with Generative AI course joins Python and SQL in practical analysis. A reproducible extraction project should keep query structure, data values and authorization rules separately understandable.

Continue learning

This article is part of the Advanced SQL and analytical patterns sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in Why a function on a filtered column can slow SQL.
• Continue with Design an incremental SQL extract with late-arriving records.