Sqlite3 Updating Row Defined by a Variable

问题:

Not sure if I phrased the title correctly, but basically my question is is it possible to have sqlite update a row which is defined by a variable? For example:

db.execute('''UPDATE CUSTOMER SET ? = ? WHERE CUSTOMER_ID = ?''', (title, info.get(), k))

where 'title' (the first question mark) is the name of the 'row' I want to update within the table Customer. I have tried the above code but it doesn't work. Does anybody know if it is possible to do this with sqlite3 in any way?

回答1:

SQL parameters are designed to never be interpretable as SQL objects (like column names); that is one of their major usecases. If they didn't they wouldn't prevent SQL injection attacks. Instead, the title value is either properly escaped as a value, or rejected altogether as the syntax doesn't allow a value in that location.

As such, you need to make sure that your title variable is a proper SQL object name (never take user input directly here) and use string formatting for just that value:

db.execute(
    '''UPDATE CUSTOMER SET {} = ? WHERE CUSTOMER_ID = ?'''.format(title),
    (info.get(), k))

You probably want to match title against a pre-defined set of possible column names first.

回答2:

Can you try like this

query = "UPDATE CUSTOMER SET %s = '%s' WHERE CUSTOMER_ID = %d" %(title, info.get(), k)
db.execute(query)

May be you need to commit it.