由于字符串替换不利于形成 SQL 查询，您如何动态分配表名? [英] Being that string substitution is frowned upon with forming SQL queries, how do you assign the table name dynamically?

问题描述

对 sqlite3 非常陌生，所以请耐心等待..

Pretty new to sqlite3, so bear with me here..

我想要一个函数，我可以向它传递表名和要更新的值.

I'd like to have a function to which I can pass the table name, and the values to update.

我最初是这样开始的:

def add_to_table(table_name, string):
    cursor.execute('INSERT INTO {table} VALUES ({var})'
        .format(
            table=table_name,
            var=string)
        )

这行得通，但进一步阅读 sqlite3 表明这是一种非常不安全的处理方式.但是，使用他们的 ? 语法，我无法传递名称来指定变量.

Which works A-OK, but further reading about sqlite3 suggested that this was a terribly insecure way to go about things. However, using their ? syntax, I'm unable to pass in a name to specify the variable.

我尝试添加 ? 来代替表格，但这会引发语法错误.

I tried adding in a ? in place of the table, but that throws a syntax error.

cursor.execute('INSERT INTO ? VALUES (?)', ('mytable','"Jello, world!"'))
>> >sqlite3.OperationalError: near "?": syntax error  

sql语句中的table能否安全动态传入?

Can the table in an sql statement be passed in safely and dynamically?

推荐答案

问题不是动态字符串替换本身.它使用用户提供的字符串进行动态字符串替换，这是一个大问题，因为这会让您面临 SQL 注入攻击.如果您绝对 100% 确定表名是您控制的安全字符串，那么将其拼接到 SQL 查询中将是安全的.

Its not the dynamic string substitution per-se thats the problem. Its dynamic string substitution with an user-supplied string thats the big problem because that opens you to SQL-injection attacks. If you are absolutely 100% sure that the tablename is a safe string that you control then splicing it into the SQL query will be safe.

if some_condition():
   table_name = 'TABLE_A'
else:
   table_name = 'TABLE_B'

cursor.execute('INSERT INTO '+ table_name + 'VALUES (?)', values)

也就是说，使用这样的动态 SQL 肯定是一种代码异味，因此您应该仔细检查是否可以找到更简单的替代方法，而无需动态生成的 SQL 字符串.此外，如果您真的需要动态 SQL，那么 SQLAlchemy 之类的工具可能有助于确保您生成的 SQL 格式正确.

That said, using dynamic SQL like that is certainly a code smell so you should double check to see if you can find a simpler alternative without the dynamically generated SQL strings. Additionally, if you really want dynamic SQL then something like SQLAlchemy might be useful to guarantee that the SQL you generate is well formed.

这篇关于由于字符串替换不利于形成 SQL 查询，您如何动态分配表名?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！