What is the correct syntax for creating a table name as variable?

Question

The user can generate a table to rank songs. It checks if there is a ranking system for the user already, if not it creates a new table. I want the name of the system to correlate with the user id.

@app.route("/setup", methods=["GET", "POST"])
def setup():
    if request.method == "POST":
        user_id = str(session["user_id"])
        system_info = ()

        # Find or create system table
        while not system_info:
            cur.execute("SELECT system_name, ready, modified FROM systems WHERE user_id = ?", user_id)
            system_info = cur.fetchone()
            if not system_info:
                system_name = "system" + user_id
                cur.execute("INSERT INTO systems (user_id, system_name) VALUES (?, ?)", (user_id, system_name))
                cur.execute(
                    """
                    CREATE TABLE ? (
                    attribute_name TEXT NOT NULL,
                    type TEXT NOT NULL,
                    min INTEGER NOT NULL DEFAULT(0),
                    max INTEGER NOT NULL DEFAULT(0),
                    length_depend BOOLEAN DEFAULT(FALSE)
                    )
                    """
                    , system_name)

I am getting this from the line for creating the table:

sqlite3.OperationalError: near "?": syntax error

I tried system_name = str("system" + user_id") and put system_name in tuple format as final parameter to cur.execute. I got the same error message with both changes.

Answer 1

Placeholders are not supported for table names in queries. From my understanding this is safe given that system_name and its component are not user-supplied:

if request.method == "POST":
    user_id = str(session["user_id"])
    system_info = ()

    # Find or create system table
    while not system_info:
        cur.execute("SELECT system_name, ready, modified FROM systems WHERE user_id = ?", user_id)
        system_info = cur.fetchone()
        if not system_info:
            system_name = "system" + user_id
            query = """
                CREATE TABLE {} (
                attribute_name TEXT NOT NULL,
                type TEXT NOT NULL,
                min INTEGER NOT NULL DEFAULT(0),
                max INTEGER NOT NULL DEFAULT(0),
                length_depend BOOLEAN DEFAULT(FALSE)
                )
                """.format(system_name)
            cur.execute("INSERT INTO systems (user_id, system_name) VALUES (?, ?)", (user_id, system_name))
            cur.execute(query)