Python MySQL escape special characters
mysql, python
Solution
This is one of the reasons you're supposed to use parameter binding instead of formatting the parameters in Python.
Just do this:
sql = 'UPGRADE inventory_server set server_mac = %s where server_name = %s'
Then:
cur.execute(sql, macs, host)
That way, you can just deal with the string as a string, and let the MySQL library figure out how to quote and escape it for you.
On top of that, you generally get better performance (because MySQL can compile and cache one query and reuse it for different parameter values) and avoid SQL injection attacks (one of the most common ways to get yourself hacked).
Problem
I am using python to insert a string into MySQL with special characters. The string to insert looks like so: ``` macaddress_eth0;00:1E:68:C6:09:A0;macaddress_eth1;00:1E:68:C6:09:A1 ``` Here is the SQL: ``` UPGRADE inventory_server set server_mac = macaddress\_eth0\;00\:1E\:68\:C6\:09\:A0\;macaddress\_eth1\;00\:1E\:68\:C6\:09\:A1' where server_name = 'myhost.fqdn.com ``` When I execute the update, I get this error: ``` ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPGRADE inventory_server set server_mac = 'macaddress\_eth0\;00\:1E\:68\:C6\:09\' at line 1 ``` The python code: ``` sql = 'UPGRADE inventory_server set server_mac = \'%s\' where server_name = \'%s\'' % (str(mydb.escape_string(macs)),host) print sql try: con = mydb.connect(DBHOST,DBUSER,DBPASS,DB); with con: cur = con.cursor(mydb.cursors.DictCursor) cur.execute(sql) con.commit() except: return False ``` How can I insert this text raw?