sqlite中的变量表名 [英] Variable table name in sqlite
问题描述
问题:是否可以使用变量作为表名,而不必使用字符串构造函数这样做?
Question: Is it possible to use a variable as your table name without having to use string constructors to do so?
信息:
我正在开发一个项目,目录数据来自我的星级模拟。为此,我将所有的数据加载到sqlite数据库。它的工作相当不错,但我决定添加更多的灵活性,效率和可用性我的数据库。我计划后来添加飞机到模拟,并希望有一个表为每个明星。这样我不需要查询每个太阳系中1-4k的一些表格。
I'm working on a project right now that catalogs data from a star simulation of mine. To do so I'm loading all the data into a sqlite database. It's working pretty well, but I've decided to add a lot more flexibility, efficiency, and usability to my db. I plan on later adding planetoids to the simulation, and wanted to have a table for each star. This way I wouldn't have to query a table of 20m some planetoids for the 1-4k in each solar system.
我被告知使用字符串构造函数是不好的,因为它让我容易受到SQL注入攻击。虽然这不是一个大问题在这里,因为我是唯一的人访问这些dbs,我想遵循最佳做法。这种方式,如果我做一个类似的情况下,它对公众开放的项目,我知道该怎么办。
I've been told using string constructors is bad because it leaves me vulnerable to a SQL injection attack. While that isn't a big deal here as I'm the only person with access to these dbs, I would like to follow best practices. And also this way if I do a project with a similar situation where it is open to the public, I know what to do.
目前我这样做: / p>
Currently I'm doing this:
cursor.execute("CREATE TABLE StarFrame"+self.name+" (etc etc)")
这可行,但我想做更像:
This works, but I would like to do something more like:
cursor.execute("CREATE TABLE StarFrame(?) (etc etc)",self.name)
虽然我知道这可能是不可能的。虽然我会解决像
though I understand that this would probably be impossible. though I would settle for something like
cursor.execute("CREATE TABLE (?) (etc etc)",self.name)
如果这不可能,我会接受这个答案,但如果有人知道做到这一点,做告诉。 :)
If this is not at all possible, I'll accept that answer, but if anyone knows a way to do this, do tell. :)
我在python中编码。
I'm coding in python.
推荐答案
,表不能是参数替换的目标(我没有找到任何确定的源,但我已经在几个网络论坛上看到它)。
Unfortunately, tables can't be the target of parameter substitution (I didn't find any definitive source, but I have seen it on a few web forums).
如果你担心注入(你可能应该),你可以编写一个函数,在传递之前清理字符串。因为你正在寻找一个表名,你应该是安全的只接受字母数字,剥离所有的标点符号,如)(] [;,
和空格。
If you are worried about injection (you probably should be), you can write a function that cleans the string before passing it. Since you are looking for just a table name, you should be safe just accepting alphanumerics, stripping out all punctuation, such as )(][;,
and whitespace. Basically, just keep A-Z a-z 0-9
.
def scrub(table_name):
return ''.join( chr for chr in table_name if chr.isalnum() )
scrub('); drop tables --') # returns 'droptables'
这篇关于sqlite中的变量表名的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!