Variable Table Name in SQLite

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'

SQLite creating table name using a variable

datetime.date.today() will return a datetime.date object which you must convert to a string, but even then a string like 2022-03-05 is not a valid name for SQLite.

You must enclose it between square brackets or backticks or double quotes.

Try this:

date_object = datetime.date.today()
sqlite_create_transfer_table = f"""CREATE TABLE IF NOT EXISTS [%s](
                                sender TEXT NOT NULL,
                                recipient TEXT NOT NULL,
                                ID text NOT NULL,
                                Size NOT NULL,
                                Colour NOT NULL,
                                Quantity INTEGER NOT NULL);""" % date_object

How to use variable for SQLite table name

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

Can I use parameters for the table name in sqlite3?

Ooookay, should have looked more thoroughly on SO.

Answers:

- SQLite Parameters - Not allowing tablename as parameter

- Variable table name in sqlite

They are meant for Python, but I guess the same applies for C++.

tl;dr:

You can't pass the table name as a parameter.

If anyone have a link in the SQLite documentation where I have the confirmation of this, I'll gladly accept the answer.

SQLite - How to use VARIABLE for TABLE NAME + VARIABLE FOR INSERT VALUES

The following line substitutes {} with the contents of the variable table and stores the result in the variable query. format works with any string:

query = 'SELECT * FROM {}'.format(table) 

On the other hand the kind of substitution done by c.execute replaces the ? in the string with the values in the list parameters:

c.execute("INSERT INTO TABLE_NAME VALUES(NULL, ?,?)", parameters)

You could combine them both to achieve the effect you want:

table = input("with what table do you want to work with? ")
query = 'INSERT INTO {} VALUES(NULL, ?, ?)'.format(table)
parameters = [order, theme]
c.execute(query, parameters)

In python, can we set table name as a variable using Sqlite3?

Parsing in table names is made to not work intentionally, since dynamically using table names in SQL queries is generally a bad idea. If you're in a situation where you end up dynamically wanting to do stuff with table names, you should think about redesigning your database and make it relational. It's hard to give specific advice about how to do this in your case, since we don't know how your database is arranged.

The other answer involves using string formatting in SQL queries. This is very bad coding practice, since it makes your code vulnerable to SQL injection. The sqlite documentation says to never do this, in fact. You don't want to have a Bobby Tables situation on your hands.

How to give Sqlite3 table name from a variable in Python

You have to bind the event based on combobox selection using application_cb.bind("<>", createTable) where you have to pass function name

application_label = Label(cbFrameRow1Col2, text='Application', bg='gray46', fg='white', font=("calibri", 10))
application_label.grid(row=0, column=0, sticky='w', padx=10, pady=5)
application_cb = ttk.Combobox(cbFrameRow1Col2, values=application_list, width=15)
application_cb.grid(row=0, column=1, sticky='w', padx=10, pady=5)
application_cb.bind("<<ComboboxSelected>>", createdb)

And now you have to call create table function when this event is triggered

def createTable(event):
    tablename = application_cb.get()
    conn = sqlite3.connect('temp.db')
    cur = conn.cursor()
    cur.execute("CREATE TABLE IF NOT EXISTS %s (id INTEGER PRIMARY KEY, Name TEXT)" % (tablename))
    conn.commit()
    conn.close()

---

Related Topics