How Can I Safely Use WHERE IN Statements to Prevent SQL Injection?

Handling IN Queries Safely: Understanding WHERE IN Statements

When working with database queries, retrieving specific records based on a set of values is a common requirement. The WHERE IN statement provides an efficient way to achieve this.

Database Schema and Intent

Consider the following database table:

CREATE TABLE IF NOT EXISTS tab (_id integer PRIMARY KEY AUTOINCREMENT, obj text NOT NULL);

You intend to retrieve records where the obj column value matches a list of variables, which could be vulnerable to SQL injection if not handled properly.

First Approach: Manual Method

Attempting to construct the query manually using list_of_vars and join() to generate the placeholder string may throw an error due to a mismatch in the number of bindings.

statement = "SELECT * FROM tab WHERE obj IN (?);"
c.execute(statement, "'"+"','".join(list_of_vars)+"'")

Recommended Approach: Parameterized Query

To safely execute an IN query, use parameter placeholders (?) and bind the list of variables as parameters. Create the statement using the format method to generate the appropriate number of placeholders:

statement = "SELECT * FROM tab WHERE obj IN ({0})".format(', '.join(['?'] * len(list_of_vars)))
c.execute(statement, list_of_vars)

By passing list_of_vars as the parameter values list, you ensure proper binding and prevent SQL injection vulnerabilities.

The above is the detailed content of How Can I Safely Use WHERE IN Statements to Prevent SQL Injection?. For more information, please follow other related articles on the PHP Chinese website!