Being that string substitution is frowned upon with forming SQL queries, how do you assign the table name dynamically?

by

Its not the dynamic string substitution per-se thats the problem. Its dynamic string substitution with an user-supplied string thats the big problem because that opens you to SQL-injection attacks. If you are absolutely 100% sure that the tablename is a safe string that you control then splicing it into the SQL query will be safe.

if some_condition():
   table_name="TABLE_A"
else:
   table_name="TABLE_B"

cursor.execute('INSERT INTO '+ table_name + 'VALUES (?)', values)

That said, using dynamic SQL like that is certainly a code smell so you should double check to see if you can find a simpler alternative without the dynamically generated SQL strings. Additionally, if you really want dynamic SQL then something like SQLAlchemy might be useful to guarantee that the SQL you generate is well formed.

More Related Contents:

  1. Variable table name in sqlite
  2. SQLite parameter substitution problem
  3. Importing a CSV file into a sqlite3 database table using Python
  4. How do you escape strings for SQLite table/column names in Python?
  5. How to retrieve inserted id after inserting row in SQLite using Python?
  6. python 3.2 UnicodeEncodeError: ‘charmap’ codec can’t encode character ‘\u2013’ in position 9629: character maps to
  7. How to load existing db file to memory in Python sqlite3?
  8. Sqlite insert query not working with python?
  9. Inserting a table name into a query gives sqlite3.OperationalError: near “?”: syntax error
  10. Python insert numpy array into sqlite3 database
  11. Python CSV to SQLite
  12. Python sqlite3 and concurrency
  13. What if I don’t close the database connection in Python SQLite
  14. HDF5 – concurrency, compression & I/O performance [closed]
  15. How to get a single result from a SQL query in python?
  16. Parameter unsupported when inserting int
  17. Executing an SQL query over a pandas dataset
  18. Is this Python code vulnerable to SQL injection? (SQLite3)
  19. SQLite Performance Benchmark — why is :memory: so slow…only 1.5X as fast as disk?
  20. pysqlite: Placeholder substitution for column or table names?
  21. SQLite not saving data between uses
  22. creating a database outside the application context
  23. Python SQLite3 SQL Injection Vulnerable Code
  24. Why do you need to create a cursor when querying a sqlite database?
  25. How to open and convert sqlite database to pandas dataframe
  26. sqlite3.ProgrammingError: You must not use 8-bit bytestrings unless you use a text_factory that can interpret 8-bit bytestrings
  27. SQLite, python, unicode, and non-utf data
  28. Python and SQLite: insert into table
  29. Pycharm environment different than command line
  30. Django: sudden DB reset after Heroku sleep

Leave a Comment