How do you escape strings for SQLite table/column names in Python?

by

The psycopg2 documentation explicitly recommends using normal python % or {} formatting to substitute in table and column names (or other bits of dynamic syntax), and then using the parameter mechanism to substitute values into the query.

I disagree with everyone who is saying “don’t ever use dynamic table/column names, you’re doing something wrong if you need to”. I write programs to automate stuff with databases every day, and I do it all the time. We have lots of databases with lots of tables, but they are all built on repeated patterns, so generic code to handle them is extremely useful. Hand-writing the queries every time would be far more error prone and dangerous.

It comes down to what “safe” means. The conventional wisdom is that using normal python string manipulation to put values into your queries is not “safe”. This is because there are all sorts of things that can go wrong if you do that, and such data very often comes from the user and is not in your control. You need a 100% reliable way of escaping these values properly so that a user cannot inject SQL in a data value and have the database execute it. So the library writers do this job; you never should.

If, however, you’re writing generic helper code to operate on things in databases, then these considerations don’t apply as much. You are implicitly giving anyone who can call such code access to everything in the database; that’s the point of the helper code. So now the safety concern is making sure that user-generated data can never be used in such code. This is a general security issue in coding, and is just the same problem as blindly execing a user-input string. It’s a distinct issue from inserting values into your queries, because there you want to be able to safely handle user-input data.

So my recommendation is: do whatever you want to dynamically assemble your queries. Use normal python string templating to sub in table and column names, glue on where clauses and joins, all the good (and horrible to debug) stuff. But make sure you’re aware that whatever values such code touches has to come from you, not your users[1]. Then you use SQLite’s parameter substitution functionality to safely insert user-input values into your queries as values.

[1] If (as is the case for a lot of the code I write) your users are the people who have full access to databases anyway and the code is to simplify their work, then this consideration doesn’t really apply; you probably are assembling queries on user-specified tables. But you should still use SQLite’s parameter substitution to save yourself from the inevitable genuine value that eventually contains quotes or percent signs.

More Related Contents:

  1. Variable table name in sqlite
  2. Sqlite / SQLAlchemy: how to enforce Foreign Keys?
  3. Django: Deploying an application on Heroku with sqlite3 as the database
  4. Parameter substitution for a SQLite “IN” clause
  5. How to load existing db file to memory in Python sqlite3?
  6. Sqlite insert query not working with python?
  7. Python SQLite parameter substitution with wildcards in LIKE
  8. Inserting a table name into a query gives sqlite3.OperationalError: near “?”: syntax error
  9. How can I get dict from sqlite query?
  10. Using a WHERE ___ IN ___ statement
  11. parameter unsupported when inserting int in sqlite
  12. How to read datetime back from sqlite as a datetime instead of string in Python?
  13. Python sqlite3 string variable in execute
  14. Why is SQLAlchemy insert with sqlite 25 times slower than using sqlite3 directly?
  15. SQLite parameter substitution and quotes
  16. Python SQLite: database is locked
  17. Bulk insert huge data into SQLite using Python
  18. How to discover table properties from SQLAlchemy mapped object
  19. String similarity with Python + Sqlite (Levenshtein distance / edit distance)
  20. Auto Increment on Composite Primary Key – Sqlite3 + Python
  21. sqlite3.OperationalError: unable to open database file
  22. Being that string substitution is frowned upon with forming SQL queries, how do you assign the table name dynamically?
  23. How to upgrade sqlite 3.8.2 to >= 3.8.3
  24. creating a database outside the application context
  25. How to delete a table in SQLAlchemy?
  26. Python and SQLite: insert into table
  27. Convert JSON to SQLite in Python – How to map json keys to database columns properly?
  28. How to check the existence of a row in SQLite with Python?
  29. sqlite3.OperationalError: no such column – but I’m not asking for a column?
  30. Django: sudden DB reset after Heroku sleep

Leave a Comment