在 Python sqlite3 查询中替换列名 [英] Substituting column names in Python sqlite3 query

问题描述

我在 sqlite3 数据库中有一个宽表，我希望在 Python 脚本中动态查询某些列.我知道通过字符串连接注入参数是不好的，所以我尝试使用参数替换.

I have a wide table in a sqlite3 database, and I wish to dynamically query certain columns in a Python script. I know that it's bad to inject parameters by string concatenation, so I tried to use parameter substitution instead.

我发现，当我使用参数替换来提供列名时，会得到意想不到的结果.一个最小的例子:

I find that, when I use parameter substitution to supply a column name, I get unexpected results. A minimal example:

import sqlite3 as lite

db = lite.connect("mre.sqlite")
c = db.cursor()

# Insert some dummy rows
c.execute("CREATE TABLE trouble (value real)")
c.execute("INSERT INTO trouble (value) VALUES (2)")
c.execute("INSERT INTO trouble (value) VALUES (4)")
db.commit()


for row in c.execute("SELECT AVG(value) FROM trouble"):
    print row   # Returns 3

for row in c.execute("SELECT AVG(:name) FROM trouble", {"name" : "value"}):
    print row   # Returns 0

db.close()

是否有比简单地将列名注入字符串并运行它更好的方法来完成此操作?

Is there a better way to accomplish this than simply injecting a column name into a string and running it?

推荐答案

正如 Rob 刚刚在评论中指出的那样，有一个相关的 SO 帖子包含我的答案.这些替换结构被称为占位符"，这就是我最初没有在 SO 上找到答案的原因.列名没有占位符模式，因为 动态指定列不是代码安全问题:

As Rob just indicated in his comment, there was a related SO post that contains my answer. These substitution constructions are called "placeholders," which is why I did not find the answer on SO initially. There is no placeholder pattern for column names, because dynamically specifying columns is not a code safety issue:

这归结为安全"的含义.传统观点认为使用普通的 python 字符串操作将值放入您的查询不是安全的".这是因为有各种各样的东西如果你这样做可能会出错，而且这些数据通常来自用户并且不受您的控制.您需要一种 100% 可靠的方式正确转义这些值，以便用户无法在数据值并让数据库执行它.所以图书馆的作家们这份工作；你永远不应该.

It comes down to what "safe" means. The conventional wisdom is that using normal python string manipulation to put values into your queries is not "safe". This is because there are all sorts of things that can go wrong if you do that, and such data very often comes from the user and is not in your control. You need a 100% reliable way of escaping these values properly so that a user cannot inject SQL in a data value and have the database execute it. So the library writers do this job; you never should.

但是，如果您正在编写通用的帮助程序代码来对事物进行操作在数据库中，那么这些考虑就不那么适用了.你是隐含地授予任何可以调用此类代码的人访问所有内容的权限在数据库中；这就是帮助程序代码的重点.所以现在安全问题是确保用户生成的数据永远不会被在这样的代码中使用.这是编码中的一般安全问题，并且是与盲目执行用户输入字符串相同的问题.这是一个将值插入查询的不同问题，因为有您希望能够安全地处理用户输入的数据.

If, however, you're writing generic helper code to operate on things in databases, then these considerations don't apply as much. You are implicitly giving anyone who can call such code access to everything in the database; that's the point of the helper code. So now the safety concern is making sure that user-generated data can never be used in such code. This is a general security issue in coding, and is just the same problem as blindly execing a user-input string. It's a distinct issue from inserting values into your queries, because there you want to be able to safely handle user-input data.

所以，解决方案是首先没有问题:使用字符串格式注入值，开心，继续你的生活.

So, the solution is that there is no problem in the first place: inject the values using string formatting, be happy, and move on with your life.

这篇关于在 Python sqlite3 查询中替换列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！