Python equivalent of mysql_real_escape_string, for getting strings safely into MySQL?

Question

Hi all

Is there a Python equivalent of PHP's mysql_real_escape_string?

I'm trying to insert some strings into a MySQL db direct from Python, and keep getting tripped up by quotes in the strings.

mysql_string = "INSERT INTO candidate (name, address) VALUES  " 
for k, v in v_dict.iteritems():
    mysql_string += " ('" + v_dict['name'] + "', '" + v_dict['address'] + "'), "
mysql_string += ";"
cursor.execute(mysql_string)

I've tried re.escape() but that escapes every non-alphanumeric character in the strings, which isn't what I need - I just need to escape single quotes in this instance (plus more generally anything else that might trip up MySQL).

Could do this manually I guess, but is there a smarter way to do it in Python?

Answer 1

In this particular case you just need to use executemany method of the cursor.

mysql_string = "INSERT INTO candidate (name, address) VALUES  (%s,%s);"
cursor.executemany(mysql_string, v_dict.iteritems())

Answer 2

If you are using mysql-python, just try

MySQLdb.escape_string(SQL)

Example

>>> import MySQLdb
>>> MySQLdb.escape_string("'")
"\\'"

Answer 3

MySQLdb.escape_string is equivalent. All of that is described in documentation.

Answer 4

cursor.executemany('INSERT INTO candidate (name, address) VALUES (%s, %s)',
                   [(v_dict['name'], v_dict['address'])] * len(v_dict))

should do what your code appears to attempt -- inserting the same identical values N times (you're looping on v_dict.iteritems() but completely ignoring the loop variables, and instad just reusing those specific two values from v_dict each and every time). Of course if you mean something completely different the second line will need to be changed accordingly, but the key idea anyway is to use placeholders, not string formatting, for such tasks.

Answer 5

Is there a Python equivalent of PHP's mysql_real_escape_string?

Yes, it's escape_string on the database connection object.

Not escape_string on the MySQLdb module, which is equivalent to mysql_escape_string in PHP, and has the same potential vulnerability with multibyte character sets. You shouldn't use MySQLdb.escape_string.

In any case, you are much better off using parameterised queries as in Alex's answer. This makes it easier to port to other databases (it's part of the DB-API standard, which escape_string is not), and it's generally more convenient. (And much more convenient than parameterisation is in PHP.)