Writing a secure python function to create a table in sqlite3

I’m trying to write a variadic function for a database class that creates a table, with the name and arbitrary number of columns (along with their data types) being passed as arguments.

Here is a MWE (with the database class removed) that accomplishes this goal by directly plugging the arguments into f-strings:

import sqlite3

def create_table(cur, conn, table_name, *columns):
    column_string = ""

    # concatenate the list of column names, along with their types
    for idx, pair in enumerate(columns):
        column_string += f"{pair[0]} {pair[1]},"

    # this adds one too many commas, so remove the trailing one
    column_string = column_string[:-1]

    sql = f"CREATE TABLE {table_name}({column_string})"

    cur.execute(sql)
    conn.commit()

# fire up test database
connection = sqlite3.connect(":memory:")
cursor = connection.cursor()

# create a simple table
create_table(cursor, connection, "employees", ("name", "text"), ("age", "int"))

# test to make sure it worked correctly
cursor.execute("PRAGMA table_info(employees)")
print(cursor.fetchall())

and the output is exactly what I hoped for

[(0, 'name', 'TEXT', 0, None, 0), (1, 'age', 'INT', 0, None, 0)]

After trying to make adjustments to this function to use placeholders instead, I found out here that using place-holders for table names and/or column names is not allowed. That is, the line

cursor.execute("""CREATE TABLE :name (:col1 :col1type, :col2 :col2type)""", {
    'name': "employees",
    'col1': "name",
    'col1type': "text",
    'col2': "age",
    'col2type': "int"
})

yields an error:

Traceback (most recent call last):
  File "/Users/doggo/casino/databse3.py", line 45, in <module>
    cursor.execute("""CREATE TABLE :name (:col1 :col1type, :col2 :col2type)""", {
sqlite3.OperationalError: near ":name": syntax error

Is there a secure way to re-write my function to maintain its flexibility while avoiding vulnerabilities to injection attacks?

  • As is often the case, now that i’ve typed my question out, I’m starting to think it might be stupid. Perhaps the best solution is to just stick with using f-strings, but add some checks to the parameters before creating the table to make sure the input isn’t suspicious.

    – 

  • You can’t use SQL parameters for identifiers or other syntax. Parameters only work in place of a value literal, like a numeric literal or quoted string literal.

    – 

  • @BillKarwin thank you for your response, but I’m having trouble following your meaning, could you elaborate a bit?

    – 

  • I can refer you to my answer here: stackoverflow.com/a/63197309/20860 Or my book, SQL Antipatterns Volume 1: Avoiding the Pitfalls of Database Programming. There’s a full chapter on SQL injection in the book.

    – 

Leave a Comment