pysqlite: Placeholder substitution for column or table names?

by

You simply can not use placeholders for column or table names. I don’t have a authoritative citation for this — I “know” this only from having tried it and from failing. It makes some sense though:

  • If the columns and table could be parametrized, there would be little
    purpose to preparing (execute-ing) the SQL statement before fetching, since all parts of the statement could be
    replaced.
  • I’m not sure about pysqlite1, but MySQLdb automatically quotes all
    string parameters. Column and table names should not be quoted. So it
    would complicate the parsing required by the driver if it had to
    decide if a placeholder represented a column or table name versus a
    value that needs quoting.

In short, you’ve found the right way — use string formating.

c.execute('SELECT {} FROM {} WHERE id=?'.format(column, table), row))

1 Not all drivers quote parameters — oursql doesn’t, since it sends SQL and arguments to the server separately.

More Related Contents:

  1. How can I get dict from sqlite query?
  2. Using a WHERE ___ IN ___ statement
  3. Python SQLite: database is locked
  4. How to get a single result from a SQL query in python?
  5. Is this Python code vulnerable to SQL injection? (SQLite3)
  6. What is an efficient way of inserting thousands of records into an SQLite table using Django?
  7. Python SQLite3 SQL Injection Vulnerable Code
  8. Python and SQLite: insert into table
  9. How to check the existence of a row in SQLite with Python?
  10. Select Query To Get Last 9 Months Data in SQLite3
  11. No module named _sqlite3
  12. Sqlite / SQLAlchemy: how to enforce Foreign Keys?
  13. Force Python to forego native sqlite3 and use the (installed) latest sqlite3 version
  14. How to create a large pandas dataframe from an sql query without running out of memory?
  15. Passing table name as a parameter in psycopg2
  16. Getting the SQL from a Django QuerySet [duplicate]
  17. Python SQLite parameter substitution with wildcards in LIKE
  18. Django: Adding “NULLS LAST” to query
  19. return SQL table as JSON in python
  20. Python sqlite3 string variable in execute
  21. How to discover table properties from SQLAlchemy mapped object
  22. String similarity with Python + Sqlite (Levenshtein distance / edit distance)
  23. Auto Increment on Composite Primary Key – Sqlite3 + Python
  24. How to see the raw SQL queries Django is running?
  25. pyodbc insert into sql
  26. sqlite3.OperationalError: unable to open database file
  27. How to get rows which match a list of 3-tuples conditions with SQLAlchemy
  28. SQL join or R’s merge() function in NumPy?
  29. How to upgrade sqlite 3.8.2 to >= 3.8.3
  30. Passing SQLite variables in Python

Leave a Comment