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.