python - SQL语法错误:1064,如何修复？

Question

我用python(Twisted)写了一个Udp服务器，接收udp消息并更新mysql数据库:

sql = "update `device` set `msg`='%s', `d_addr`='%s', `d_port`=%d where `did`=%d" %(msg, host, port, r[0])
try:
    txn.execute(sql)
except Exception, e:
    f = open('./err_log', 'a')
    f.write('%s\n' % e)
    f.write('%s\n' % sql)
    f.close()

err_log中的错误信息是:

(1064, "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '220.168.13.132', `d_port`=14058 where `did`=2' at line 1")

update `device` set `msg`='.?F/.ddd?', `d_addr`='220.168.13.132', `d_port`=14058 where `did`=2

所以，我手动执行了sql，但没有错误:

MariaDB [kj]> update `device` set `msg`='.?F/.ddd?',
    `d_addr`='220.168.13.132', `d_port`=14058 where `did`=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

msg是远程客户端发送的字符串(18字节)，字符串的ascii码是:

0x86 0xAC 0xCF 0x23 0x29 ... 0xE3

Answer 1

参数化查询并忘记与将变量插入查询相关的 SQL 语法错误。作为奖励，您可以使您的代码免受 SQL injection attacks 的侵害。 :

sql = """
    UPDATE
        device 
    SET 
        msg = %s, 
        d_addr = %s, 
        d_port = %s 
    where 
        did = %s"""
txn.execute(sql, (msg, host, port, r[0]))