Python: best practice and securest way to connect to MySQL and execute queries

Question

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?

Answer 1

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.

Answer 2

As an expansion of Bruno's answer, your MySQL client library may support any of several different formats for specifying named parameters. From PEP 249 (DB-API), you could write your queries like:

'qmark'

>>> cursor.execute("SELECT spam FROM eggs WHERE lumberjack = ?", (lumberjack,)) 

'numeric'

>>> cursor.execute("SELECT spam FROM eggs WHERE lumberjack = :1", (lumberjack,)) 

'named'

>>> cursor.execute("SELECT spam FROM eggs WHERE lumberjack = :jack", {'jack': lumberjack}) 

'format'

>>> cursor.execute("SELECT spam FROM eggs WHERE lumberjack = %s", (lumberjack,)) 

'pyformat'

>>> cursor.execute("SELECT spam FROM eggs WHERE lumberjack = %(jack)s", {'jack': lumberjack}) 

You can see which your client library supports by looking at the paramstyle module-level variable:

>>> clientlibrary.paramstyle 'pyformat' 

Any of the above options should Do The Right Thing with regards to handling your possibly insecure data. As Bruno pointed out, please don't ever try to insert parameters yourself. The commonly-used client libraries are much better at processing data correctly than we mere mortals will ever be.