1

I'm writing some scoring server using python and sqlite, and error occured when using update.

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

score table looks like this: score table screenshot

As you see, there are FXVCWI column and ZSPEF1 row and want to change that value, but error says there are no ZSPEF1 column.
UPDATE command only occurs error on update_user_score function.

Why this happens to me? Also, error sometimes occurs when first character in string is number. Is there any way to prevent this error?

Here is my code

#!/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
6
  • I think you need quotation marks around user_id Commented Jul 24, 2016 at 13:39
  • @fodma1 That didn't work for me. "UPDATE score SET {0} = {1} WHERE 'USER_ID' = {2}".format(songID, score, cardID) Commented Jul 24, 2016 at 13:41
  • @LeeM.U. not like that. I meant the actual id"UPDATE score SET {0} = {1} WHERE USER_ID = '{2}'" Commented Jul 24, 2016 at 13:53
  • Side note: you apparently add a new column for each new song. That's not like the relational Dbs are supposed to be used. You probably may want to read about many to many relationship Commented Jul 24, 2016 at 13:59
  • @robyschek Oh, i should search about that. Thanks a lot! Commented Jul 24, 2016 at 14:11

1 Answer 1

3

You are interpolating column values as SQL object names, with no quoting:

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

Don't use string interpolation for SQL values. Use bind parameters instead:

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

The cursor.execute() function will then take care of proper quoting, reducing your risk of SQL injection.

Even interpolating SQL object names (songID here) is dodgy; do make sure you validate that string up front.

It looks as if you are creating a column per song. You probably want to read up some more about proper relational table design and not store data in column names. Use a user_song_scores many-to-many table instead, where that table stores (USER_ID, SONG_ID, SCORE) tuples, letting you update scores for a given song with UPDATE user_song_scores SET score=? WHERE USER_ID=? AND SONG_ID=? instead, removing the need to generate column names.

Sign up to request clarification or add additional context in comments.

1 Comment

Thank you! This worked for me. I'm new to DB so i didn't know about many-to-many table. Maybe i should re-write my code ;)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.