Use placeholder for table name in python sqlite3

Question

I was wondering if there was a way to use the '?' to create a table with a different name every time in a function. Here's my code:

@bot.tree.command(name='create', description='Crea un nuovo torneo')
@app_commands.describe(name='Il nome del torneo', max_participants='Numero massimo di partecipanti', 
                       date='Data del torneo(mm:hh:dd:MM:yyyy)', mode='Modalità del torneo', 
                       mappa='Mappa del torneo')
async def create(interaction:discord.Interaction, name:str, max_participants:int, date:str, 
                 mode:str, mappa:str):
    date = datetime.datetime(year=int((date.split(':'))[4]), month=int((date.split(':'))[3]), 
                             day=int((date.split(':'))[2]), hour=int((date.split(':'))[1]), 
                             minute=int((date.split(':'))[0]), second=0)
    conn = sqlite3.connect('tourneys.sqlite3')
    c = conn.cursor()
    c.execute('CREATE TABLE IF NOT EXISTS (?) (id INTEGER PRIMARY KEY, max_participants INTEGER, date DATETIME, mode TEXT, map TEXT)',
              (name, )) # error here
    t_id = (random.randint(0,9) for _ in range(9))
    c.execute('INSERT INTO ? VALUES (?, ?, ?, ?, ?)', (name, t_id, max_participants, date, mode, mappa))
    c.execute('SELECT * FROM ? WHERE id = ?', (name, t_id))
    t = c.fetchone()
    await interaction.response.send_message(t)

Error: Command 'create' raised an exception: OperationalError: near "(": syntax error

PS: I know you can't use placeholders columns/tables names but I have seen someone doing something like .'''format and I didn't really understand what was going on.

I tried to use placeholders in my table name and I expected that it created a table with the placeholder name.

Does this answer your question? [Variable table name in sqlite](https://stackoverflow.com/questions/3247183/variable-table-name-in-sqlite) — Michael Ruth, May 02 '23 at 16:41
Looks like it's impossible to use parameter binding for table names. Be careful with string substitution, especially when the table name is what looks like a user-defined string. Injection is the obvious risk, but there could be all sorts of illegal characters in `name`. Sanitization is mandatory, but naively removing illegal characters could result in name collision, i.e. two unique names which, when stripped of illegal characters, result in the same table name. — Michael Ruth, May 02 '23 at 16:45

score 0 · Answer 1 · answered May 02 '23 at 16:40

of course you can use f string instead.

instead of the following code:

c.execute('CREATE TABLE IF NOT EXISTS (?) (id INTEGER PRIMARY KEY, max_participants INTEGER, date DATETIME, mode TEXT, map TEXT)',
              (name, )) # error here

use this code:

table_name = 'some name assigned in run time'
# if the table name is coming from user you should causes of SQL injection. avoid harmness using .qoute() method like following
table_name = sqlite3.qoute(table_name)

c.execute(f'CREATE TABLE IF NOT EXISTS {table_name} (id INTEGER PRIMARY KEY, max_participants INTEGER, date DATETIME, mode TEXT, map TEXT)',
                  (name, ))

This code generates an exception: `sqlite3.ProgrammingError: Incorrect number of bindings supplied.` The second argument to `c.execute()`, `(name, )`, should be removed. — Michael Ruth, May 02 '23 at 16:48

Use placeholder for table name in python sqlite3

1 Answers1