python - 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?

Answer 1

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)