Python MySQL转义特殊字符

Question

我使用python在MySQL中插入一个带有特殊字符的字符串。

要插入的字符串如下所示：

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

当我执行更新时，会出现以下错误：

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

如何原始插入此文本？

Answer 1

Python示例如何插入原始文本：

在MySQL中创建一个表：

create table penguins(id int primary key auto_increment, msg VARCHAR(4000))

Python代码：

#!/usr/bin/env python
import sqlalchemy
from sqlalchemy import text

engine = sqlalchemy.create_engine(
    "mysql+mysqlconnector://yourusername:yourpassword@yourhostname.com/your_database")
db = engine.connect()

weird_string = "~!@#$%^&*()_+`1234567890-={}|[]\;':\""

sql = text('INSERT INTO penguins (msg) VALUES (:msg)')
insert = db.execute(sql, msg=weird_string)

db.close()

运行它，检查输出：

select * from penguins

1      ~!@#$%^&*()_+`1234567890-={}|[]\;\':"

这些字符在插入时都没有被解释。

Answer 2

虽然我也认为应该使用参数绑定，但也有以下几点：

>>> import MySQLdb
>>> example = r"""I don't like "special" chars ¯\_(ツ)_/¯"""
>>> example
'I don\'t like "special" chars \xc2\xaf\\_(\xe3\x83\x84)_/\xc2\xaf'
>>> MySQLdb.escape_string(example)
'I don\\\'t like \\"special\\" chars \xc2\xaf\\\\_(\xe3\x83\x84)_/\xc2\xaf'

Answer 3

这是使用parameter binding而不是在Python中格式化参数的原因之一。

就这样做：

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

然后：

cur.execute(sql, macs, host)

这样，您就可以将字符串作为字符串处理，并让MySQL库计算出如何引用和转义它。

除此之外，您通常可以获得更好的性能（因为MySQL可以编译和缓存一个查询，并将其用于不同的参数值），并避免SQL injection attacks（最常见的黑客攻击方法之一）。