python数据库特殊字符转义,Python MySQL转义特殊字符

最新推荐文章于 2024-05-07 10:26:56 发布

weixin_39929721

最新推荐文章于 2024-05-07 10:26:56 发布

阅读量174

点赞数

文章标签： python数据库特殊字符转义

本文介绍了一种使用Python向MySQL数据库插入包含特殊字符字符串的方法。通过采用参数绑定而非字符串格式化的方式，可以有效避免SQL语法错误及提升安全性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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?

解决方案

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