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?
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.