What is the best escape character strategy for Python/MySQL combo?

Question

This is my query.

cursor2.execute("update myTable set `"+ str(row[1]) +"` = \"'" + str(row[3]) +"'\" where ID = '"+str(row[0])+"'")

It is failing when row values have double quotes "some value". How do I escape all special characters?

Answer 1

Here is an example:

import MySQLdb
column = str(MySQLdb.escape_string(row[1]))
query = "update myTable set %(column)s = %%s where ID = %%s" % dict(column = column) 
cursor2.execute(query, [row[3], row[0]])

Update

Here is a brief commentary:

column = str(MySQLdb.escape_string(row[1]))

Always a good idea to escape anything that goes into a query. In this case we are dynamically adding a column name and hence it has to be escaped before the query is executed.

query = "update myTable set %(column)s = %%s where ID = %%s" % dict(column = column) 

I am forming the query here. I am trying to achieve two things: (1) form a query with column name populated using the column variable declared in the previous line (2) add placeholders that will be filled in by actual parameters during query execution.

The snippet dict(column = column) is actually another way of creating the dictionary {'column': column}. This is made possible using the dict constructor. I don't want to fill in the other place holders just yet so I escape them using two percentage signs (%%).

cursor2.execute(query, [row[3], row[0]])

Finally execute the query. If you print query before executing you'll see the string update myTable set column_name = %s where ID = %s.