Handling IN Clauses Safely
What you'll learn: How to safely parameterize SQL IN clauses when working with variable-length lists to prevent SQL injection vulnerabilities.
The Challenge with IN Clauses
You've learned to use parameterized queries to safely pass single values into your SQL statements. But what happens when you need to filter by a list of values using an IN clause? For example, you want to find all orders where customer_id is in [101, 205, 387], but that list changes at runtime.
Why String Formatting Fails Here
You might be tempted to do this:
ids = [101, 205, 387]
query = f"SELECT * FROM orders WHERE customer_id IN ({','.join(map(str, ids))})"
Don't! This is still vulnerable to SQL injection if the list contains user input.
The Safe Solution
The proper approach is to create one placeholder for each value in your list:
import sqlite3
conn = sqlite3.connect('database.db')
cursor = conn.cursor()
customer_ids = [101, 205, 387]
# Create placeholders: "?, ?, ?"
placeholders = ', '.join(['?'] * len(customer_ids))
query = f"SELECT * FROM orders WHERE customer_id IN ({placeholders})"
# Execute with unpacked list
cursor.execute(query, customer_ids)
results = cursor.fetchall()
Think of it like ordering pizza for a group: instead of writing one note listing everyone's toppings (risky formatting), you hand the chef separate, validated order cards—one per person.
For named parameters in databases like PostgreSQL:
placeholders = ', '.join([f':id{i}' for i in range(len(customer_ids))])
params = {f'id{i}': val for i, val in enumerate(customer_ids)}
Key Takeaway: Always dynamically generate the correct number of parameter placeholders for IN clauses based on your list length, then pass the list as parameters—never concatenate values directly into the SQL string.