sqlite3 in Python: is manual string formatting safe for selecting or updating variable columns?
<p>It is well known that one shouldn't assemble SQL queries using native string operations, but instead use prepared statements with placeholders for variables. The sqlite3 in Python handle this this way:</p>

<pre><code>c.execute('UPDATE stock SET price = ? WHERE symbol = ?', the_price, the_symbol)
</code></pre>

<p>The limit of this approach is that there is no way to set parameters for the columns that are selected or updated. In the above example, the price column is updated. However, other requests would update another column in the same fashion. Each of these requests would be pretty much the same except for the name of the column. Therefore, in respect to DRY (Don't Repeat Yourself), it becomes very tempting to do the following:</p>

<pre><code>if my_col in LIST_OK_COLUMNS:
    c.execute('UPDATE stock SET {} = ? WHERE symbol = ?'.format(my_col), the_symbol, the_price)
</code></pre>

<p>Still, this is a query being built using native string operations, which is unsafe according to the documentation.</p>

<p>What is the most standard way to do this properly?</p>

<p>If any of the strings you're inserting directly into the SQL statement might be controlled by the user, you have a problem.</p>

<p>For values, you can use SQL parameters.</p>

<p>There is no such mechamism for table/column names (because asking the user for these names usually does not happen; SQL was designed before there was the evil internet with half of the app running on the untrusted client).</p>

<p>To make a column name such as <code>price = -100 WHERE 1 OR symbol = ? --</code> harmless, you have to properly escape it:</p>

<pre><code>def sql_escape_identifier(s):
    return '"' + s.replace('"', '""') + '"'

c.execute('UPDATE stock SET {} = ...'.format(sql_escape_identifier(my_col)), ...
</code></pre>

python sqlite3