Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
771 views
in Technique[技术] by (71.8m points)

python - 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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...