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

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=5c.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 don't need the quotes around the position holder ('%s') if the parameter is a string.

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.

If you're using mysqldb, you can use the built-in escape_string function. Like this.

sql = "SELECT spam FROM eggs WHERE lumberjack = '" + MySQLdb.escape_string(str(lumberjack)) + "';"cursor.execute(sql)

I always prefer to use the database connector's escape functionality - it works as intended and manually coding escape functions yourself is a security risk.