Python MySQLdb 错误 - 是什么导致了这个

Question

我想知道为什么会收到此错误:

cmd = "INSERT INTO resulttest (category, value, timestamp) VALUES (" + key + ", " + str(value) + ", " + str(timestamp) + ")"
c.execute(cmd)
db.commit()

    INSERT INTO resulttest (category, value, timestamp) VALUES (composed, 2, 1343186948.8)

Traceback (most recent call last):
  File "C:/Behavioral Technology/Google Drive/twitterthingv5test.py", line 94, in <module>
    moodParser()
  File "C:/Behavioral Technology/Google Drive/twitterthingv5test.py", line 92, in moodParser
    query()
  File "C:/Behavioral Technology/Google Drive/twitterthingv5test.py", line 37, in query
    main(columns)
  File "C:/Behavioral Technology/Google Drive/twitterthingv5test.py", line 81, in main
    c.execute(cmd)
  File "C:\Python27\lib\site-packages\MySQLdb\cursors.py", line 174, in execute
    self.errorhandler(self, exc, value)
  File "C:\Python27\lib\site-packages\MySQLdb\connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue

我相信这与我将值传递给 SQL 命令的方式有关。

Answer 1

您创建查询的代码并未尝试引用字符串值:

cmd = "INSERT INTO resulttest (category, value, timestamp) VALUES (" + key + ", " + str(value) + ", " + str(timestamp) + ")"

看看你打印的SQL语句:

INSERT INTO resulttest (category, value, timestamp) VALUES (composed, 2, 1343186948.8)

“category”不应该加引号吗？

您不应该首先编写带有字符串操作的 SQL 语句。 SQL 注入(inject)漏洞就是这样发生的。相反，您应该使用占位符并让 MySQL 库处理它们:

c.execute(
    "INSERT INTO resulttest (category, value, timestamp) VALUES (?, ?, ?)", 
    (key, value, timestamp)
)