python - 在 Python 3 中使用 MySQLdb 进行参数化查询

Question

我尝试使用参数化查询通过 Python 脚本将数据插入 MySQL 数据库，而不是将参数格式化为字符串并打开应用程序以进行 SQL 注入(inject)。

这是 Python 代码:

#!/usr/bin/python3

import MySQLdb as mdb

conn = mdb.connect("localhost", "fakeuser", "fakepassword", "testdb")
c = conn.cursor()
c.execute("INSERT INTO test VALUES (%s)", ("Test", ))
conn.commit()
conn.close()

当我运行脚本时，我得到以下堆栈跟踪:

Traceback (most recent call last):
  File "./load.py", line 7, in <module>
    c.execute("INSERT INTO test VALUES (%s)", ("Test", ))
  File "/opt/python3/lib/python3.2/site-packages/MySQL_python-1.2.3-py3.2-linux-x86_64.egg/MySQLdb/cursors.py", line 184, in execute
    self.errorhandler(self, exc, value)
  File "/opt/python3/lib/python3.2/site-packages/MySQL_python-1.2.3-py3.2-linux-x86_64.egg/MySQLdb/connections.py", line 37, in defaulterrorhandler
    raise errorvalue
  File "/opt/python3/lib/python3.2/site-packages/MySQL_python-1.2.3-py3.2-linux-x86_64.egg/MySQLdb/cursors.py", line 171, in execute
    r = self._query(query)
  File "/opt/python3/lib/python3.2/site-packages/MySQL_python-1.2.3-py3.2-linux-x86_64.egg/MySQLdb/cursors.py", line 330, in _query
    rowcount = self._do_query(q)
  File "/opt/python3/lib/python3.2/site-packages/MySQL_python-1.2.3-py3.2-linux-x86_64.egg/MySQLdb/cursors.py", line 294, in _do_query
    db.query(q)
_mysql_exceptions.ProgrammingError: (1064, "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 '%s)' at line 1")

但是，我阅读的所有文档都表明 %s 是正确的占位符。我试图将 %s 括在单引号中，但这会导致语句按字面意思插入该字符串，而不是替换给定值，这正是我所期望的。

相关软件版本如下:

• python 3.2.3
• MySQL_python-1.2.3-py3.2
• MySQL 5.5.40-36.1

注意:我使用的是共享主机，无法升级软件。

Answer 1

MySQL-Python 项目不支持 Python 3—参见 the project web page ，多年来一直说“Python 3 支持即将推出”，并且 the cheese shop entry ，它只提供 Python 2.7 版本并说:

Python-3.0 will be supported in a future release.

我没有听说过(或可以通过搜索找到)可用的“-py3.2”发行版；您的主机可能有一些愚蠢的东西(例如 this )来让库安装在 Python 3 上，但是能够安装库并不能保证它会实际工作。

这是 an example of someone running into the same problem on a shared host :

Today i switched to Python v3.2.3 on my remote web server at HostGator. My cgi-script which it works at python v2.6.6 now produces theses errors:

--> --> 
Traceback (most recent call last): 
File "/opt/python3/lib/python3.2/site-packages/MySQL_python-1.2.3-py3.2-linux-x86_64.egg/MySQLdb/cursors.py", line 171, in execute 
r = self._query(query) 
File "/opt/python3/lib/python3.2/site-packages/MySQL_python-1.2.3-py3.2-linux-x86_64.egg/MySQLdb/cursors.py", line 330, in _query 
rowcount = self._do_query(q) 
File "/opt/python3/lib/python3.2/site-packages/MySQL_python-1.2.3-py3.2-linux-x86_64.egg/MySQLdb/cursors.py", line 294, in _do_query 
db.query(q) 
_mysql_exceptions.ProgrammingError: (1064, "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 '%s' at line 1") 

关于 %s 语法对于库来说是正确的，你是对的；不幸的是，它在 Python 3 中表现不佳。您可以尝试改用命名参数样式，看看是否能帮到您，但最好的办法是完全停止使用不受支持的库。如果你的主机也提供an old version of Connector/Python ，例如。