Variable table name in sqlite
PythonDatabaseSqlitePython Problem Overview
Question: Is it possible to use a variable as your table name without having to use string constructors to do so?
Info:
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.
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.
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. :)
I'm coding in python.
Python Solutions
Solution 1 - 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'
Solution 2 - Python
For people searching for a way to make the table as a variable, I got this from another reply to same question here:
It said the following and it works. It's all quoted from mhawke:
You can't use parameter substitution for the table name. You need to add the table name to the query string yourself. Something like this:
query = 'SELECT * FROM {}'.format(table)
c.execute(query)
One thing to be mindful of is the source of the value for the table name. If that comes from an untrusted source, e.g. a user, then you need to validate the table name to avoid potential SQL injection attacks. One way might be to construct a parameterised query that looks up the table name from the DB catalogue:
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
Solution 3 - Python
I wouldn't separate the data into more than one table. If you create an index on the star column, you won't have any problem efficiently accessing the data.
Solution 4 - Python
Try with string formatting:
sql_cmd = '''CREATE TABLE {}(id, column1, column2, column2)'''.format(
'table_name')
db.execute(sql_cmd)
Replace 'table_name'
with your desire.
Solution 5 - Python
To avoid hard-coding table names, I've used:
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))
Solution 6 - Python
As has been said in the other answers, "tables can't be the target of parameter substitution" but if you find yourself in a bind where you have no option, here is a method of testing if the table name supplied is valid.
Note: I have made the table name a real pig in an attempt to cover all of the bases.
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()
Solution 7 - Python
you can use something like this
conn = sqlite3.connect() createTable = '''CREATE TABLE %s (# );''' %dateNow) conn.execute(createTable)
basically, if we want to separate the data into several tables according to the date right now, for example, you want to monitor a system based on the date.
createTable = '''CREATE TABLE %s (# );''' %dateNow) means that you create a table with variable dateNow which according to your coding language, you can define dateNow as a variable to retrieve the current date from your coding language.
Solution 8 - Python
You can save your query in a .sql or txt file and use the open().replace() method to use variables in any part of your query. Long time reader but first time poster so I apologize if anything is off here.
```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))
Solution 9 - Python
You can pass a string as the SQL command:
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)