4

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?

akalikin
  • 1,071
  • 12
  • 35
  • 1
    How about _whitelisting_, as explained [here](http://stackoverflow.com/questions/22544335/avoiding-sql-injection-in-sqlite3#22544694)? – jheyse Jul 27 '15 at 14:57
  • 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. – Kalenda Jul 27 '15 at 15:06
  • @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 – akalikin Jul 27 '15 at 15:10
  • 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). – jheyse Jul 27 '15 at 16:29

1 Answers1

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.

ezig
  • 1,219
  • 1
  • 10
  • 15