改进 SQL INSERT 查询以避免 sql 注入 [英] Improve SQL INSERT query to avoid sql injections
问题描述
我正在使用 pymyql/mysql-connector 将消息写入 mysql 数据库.消息在来自 mqtt 代理的回调(paho.mqtt 回调)上处理.我有 4 个不同的表,并且根据消息类型,我将消息插入到数据库中.我已经编写了如下插入查询.这种写法似乎会导致 sql 注入.有什么建议可以改进插入查询语句吗?
I am using pymyql/mysql-connector to write the messages to mysql database. The messages are processed on callback (paho.mqtt callback) from mqtt broker.I have 4 different tables and based on the message type, I am inserting messages into database. I have written the insert queries as below. this way of writing leads to sql injections it seems.Any suggestions how can I improve the insert query statements?
# callback attached to paho.mqtt.client
def on_message(self, client, userdata, msg):
if msg.topic.startswith("topic1/"):
self.bulkpayload += "(" + msg.payload.decode("utf-8") + "," + datetime + "),"
elif msg.topic.startswith("topic2/"):
self.insertStatement += "INSERT INTO mydatabase.table1 VALUES (" + msg.payload.decode("utf-8") + "," + datetime + ");"
elif msg.topic.startswith("topic3/")
self.insertStatement += "INSERT INTO mydatabase.table2 VALUES (" +msg.payload.decode("utf-8") + "," + datetime + ");"
elif msg.topic.startswith("messages"):
self.insertStatement += "INSERT INTO mydatabase.table3 VALUES ('" + msg.topic + "'," + msg.payload.decode("utf-8") + "," + datetime + ");"
else:
return # do not store in DB
cursor.execute(self.insertStatement)
cursor.commit()
推荐答案
使您的查询使用参数.注射的机会少得多:
Make your query use parameters. Much less chance of injection:
cursor.execute("INSERT INTO table VALUES (%s, %s, %s)", (var1, var2, var3))
信用(和更多信息)在这里:如何使用Python中SQL语句中的变量?
credit (and more info) here: How to use variables in SQL statement in Python?
此外,Dan Bracuk 是正确的 - 如果您还没有验证您的参数,请确保在执行 SQL 之前验证您的参数
Also, Dan Bracuk is correct - make sure you validate your params before executing the SQL if you aren't already
这篇关于改进 SQL INSERT 查询以避免 sql 注入的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!