SQLite table name parameter

Question

I'm trying to replace some of my string insertions with parameters. So I have this code that executes the query:

cursor.execute("DELETE FROM %s WHERE COL=%s" % ("tablename","column"))

I can replace it with

cursor.execute("DELETE FROM tablename WHERE COL=?" , ("column"))

But I want my tablename to be in a variable. How can I protect insertion of a variable for a table from sql injections?

• jheyse: How about whitelisting, as explained here?
• Kalenda: You have to sanitized your parameters before passing it down to the database. White list your table names as explained in @jheyse 's link. Or create some sort of a mapping with your acceptable table names and only execute your query if there is match to your acceptable values.
• akalikin: @jheyse The only reason that I have table name in a variable is that it's not hardcoded into every query. So it's being read from a config file at the moment. Having a config file and then checking that variable against a list of accepted names seems slightly redundant
• jheyse: My suggestion is that the list is also dynamic like @ezig 's example in the answer below pointed to. This solution is also given in the link that I suggested above (look at the end of the answer).

Answer 1

If your goal is to make sure that the variable is the name of a valid table, you can get a list of table names using

SELECT name FROM sqlite_master WHERE type='table'

And then check to see if the variable from the config file matches one of the tables. This avoids having to hardcode a list of tables.