Python SQL查询的类型错误

Question

我想从我的python程序中执行SQL语句。为此，我使用MySQLdb库。这是我的代码：

def execute(sql_statement):
db = MySQLdb.connect("<DatabaseIP>", "<DatabaseUserName>", "<DatabasePassword>", "<DatabaseName>")
cursor = db.cursor()
cursor.execute(sql_statement)
data = cursor.fetchone()
db.close()
return data
def look_up_user_id(username, password):
print(type(username))
print(type(password))
sql_statement = "SELECT ID FROM user WHERE name = '" + username + "' AND password = '" + password + "'"
print(sql_statement)
return Database.execute(sql_statement)

这是从请求中提取用户名和密码的方法，也是实际调用Database类方法的方法。

@app.route('/auth', methods=['GET'])
def log_in():
return AuthenticationManager.log_in(request.authorization.username, request.authorization.password)

def log_in(username, password):
return Database.execute(SQLStatementBuilder.look_up_user_id(username, password))[0]

当我执行print(look_up_user_id("me", "password"))时，一切都按照需要工作，我得到了用户的ID。但是，当我向我的程序发送一个HTTP请求，在基本的身份验证标头中包含用户名和密码时，我会得到

File "<PythonPath>PythonPython38Libsite-packagesMySQLdbcursors.py", line 208, in execute
assert isinstance(query, (bytes, bytearray))
AssertionError 

这是提出错误的方法

def execute(self, query, args=None):
"""Execute a query.
query -- string, query to execute on server
args -- optional sequence or mapping, parameters to use with query.
Note: If args is a sequence, then %s must be used as the
parameter placeholder in the query. If a mapping is used,
%(key)s must be used as the placeholder.
Returns integer represents rows affected, if any
"""
while self.nextset():
pass
db = self._get_db()
if isinstance(query, unicode):
query = query.encode(db.encoding)
if args is not None:
if isinstance(args, dict):
nargs = {}
for key, item in args.items():
if isinstance(key, unicode):
key = key.encode(db.encoding)
nargs[key] = db.literal(item)
args = nargs
else:
args = tuple(map(db.literal, args))
try:
query = query % args
except TypeError as m:
raise ProgrammingError(str(m))
assert isinstance(query, (bytes, bytearray))
res = self._query(query)
return res

方法look_up_user_id中的两个print调用在两个测试用例中都只返回<class 'str'>，并返回密码和名称。

谢谢你的帮助！

编辑：添加了引发错误的方法

Answer 1

问题是lookup_user_id正在返回Database.execute(sql_statement)的结果，而结果在AuthorizationManager的返回语句中再次传递给Database.execute。要解决此问题，请让SQLStatementBuilder返回sql_statement。还要注意，log_in视图应该返回一个字符串或类似的内容。

另外，考虑执行如下SQL语句：

res = cursor.execute("""SELECT id FROM user WHERE name = %s and password = %s""",
(username, password))

以防止SQL注入攻击。