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.

Answer 2

For values, you should use prepared queries to embed them. For the rows, I'm not too sure... it depends on your setting. You'll probably want to accept any character above the ASCII value 32 except an unescaped backtick. Don't think there's a specific function for this, though.

cursor2.execute("UPDATE myTable SET `" + str(row[1]) + "` = ? WHERE ID = ?", (row[3], row[1]))

Prepared queries have question marks where there should be variables, and you pass in a list or a tuple as a second argument to specify what they should be substituted with. The driver will take care of making the values safe. You can only put interrogation marks where values are expected, though; so you can't use them as column names.

Answer 3

You should learn to use query parameters:

colname = str(row[1]).replace("`", "\\`")
sql = "update myTable set `%s` = :col1 WHERE ID = :id" % (colname)
cursor2.execute(sql, {"col1":str(row[3]), "id":str(row[0])})

Answer 4

When you using Oracle MySql connector you can escape special characters in a follow way:

import mysql.connector
from mysql.connector import conversion

query = "SELECT id, code, name, description FROM data WHERE code='{:s}'"

escaped_text = conversion.MySQLConverter().escape(unescaped_text)

cursor.execute(query.format(escaped_text))