SQLite3 Formatting Issue with Parameters - Updating an existing value by adding another value

Question

I am currently in the process of learning how to use the sqlite3 module inside Python. I'm trying to write code that will update a value from a column in sqlite by adding a new value to an existing value, but I'm running into formatting issues that I don't know how to fix...

I know that the basic syntax for doing this (as far as I'm aware) is the following:

sql_statement = "UPDATE table 
                 SET column_name = column_name + new_value 
                 WHERE condition;"

The issue I'm running into is that I'm trying to insert the column name using dictionary parameters from python. This is where the problem is occuring in my code:

muscle = 'Lats'
num_sets = 5
week_num = 40

sql_statement = f"UPDATE WeeklyMuscleSets 
                 SET {muscle} = (:muscle) + (:num_sets) 
                 WHERE WeekID = (:week_num);"

parameters = {'muscle':muscle,'num_sets':num_sets,'week_num':week_num}

c.execute(sql_statement,parameters)

The code runs just fine, but instead of adding to the existing value in the table it is replacing it. I did some testing, and when I manually enter the value for the "muscle" variable, it updates just fine.

muscle = 'Lats'
num_sets = 5
week_num = 40

sql_statement = f"UPDATE WeeklyMuscleSets 
                 SET {muscle} = Lats + (:num_sets) 
                 WHERE WeekID = (:week_num);"

parameters = {'num_sets':num_sets,'week_num':week_num}

c.execute(sql_statement,parameters)

It also worked when I used f-string formatting for the muscle variable, however I know that is bad practice and leaves the code vulnerable to injection attacks.

For some reason when I use the parameters it doesn't add the new value to the existing one and instead replaces it, and I'm at a loss as to why. Any help with this is greatly appreciated!

Table and column names have to be directly present in the statement; they can't be bound as parameters. — Shawn, Oct 01 '19 at 15:15
https://stackoverflow.com/questions/33019599/sqlite-why-cant-parameters-be-used-to-set-an-identifier — Shawn, Oct 01 '19 at 15:17

Mostafa Vatanpour · Accepted Answer · 2019-10-01T15:28:39.543

0

This is parameterized query. Then the query and values are sent to the DBMS engin separately and the DBMS use the values in the parsed query. Then the parsed query stored and used again so the performance improved and SQL injection can not be happened.

If you want to add num_sets to the current value of muscle you must use muscle + :num_sets :

num_sets = 5
week_num = 40

sql_statement = f"UPDATE WeeklyMuscleSets 
                 SET muscle = muscle + :num_sets
                 WHERE WeekID = :week_num;"

parameters = {'num_sets':num_sets,'week_num':week_num}

c.execute(sql_statement,parameters)

edited Oct 01 '19 at 15:28

answered Oct 01 '19 at 15:14

Mostafa Vatanpour

1,328
13
18

Thank you for your reply! So basically it's like what Shawn said in that I cannot use parameters for table or column names? So using f-string formatting is acceptable for retrieving the column name and is not bad practice? – Brandon M Oct 01 '19 at 15:34
@BrandonM Yes Shawn is right. It is not bad practice and in fact it is necessary. – Mostafa Vatanpour Oct 01 '19 at 15:37

score 0 · Answer 2 · answered Oct 01 '19 at 15:21

Are you sure your code runs fine? According to this post and this one, it isn't possible to use named placeholders for table names or column names. You are trying to use a named placeholder for a column so I would expect an exception to be raised.

Two ways around the problem spring to mind:

Use string {formatting}. As you point out, this has the downside of allowing SQL injection attacks. However, it is possible to sanitise data from the user. If security is that much of a concern, you should probably be using some kind of ORM instead of raw SQL.

Re-structure your table so that it looks more like:

create WeeklyMuscleSets (
    muscle varchar,
    number_of_sets number,
    week number
)

and your query to look more like this:

update WeeklyMuscleSets 
set number_of_sets = number_of_sets + :extra_sets
where muscle = :muscle

Basically, when I tried to use a named placeholder for the column name it would raise a syntax error. When ordered like this and using an f-string: {muscle} = (:muscle) + (:num_sets) didn't raise any error, but obviously any existing value for the muscle was being ignored when the code ran. Thank you for your reply and suggestion! — Brandon M, Oct 01 '19 at 15:46

SQLite3 Formatting Issue with Parameters - Updating an existing value by adding another value

2 Answers2