python - Python SQL query has the wrong type
问题描述
I want to execute a SQL Statement from my python program. For this I use the MySQLdb library. This is my code:
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)
This is the method in which the username and password are extracted from the request and the method that actually calls the Database class method.
@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]
When I execute print(look_up_user_id("me", "password"))
everything works as wanted and I get the ID of the user. But when I send a HTTP-Request to my Program with username and password in a basic authentication header, I get
File "<PythonPath>\Python\Python38\Lib\site-packages\MySQLdb\cursors.py", line 208, in execute
assert isinstance(query, (bytes, bytearray))
AssertionError
This is the method in which the Error is raised
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
The two calls of print in the method look_up_user_id
just return <class 'str'>
in both test cases and for password and name.
Thanks for any help!
Edit: added method that raises Error
解决方案
问题是lookup_user_id
正在返回结果,Database.execute(sql_statement)
并且该结果被再次传递给Database.execute
在的返回语句中AuthorizationManager
。要解决此问题,请使用SQLStatementBuilder
return sql_statement
。另请注意,log_in
视图应返回字符串或类似内容。
另外,考虑执行如下 SQL 语句:
res = cursor.execute("""SELECT id FROM user WHERE name = %s and password = %s""",
(username, password))
以防止 SQL 注入攻击。
推荐阅读
- python - img = np.reshape(img,[1,224,224,3]) - ValueError:无法将大小为 200704 的数组重塑为形状 (1,224,224,3)
- swift - Firebase getIDToken 以及如何在 API 调用中使用它
- python - 通过命令提示符安装 pyttsx3 时出错
- python - 我不知道为什么我对 django 的看法没有显示在浏览器上
- python - 如何在 matplotlib 中跨 twinx 控制 zorder?
- google-apps-script - 使用应用程序脚本更新嵌入式图表会破坏垂直轴格式
- python - 如何在 AWS CDK 中复制和修改密钥
- c++ - 在 VS Code 中构建 wxWidgets 项目
- javascript - 如何在 Redux 中更新状态 - 上下移动 todo - React
- reactjs - 当我运行 npm test 时,xstate 不会改变我机器的状态(如果我运行应用程序,它会正常工作)