Python MySQL转义特殊字符 [英] Python MySQL escape special characters

问题描述

我正在使用python将带有特殊字符的字符串插入MySQL.

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

这是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

python代码:

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

如何原始插入此文本?

推荐答案

这是您应该使用

This is one of the reasons you're supposed to use parameter binding instead of formatting the parameters in Python.

只需执行以下操作:

sql = 'UPGRADE inventory_server set server_mac = %s where server_name = %s'

然后:

cur.execute(sql, macs, host)

这样，您可以将字符串作为字符串处理，然后让MySQL库找出如何为您引用和转义它.

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.

最重要的是，您通常会获得更好的性能(因为MySQL可以编译和缓存一个查询，并将其重用于不同的参数值)，并且避免

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).

这篇关于Python MySQL转义特殊字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！