CODEWITHSUNDEEP
pythonmysqlsqldjangoparameterized-query
Brian
Brian

Reputation: 11

Trying to sanitize very basic MySQL SELECT in Python/Django

This is driving my nuts. I have this very basic query that works without the sanitization statement but breaks with it. Everything I've read says to do it this way. 

query = "SELECT COL1 FROM A_TABLE %s"
queryUserInput = "WHERE COL1 = 'user input value'"
cursor.execute(query, (queryUserInput,))

I receive the error:

1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' WHERE ...

Top of the stack: C:..\env\lib\site-packages\mysql\connector\connection_cext.py in cmd_query

raw_as_string=raw_as_string)

Python 3.7 Django 2.1.3

Any advice is very appreciated.

Upvotes: 1

Views: 973

Answers (2)

ADyson
ADyson

Reputation: 61859

Parameterisation is supposed to used to santitise a variable value which is being used to form part of the finished SQL - e.g. the 7 in a query such as SELECT * FROM user WHERE ID = 7. You can't parameterise a whole section of SQL the way you're doing - and indeed there should be no need to do so.

I don't know Python specifically, but logically I think your code should be:

query = "SELECT COL1 FROM A_TABLE WHERE COL1 = %s"
queryUserInput = "user input value"
cursor.execute(query, (queryUserInput,))

in order that it only parameterises the actual user input, and not any of the SQL.

(Your original code would produce a SQL string something like SELECT COL1 FROM A_TABLE 'WHERE COL1 = \'user input value\'' which clearly is not valid SQL at all, due to everything after A_TABLE being contained within a string literal.)

This documentation (of the execute() method) shows you some other examples of correct usage: https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-execute.html

Upvotes: 2

blacker
blacker

Reputation: 798

you can try it:

queryUserInput = "user input value"
cursor.execute("SELECT COL1 FROM A_TABLE WHERE COL1 = %s", [queryUserInput])

and django doc:

Executing custom SQL directly

Upvotes: 1

Related Questions