Course contentsShow
← Data Science
Lesson 1108 of 2,14528. Working with Databases from PythonFree lesson

Handling IN Clauses Safely

Special considerations for parameterizing IN clauses with variable-length lists of values in WHERE conditions.

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.