Python: best practice and securest way to connect to MySQL and execute queries
mysql, python, sql-injection
Solution
To avoid injections, use `execute` with `%s` in place of each variable, then pass the value via a list or tuple as the second parameter of `execute`. Here is an example from the documentation:
c=db.cursor()
max_price=5
c.execute("""SELECT spam, eggs, sausage FROM breakfast
WHERE price < %s""", (max_price,))
Note that this is using a comma, not % (which would be a direct string substitution, not escaped). Don't do this:
c.execute("""SELECT spam, eggs, sausage FROM breakfast
WHERE price < %s""" % (max_price,))
In addition, you must not use single quotes around the position holder (`'%s'`) if the parameter is a string as the driver provides these.
Problem
What is the safest way to run queries on MySQL? I am aware of the dangers involved with MySQL and SQL injection. However, I do not know how I should run my queries to prevent injection on the variables to which other users (webclients) can manipulate. I used to write my own escape function, but apparently this is "not-done". What should I use and how should I use it to query and do inserts safely on a MySQL database through python without risking MySQL injection?