CODEWITHSUNDEEP
pythonsql-serverstored-procedurespyodbc
Nitesh Joshi
Nitesh Joshi

Reputation: 113

Unable to execute parameterized SQL using Python and SQL Server

I am trying to run a stored procedure with numerous parameters to it. To do that I am using pyodbc library and its cursor.execute function.

Following is the query I am trying to execute:

executionStr = "EXEC ? @ImportJSON= ?, ?=?, ?=?, ?=?",procName, data, param1, param1Value, param2, param2Value, param3, param3Value

When I run the query it gives me an error

TypeError: The first argument to execute must be a string or unicode query.

When I try and run the query as following it executes successfully and I can see data going in.

executionStr = "EXEC "+procName+" @ImportJSON='"+data+"', "+param1+"='"+param1Value+"' , "+param2+"='"+param2Value+"' , "+param3+"='"+param3Value+"';"

I am trying to run the query in the previous format so that I protect my code from SQL Injection.

I also tried to run the query using parameters as follows but gives the same error:

preexe = "EXEC ? @ImportJSON= ?, "+param1+"=?, "+param2+"=?, "+param3+"=?"
procedure_cursor.execute(preexe,(procName, data, param1Value, param2Value, param3Value))

I am not sure what I am doing wrong or where I am going wrong. Any help will be greatly appreciated.

Thank you in advance.

Upvotes: 1

Views: 219

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123849

Your last attempt was on the right track, but parameters can only be used to pass data values (e.g., strings, numbers, dates, etc.), not database object names (e.g., table names, column names, stored procedure names, etc.). Therefore, you'll need to do something like this:

preexe = f"EXEC {procName} @ImportJSON= ?, {param1}=?, {param2}=?, {param3}=?"
procedure_cursor.execute(preexe, (data, param1Value, param2Value, param3Value))

This does not pose a significant risk of SQL Injection provided that your code has complete control over the values of procName, param1, param2, and param3.

Upvotes: 1

Related Questions