safe way to create sqlite3 table in python

Question

For inserting into a table, the safe way is

c.execute("insert into table (?,?,?,...)",my_tuple)

But how does one create a table safely? I've tried something like this:

conn = sqlite3.connect(database)
c = conn.cursor()
cmd = "create table ? (? text,? text)"
my_tuple = ("my_table","first","second")
c.execute(cmd,my_tuple)

but I get errors like this:

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
sqlite3.OperationalError: near "?": syntax error

Should I just assemble a string in python and throw it at sqlite to create the table?

I'm assuming you're trying to create a table out of user input, right? Sigh, dunno if that's a good idea. I suppose you should validate input against table name and column name standards. I also suppose you need to block use of restricted SQL commands, so a table with the name "create" wouldn't be used. Makes sense? — Christopher Mahan, Nov 29 '10 at 21:12
execute() only use the "?" expression on values, that's why it's safe. If you let users name tables and fields that's not safe anymore. Anyway, you could add an prefix in the input. — Kabie, Nov 29 '10 at 21:41
Huh? I don't see why the creator and the viewer should ever have different levels of trust. If the safety mechanism is already there for checking code passed to sqlite, I would think it should be accessible to other types of commands like create. I'm not really worried about it in my tiny application, I just don't really how one can be expected to hardwire table creation into their code all the time. I'm just trying to import funny csv files. Maybe there's a better way? — mathtick, Nov 29 '10 at 22:04

score 3 · Accepted Answer · answered Nov 29 '10 at 22:31

3

If you're just trying to import csv files, and assuming that they're not being sent to you by some user who might try to mess with your database, just put it together in Python:

"create table %s (%s text,%s text)" % ("my_table","first","second")

If there's any chance the spreadsheets could come from a malicious user, then it's up to you to worry about sanitising the column / file names.

answered Nov 29 '10 at 22:31

Thomas K

39,200
7
84
86

sanitizing: for example, only allowing alphanums or some other sane subset of things in the user input (valid using a regex or string method). – Gregg Lind Nov 30 '10 at 01:07

score 2 · Answer 2 · edited May 23 '17 at 12:04

This answer to a similar question might help https://stackoverflow.com/a/3247553/1709198

There the answerer thought it was not possible to name tables with the question mark method and instead proposed a way to 'sanitize' inputs by defining a function to 'scrub' the input which would remove things like )(][;, so that someone can't try to throw in sql code like ); drop tables -- to mess things up.

I also don't know of any way to make the question mark method work with table names and I think this 'sanitize the string' method is about as good as it gets.

safe way to create sqlite3 table in python

2 Answers2