SQLite 中的变量表名称
- 2024-12-31 08:37:00
- admin 原创
- 151
问题描述：
我目前正在做一个项目，该项目对我的恒星模拟数据进行分类。为此，我将把所有数据加载到 sqlite 数据库中。它运行良好，但我决定为我的数据库增加更多的灵活性、效率和可用性。我计划稍后将行星添加到模拟中，并希望为每颗恒星建立一个表。这样，我就不必为每个太阳系中的 1-4k 个行星查询一个包含 20m 个行星的表。
有人告诉我使用字符串构造函数是不好的，因为它会让我容易受到 SQL 注入攻击。虽然这在这里不是什么大问题，因为我是唯一有权访问这些数据库的人，但我想遵循最佳实践。
目前我正在这样做：
cursor.execute("CREATE TABLE StarFrame"+self.name+" (etc etc)")
这可行，但我想做更多的事情：
cursor.execute("CREATE TABLE StarFrame(?) (etc etc)",self.name)
虽然我知道这可能是不可能的。虽然我会满足于类似
cursor.execute("CREATE TABLE (?) (etc etc)",self.name)
是否可以使用变量作为表名而不必使用字符串构造函数来执行此操作？
解决方案 1：
不幸的是，表不能成为参数替换的目标（我没有找到任何明确的来源，但我在一些网络论坛上看到过）。
如果您担心注入（您可能应该担心），您可以编写一个函数在传递字符串之前清理它。由于您只是在寻找表名，因此您应该安全地接受字母数字，删除所有标点符号（例如)(][;,和空格）。基本上，只需保留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'
解决方案 2：
对于那些正在寻找将表作为变量的方法的人来说，我从这里对同一问题的另一个答复中得到了这个：
它说了以下内容并且有效。 全部引自mhawke：
您不能使用参数替换表名。您需要自己将表名添加到查询字符串中。如下所示：
query = 'SELECT * FROM {}'.format(table)
c.execute(query)
需要注意的一件事是表名值的来源。如果该值来自不受信任的来源（例如用户），则需要验证表名以避免潜在的 SQL 注入攻击。一种方法可能是构建一个参数化查询，从 DB 目录中查找表名：
import sqlite3
def exists_table(db, name):
query = "SELECT 1 FROM sqlite_master WHERE type='table' and name = ?"
return db.execute(query, (name,)).fetchone() is not None
解决方案 3：
我不会将数据分成多个表。如果您在星号列上创建索引，则高效访问数据不会遇到任何问题。
解决方案 4：
尝试使用字符串格式：
sql_cmd = '''CREATE TABLE {}(id, column1, column2, column2)'''.format(
'table_name')
db.execute(sql_cmd)
'table_name'按照您的意愿进行替换。
解决方案 5：
正如其他答案中所说，“表不能成为参数替换的目标”，但如果您发现自己陷入了别无选择的困境，这里有一种方法可以测试所提供的表名是否有效。
注意：我把表名改成了真猪，试图涵盖所有基础。
import sys
import sqlite3
def delim(s):
delims="\"'`"
use_delim = []
for d in delims:
if d not in s:
use_delim.append(d)
return use_delim
db_name = "some.db"
db = sqlite3.connect(db_name)
mycursor = db.cursor()
table = 'so""m ][ `etable'
delimiters = delim(table)
if len(delimiters) < 1:
print "The name of the database will not allow this!"
sys.exit()
use_delimiter = delimiters[0]
print "Using delimiter ", use_delimiter
mycursor.execute('SELECT name FROM sqlite_master where (name = ?)', [table])
row = mycursor.fetchall()
valid_table = False
if row:
print (table,"table name verified")
valid_table = True
else:
print (table,"Table name not in database", db_name)
if valid_table:
try:
mycursor.execute('insert into ' +use_delimiter+ table +use_delimiter+ ' (my_data,my_column_name) values (?,?) ',(1,"Name"));
db.commit()
except Exception as e:
print "Error:", str(e)
try:
mycursor.execute('UPDATE ' +use_delimiter+ table +use_delimiter+ ' set my_column_name = ? where my_data = ?', ["ReNamed",1])
db.commit()
except Exception as e:
print "Error:", str(e)
db.close()
解决方案 6：
你可以使用类似这样的东西
`conn = sqlite3.connect()
createTable = '''CREATE TABLE %s (# );''' %dateNow)
conn.execute(createTable)`
基本上，如果我们想根据当前的日期将数据分成几个表，例如，您想根据日期监控一个系统。
createTable = '''CREATE TABLE %s (# );''' %dateNow) 意味着您创建一个带有变量 dateNow 的表，根据您的编码语言，您可以将 dateNow 定义为变量以从您的编码语言中检索当前日期。
解决方案 7：
为了避免硬编码表名，我使用了：
table = "sometable"
c = conn.cursor()
c.execute('''CREATE TABLE IF NOT EXISTS {} (
importantdate DATE,
somename VARCHAR,
)'''.format(table))
c.execute('''INSERT INTO {} VALUES (?, ?)'''.format(table),
(datetime.strftime(datetime.today(), "%Y-%m-%d"),
myname))
解决方案 8：
您可以将查询保存在 .sql 或 txt 文件中，并使用 open().replace() 方法在查询的任何部分使用变量。我是长期读者，但第一次发帖，所以如果这里有什么不对的地方，我深表歉意。
```SQL in yoursql.sql```
Sel *
From yourdbschema.tablenm
```SQL to run```
tablenm = 'yourtablename'
cur = connect.cursor()
query = cur.execute(open(file = yoursql.sql).read().replace('tablenm',tablenm))运行代码片段Hide results展开片段
解决方案 9：
那么 f-Strings 怎么样，可读性更强。
table = "myTableName"
condition = "ID = 5"
sql = f'''SELECT * FROM {table} WHERE {condition}'''
解决方案 10：
您可以将字符串作为 SQL 命令传递：
import sqlite3
conn = sqlite3.connect('db.db')
c = conn.cursor()
tablename, field_data = 'some_table','some_data'
query = 'SELECT * FROM '+tablename+' WHERE column1=\"'+field_data+"\""
c.execute(query)
