SQLite3 query ORDER BY parameter with ? notation

Question

I am trying to make a query with sqlite3 in python, to be ordered by a parameter column "overall_risk" or "latest_risk" (which is double number)

param = ('overall_risk',)
cur = db.execute("SELECT * FROM users ORDER BY ? DESC", param)

However, I doesn't return the data ordered by "overall_risk" (or "latest_risk"), moreover, when I write the query like:

"SELECT * FROM users ORDER BY " + 'overall_risk' + " DESC"

it does work.

Answer 1

SQL parameters can't be used for anything other than values. The whole point in using a placeholder is to have the value properly quoted to avoid it from being interpreted as part of a SQL statement or as an object.

You are trying to insert an object name, not a value, so you can't use ? for this. You'll have to use string formatting and be extremely careful as to what names you allow:

cur = db.execute("SELECT * FROM users ORDER BY {} DESC".format(column_name))

If column_name is taken from user input, you'll need to validate this against existing column names.

The alternative is to use a library like SQLAlchemy to generate your SQL for you. See the SQL Expression Language tutorial.