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.

Answer 5

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))

Answer 6

What about f-Strings, much more readable.

table = "myTableName"
condition = "ID = 5"
sql = f'''SELECT * FROM {table} WHERE {condition}'''

Answer 7

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()

Answer 8

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.

Answer 9

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))

Answer 10

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)