Variable table name in sqlite

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'

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

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.

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.

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

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.

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

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

What about f-Strings, much more readable.

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

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)

• python
• database
• sqlite