Python SQLite 更新错误

Question

我正在使用 python 和 sqlite 编写一些评分服务器，并且在使用更新时发生错误。

Python 2.7.11 (v2.7.11:6d1b6a68f775, Dec  5 2015, 20:40:30) [MSC v.1500 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> from server import *
>>> db = DB_control()
>>> db.update_user_score("ZSPEF1", "FXVCWI", 180)
UPDATE score SET FXVCWI = 180 WHERE USER_ID = ZSPEF1
Error raised while updating ID ZSPEF1's score to 180. Rolling back DB...
DB Successfully rolled back
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "server.py", line 102, in update_user_score
    musica_db.execute(update_score_str)
sqlite3.OperationalError: no such column: ZSPEF1
>>>

分数表如下所示:

如您所见，有 FXVCWI 列和 ZSPEF1 行，并且想要更改该值，但错误表明没有 ZSPEF1 列。
UPDATE命令仅在update_user_score函数上发生错误。

为什么我会遇到这种情况？另外，当字符串中的第一个字符是数字时，有时会出现错误。有什么办法可以防止这个错误吗？

这是我的代码

#!/usr/bin/env python
import sqlite3

musica_db_file  = sqlite3.connect("musica.db")
musica_db  = musica_db_file.cursor()

class DB_control(object):
    def setupDB(self):
        #This function should execute only on first run.
        try:
            userDB_setupDB_str   = "NUM         INTEGER PRIMARY KEY AUTOINCREMENT, "
            userDB_setupDB_str  += "CARD_ID     TEXT NOT NULL UNIQUE, "
            userDB_setupDB_str  += "NAME        TEXT NOT NULL UNIQUE, "
            userDB_setupDB_str  += "PASSWORD    TEXT NOT NULL, "
            userDB_setupDB_str  += "ADMIN       INT  NOT NULL DEFAULT 0"

            songDB_setupDB_str   = "NUM         INTEGER PRIMARY KEY AUTOINCREMENT, "
            songDB_setupDB_str  += "SONG_ID     INT  NOT NULL UNIQUE, "
            songDB_setupDB_str  += "NAME        TEXT NOT NULL UNIQUE, "
            songDB_setupDB_str  += "FINGERPRINT TEXT NOT NULL UNIQUE"

            scoreDB_setupDB_str  = "USER_ID     TEXT NOT NULL UNIQUE"

            musica_db.execute('CREATE TABLE user({0}) '.format(userDB_setupDB_str))
            musica_db.execute('CREATE TABLE song({0}) '.format(songDB_setupDB_str))
            musica_db.execute('CREATE TABLE score({0})'.format(scoreDB_setupDB_str))
            musica_db_file.commit()

            self.add_user(randomID(), 'MU_Admin', 'yj809042', admin=True) #Create admin account.
            self.add_song(randomID(), 'Tutorial', randomID()) #Create tutorial(dummy) song
            print("DB setuped.")
        except:
            print("Error raised while setuping DB")
            raise
    def update_user_score(self, cardID, songID, score):
        try:
            update_score_str = "UPDATE score SET {0} = {1} WHERE USER_ID = {2}".format(songID, score, cardID)
            print update_score_str
            musica_db.execute(update_score_str)
            musica_db_file.commit()
            print("User ID {0}'s score is now {1}.".format(cardID, score))
        except:
            print("Error raised while updating ID {0}'s score to {1}. Rolling back DB...".format(cardID, score))
            self.rollback_DB()
            raise
    def rollback_DB(self):
        try:
            musica_db_file.rollback()
            musica_db_file.commit()
            print("DB Successfully rolled back")
        except:
            print("Error raised while rolling back DB. Critical.")
            raise

Answer 1

您正在将列值插值为 SQL 对象名称，不带引号:

update_score_str = "UPDATE score SET {0} = {1} WHERE USER_ID = {2}".format(songID, score, cardID)
musica_db.execute(update_score_str)

不要对 SQL值使用字符串插值。使用绑定(bind)参数代替:

update_score_str = "UPDATE score SET {0} = ? WHERE USER_ID = ?".format(songID)
musica_db.execute(update_score_str, (score, cardID))

然后，cursor.execute() 函数将处理正确的引用，从而降低 SQL 注入(inject)的风险。

即使插入 SQL 对象名称(这里是 songID)也是不可靠的；请确保预先验证该字符串。

看起来好像您正在为每首歌曲创建一个列。您可能想阅读更多有关正确关系表设计的信息，并且不将数据存储在列名中。请改用 user_song_scores 多对多表，该表存储 (USER_ID, SONG_ID, SCORE) 元组，让您可以使用 更新给定歌曲的乐谱更新 user_song_scores SET 分数=？哪里 USER_ID=？ AND SONG_ID=? 相反，无需生成列名称。