pyodbc execute SQL code

Question

I am trying to use pyodbc cursor execute the right way to prevent injection attacks, as suggested here: what does ? mean in python pyodbc module

My code is as follows:

query = """\
SELECT 
    ?,count(*)
FROM 
    ?
WHERE 
    ?=?
""", ('date', 'myTable', 'date', '2017-05-08')
cursor.execute(query)

And I get an error:

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

For the right answer I'd want to:

Keep the question mark format to avoid SQL injection attacks
Keep the triple quotes format so I can write long SQL queries and not loose code readability.

Is there a way to achieve this? I know I could use """ %s """ %('table') format type but that defeats the purpose of this question.

Ok, your edit came at the same time as my comment. Where are you getting myTable and date strings from? It's unlikely that these would be coming from freetext anywhere in your setup? A value for the date might be entered by a user, but would they specify that it was going to be written into the date column of your table? In other words, could you be vulnerable to SQL injection from formatting the table name and the column names? — roganjosh
– roganjosh, Commented May 8, 2017 at 19:15

DeepSpace · Accepted Answer · 2017-05-09 07:56:41Z

3

You have 2 issues:

query is a tuple. The way to execute a parameterized query is as follows:

query = """SELECT ?,count(*)
           FROM ?
           WHERE ?=? """
args = ('date', 'myTable', 'date', '2017-05-08')
cursor.execute(query, args)

You could pass query with *. This would expand query to a string and a tuple which is what execute expects:

cursor.execute(*query)  # 'query' here is defined as it is in your example

But, that won't work. You can not use parameterized query to use parameters in the select and from clauses. You can also not use parameters for the column name in the where clause.

You (usually) don't have to worry about SQL injection if the value isn't inputted by the user (or if the user can't change it in anyway).

answered May 8, 2017 at 19:20

DeepSpace

82.1k12 gold badges119 silver badges166 bronze badges

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

1 Answer 1