SQLite3 query ORDER BY parameter with ? notation
<p>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)</p>

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

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

<pre><code>"SELECT * FROM users ORDER BY " + 'overall_risk' + " DESC"
</code></pre>

<p>it does work. </p>

<p>SQL parameters can't be used for anything other than <em>values</em>. 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.</p>

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

<pre><code>cur = db.execute("SELECT * FROM users ORDER BY {} DESC".format(column_name))
</code></pre>

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

<p>The alternative is to use a library like <a href="http://www.sqlalchemy.org/" rel="nofollow">SQLAlchemy</a> to generate your SQL for you. See the <a href="http://docs.sqlalchemy.org/en/rel_1_0/core/tutorial.html" rel="nofollow">SQL Expression Language tutorial</a>.</p>

python sqlite3