Sqlite3 更新变量定义的行 [英] Sqlite3 Updating Row Defined by a Variable

问题描述

不确定我的标题是否正确，但基本上我的问题是是否可以让 sqlite 更新由变量定义的行?例如:

Not sure if I phrased the title correctly, but basically my question is is it possible to have sqlite update a row which is defined by a variable? For example:

db.execute('''UPDATE CUSTOMER SET ? = ? WHERE CUSTOMER_ID = ?''', (title, info.get(), k))

其中标题"(第一个问号)是我要在表 Customer 中更新的行"的名称.我已经尝试了上面的代码，但它不起作用.有谁知道是否可以通过 sqlite3 以任何方式做到这一点?

where 'title' (the first question mark) is the name of the 'row' I want to update within the table Customer. I have tried the above code but it doesn't work. Does anybody know if it is possible to do this with sqlite3 in any way?

推荐答案

SQL 参数被设计为永远可解释为 SQL 对象(如列名)；这是他们的主要用例之一.如果他们不这样做，他们将无法阻止 SQL 注入攻击.相反，title 值要么正确转义为 value，要么完全拒绝，因为语法不允许在该位置有值.

SQL parameters are designed to never be interpretable as SQL objects (like column names); that is one of their major usecases. If they didn't they wouldn't prevent SQL injection attacks. Instead, the title value is either properly escaped as a value, or rejected altogether as the syntax doesn't allow a value in that location.

因此，您需要确保您的 title 变量是正确的 SQL 对象名称(永远不要在此处直接接受用户输入)并使用字符串格式就是那个价值:

As such, you need to make sure that your title variable is a proper SQL object name (never take user input directly here) and use string formatting for just that value:

db.execute(
    '''UPDATE CUSTOMER SET {} = ? WHERE CUSTOMER_ID = ?'''.format(title),
    (info.get(), k))

您可能希望首先将 title 与一组预定义的可能的列名进行匹配.

You probably want to match title against a pre-defined set of possible column names first.

这篇关于Sqlite3 更新变量定义的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！