whybull whybull - 2 months ago 8
Python Question

How do you cleanly pass column names into cursor, Python/SQLite?

I'm new to cursors and I'm trying to practice by building a dynamic python sql insert statement using a sanitized method for sqlite3:

import sqlite3
conn = sqlite3.connect("db.sqlite")
cursor = conn.cursor()
list = ['column1', 'column2', 'column3', 'value1', 'value2', 'value3']
cursor.execute("""insert into table_name (?,?,?)
values (?,?,?)""", list)


When I attempt to use this, I get a syntax error"sqlite3.OperationalError: near "?"" on the line with the values. This is despite the fact that when I hard code the columns (and remove the column names from the list), I have no problem. I could construct with %s but I know that the sanitized method is preferred.

How do I insert these cleanly? Or am I missing something obvious?

Answer

The (?, ?, ?) syntax works only for the tuple containing the values, imho... That would be the reason for sqlite3.OperationalError

I believe(!) that you are ought to build it similar to that:

cursor.execute("INSERT INTO {tn} ({f1}, {f2}) VALUES (?, ?)".format(tn='testable', f1='foo', f1='bar'), ('test', 'test2',))

But this does not solve the injection problem, if the user is allowed to provide tablename or fieldnames himself, however.

I do not know any inbuilt method to help against that. But you could use a function like that:

def clean(some_string):
    return ''.join(char for char in some_string if char.isalnum())

to sanitize the usergiven tablename or fieldnames. This should suffice, because table-/fieldnames usually consists only of alphanumeric chars.

Perhaps it may be smart to check, if

some_string == clean(some_string)

And if False, drop a nice exception to be on the safe side.

During my work with sql & python I felt, that you wont need to let the user name his tables and fieldnames himself, though. So it is/was rarely necessary for me.

If anyone could elaborate some more and give his insights, I would greatly appreciate it.