Variable table name in sqlite

Question

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.

Answer 1

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' 

Answer 2

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 

Answer 3

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.

Answer 4

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.