在MySQL更新命令python3.5中使用准备好的查询更改列

2024-06-17 17:53:59 发布

您现在位置:Python中文网/ 问答频道 /正文

我正在尝试编写一个函数,它包含3个值:要编辑的列、要插入的值和行的id(在本例中是散列)。但是我得到了一个标准的“SQL语法有错误”

def _alter_material_prop(self, hash, key, val):
    sql = "UPDATE `materials` SET "+key+" = %s WHERE `materials`.`hashkey` = %s"
    self.cursor.execute(sql, (val, hash))
    self.db.commit()

这是我的sql可注入代码。你知道吗

我想这样做:

def _alter_material_prop(self, hash, key, val):
    sql = "UPDATE `materials` SET %s = %s WHERE `materials`.`hashkey` = %s"
    self.cursor.execute(sql, (key, val, hash))
    self.db.commit()

但这会产生以下错误消息:

Traceback (most recent call last):
  File "/Users/Nate/PycharmProjects/mofdb-insert-mofs/mofdb_interface_tests.py", line 478, in test_alter_mat_prop
    mofdb._alter_material_prop(hashkey, "PLD", 1337.0)
  File "/Users/Nate/PycharmProjects/mofdb-insert-mofs/mofdb_interface.py", line 227, in _alter_material_prop
    self.cursor.execute(sql, (key, val, hash))
  File "/Users/Nate/PycharmProjects/mofdb-insert-mofs/venv/lib/python3.5/site-packages/mysql/connector/cursor.py", line 559, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "/Users/Nate/PycharmProjects/mofdb-insert-mofs/venv/lib/python3.5/site-packages/mysql/connector/connection.py", line 494, in cmd_query
    result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
  File "/Users/Nate/PycharmProjects/mofdb-insert-mofs/venv/lib/python3.5/site-packages/mysql/connector/connection.py", line 396, in _handle_result
    raise errors.get_exception(packet)
mysql.connector.errors.ProgrammingError: 1064 (42000): 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 ''PLD' = 1337.0 WHERE `materials`.`hashkey` = '43ba34f38db8ec0f20cb058853275535ea' at line 1

Tags: keyinselfsqllinevalhashusers