Overwriting of variable columns in sqlite without string-based construction?

I have a JSON object containing sets of a “keyX” and the corresponding “value”.

data = {
    "key1": 10,
    "key2": 20,
    ...
}

I need to write the values into a database into the column “keyX”. Unfortunately one can’t format the SQL Query like this:

for key in data.keys():
    cur.execute('UPDATE table SET ?=? WHERE identifier=?;', (key, data[key], identifier))

Therefore I’m currently solving it like this:

for key in data.keys():
    cur.execute('UPDATE table SET ' + key + '=? WHERE identifier=?;', (data[key], identifier))

This is working perfectly, but SQL queries shouldn’t be constructed string-based. In this specific case, the keys are not set by the user, so SQL injection by the user is imo not possible,

Can this be solved better without string-based query construction?

Answer

You cannot set up placeholders for structural parts of the query, only for the slots where values are supposed to go.

That’s by design. Placeholders are supposed to protect the integrity of the SQL from maliciously crafted values, i.e. to prevent SQL injection attacks. If you could set arbitrary parts of your query from dynamic inputs, placeholders would not be able to do this job anymore.

Column names are as much a structural part of the SQL as the SELECT keyword. You need to use string interpolation to make them dynamic. Formatted strings make this quite natural:

for column, value in data:
    cur.execute(f'UPDATE table SET {column} = ? WHERE identifier = ?;', (value, identifier))

but SQL queries shouldn’t be constructed string-based.

That’s meant to be a rule for values, though. String interpolation would work here, too, and it even does not carry much of a risk when you already know the data you are processing, but it’s a bad habit and you will end up taking that shortcut one time too often. Keep using placeholders wherever it’s possible.

In this specific case, the keys are not set by the user, so SQL injection by the user is imo not possible

Correct. You can safely make parts of the SQL structure dynamic if you only use trusted parts. Placeholders are meant to guard against untrusted input.