How to use SQL parameters with IN clause for a variable number of values with pyodbc?

Question

I have a list of values that I'd like to use in an IN clause for an SQL (SQL Server) statement to be executed with pyodbc. Example:

files = ['file1', 'file2', ...]  # this list can have a variable number of elements
con = pyodbc.connect(...)

# What I'd like to do
result = con.cursor().execute('SELECT * FROM sometable WHERE file_name IN (?)', files)

However when I execute the statement above I get an error such as this:

ProgrammingError: ('The SQL contains 1 parameter markers, but 18 parameters were supplied', 'HY000')

I can generate a variable parameter string using something like:

params = ','.join(['?']*len(files))
query = 'SELECT * FROM sometable WHERE file_name IN ({})'.format(params)
result = con.cursor().execute(query, files)

But doing so would put me at risk for SQL injection, if I understand correctly. Is there a way to accomplish this safely?

There is no SQL injection risk with your attempt. You are only building a prepared statement with dynamic parameter placeholders. But you are binding values in execute. — Parfait
– Parfait, Commented Aug 23, 2019 at 2:02

David Browne - Microsoft · Accepted Answer · 2019-08-22 22:03:38Z

4

You can use JSON to pass the list to SQL Server. EG

import numpy as np
import pandas as pd
import pyodbc
import json 

files = ['file1', 'file2', 'file3']  # this list can have a variable number of elements
json_files = json.dumps(files)
print(json_files)
conn = pyodbc.connect('Driver={Sql Server};'
                      'Server=localhost;'
                      'Database=tempdb;'
                      'Trusted_Connection=yes;')

cursor = conn.cursor()

cursor.execute("create table #sometable(id int, file_name varchar(255)); insert into #sometable(id,file_name) values (1,'file2')")
# What I'd like to do
result = cursor.execute('SELECT * FROM #sometable WHERE file_name IN (select value from openjson(?))', json_files)
rows = cursor.fetchall()
print(rows)

answered Aug 22, 2019 at 22:03

David Browne - Microsoft

90.8k7 gold badges53 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Gord Thompson Over a year ago

Neat trick if you've got a database with compatibility level 130 or higher.

David Browne - Microsoft Over a year ago

Yep. The Python data access libraries are not the greatest, SQL Server JSON features fill a lot of gaps like Bulk Insert, Table Valued Parameters, shaped data retrieval, and object mapping.

Gord Thompson · Accepted Answer · 2019-08-22 22:43:21Z

2

doing so would put me at risk for SQL injection

No, it wouldn't, because you are in complete control over what you are injecting into the SQL command text (i.e., a comma-separated string of question marks). Your approach is fine, provided that your list doesn't contain more than approximately 2100 items.

answered Aug 22, 2019 at 22:43

Gord Thompson

125k39 gold badges252 silver badges458 bronze badges

1 Comment

x1084 Over a year ago

Ah, I see. these links appear to suggest not using Python string formatting in general, but perhaps it isn't a hard-and-fast rule.

Collectives™ on Stack Overflow

How to use SQL parameters with IN clause for a variable number of values with pyodbc?

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related