Inserting a table name into a query gives sqlite3.OperationalError: near “?”: syntax error

by

You cannot use SQL parameters to be placeholders in SQL objects; one of the reasons for using a SQL parameters is to escape the value such that the database can never mistake the contents for a database object.

You’ll have to interpolate the database objects separately; escape your identifiers by doubling any " double quote parameters and use

cur.execute('SELECT COUNT(Name) FROM "{}" WHERE Name=?'.format(group.replace('"', '""')), (food,))

and

cur.execute('INSERT INTO "{}" VALUES(?, ?)'.format(group.replace('"', '""')), (food, 1))

and 

cur.execute('UPDATE "{}" SET Times=? WHERE Name=?'.format(group.replace('"', '""')),
            (times_before + 1, food))

The ".." double quotes are there to properly demark an identifier, even if that identifier is also a valid keyword; any existing " characters in the name must be doubled; this also helps de-fuse SQL injection attempts.

However, if your object names are user-sourced, you’ll have to do your own (stringent) validation on the object names to prevent SQL injection attacks here. Always validate them against existing objects in that case.

You should really consider using a project like SQLAlchemy to generate your SQL instead; it can take care of validating object names and rigorously protect you from SQL injection risks. It can load your table definitions up front so it’ll know what names are legal:

from sqlalchemy import create_engine, func, select, MetaData

engine = create_engine('sqlite:////path/to/database')
meta = MetaData()
meta.reflect(bind=engine)
conn = engine.connect()

group_table = meta.tables[group]  # can only find existing tables
count_statement = select([func.count(group_table.c.Name)], group_table.c.Name == food)
count, = conn.execute(count_statement).fetchone()
if count:
    # etc.

More Related Contents:

  1. Pycharm environment different than command line
  2. Execute python script while open terminal
  3. Division in Python 2.7. and 3.3 [duplicate]
  4. How to remove \xa0 from string in Python?
  5. How can I selectively escape percent (%) in Python strings?
  6. Python MySQLdb TypeError: not all arguments converted during string formatting
  7. Extracting text from a PDF file using PDFMiner in python?
  8. Python – Extracting and Saving Video Frames
  9. Split a python list into other “sublists” i.e smaller lists [duplicate]
  10. How to get the latest file in a folder?
  11. UnicodeDecodeError: ‘ascii’ codec can’t decode byte 0xe2 in position 13: ordinal not in range(128)
  12. Add an image in a specific position in the document (.docx)?
  13. Python : How to parse the Body from a raw email , given that raw email does not have a “Body” tag or anything
  14. python ignore certificate validation urllib2
  15. Changing the options of a OptionMenu when clicking a Button
  16. too many values to unpack calling cv2.findContours
  17. How to run different python versions in cmd [duplicate]
  18. Permission problems when creating a dir with os.makedirs in Python
  19. Unable to install sasl-0.1.3 python package on windows machine
  20. “no such table” exception
  21. Using Properties in Python classes cause “maximum recursion depth exceeded” [duplicate]
  22. Screen Capture with OpenCV and Python-2.7
  23. Checking fuzzy/approximate substring existing in a longer string, in Python?
  24. pandas, melt, unmelt preserve index
  25. What are the differences between bool() and operator.truth()?
  26. Is the right-hand side of an assignment always evaluated before the assignment?
  27. Module imports and __init__.py
  28. python_2_unicode_compatible error
  29. How to filter a DataFrame column of lists for those that contain a certain item
  30. How to display charts in Spyder

Leave a Comment