SQLite Forum

BUG: Cannot pass position parameters into UPDATE statement
Login

BUG: Cannot pass position parameters into UPDATE statement

(1) By schnipdip on 2020-08-25 00:25:34 [link] [source]

ISSUE and RESOLUTION:

https://github.com/schnipdip/Raisensu/issues/1

(2.1) By Larry Brasfield (LarryBrasfield) on 2020-08-25 00:52:27 edited from 2.0 in reply to 1 [link] [source]

In Python, this cursor.execute('''UPDATE ASSETS SET ? = '?' WHERE ID = ?''', (updateColumn, setValue, updateIndex)) cannot be expected to execute by somebody familiar with parameterization in SQL. You can use parameters to be bound later for values but not for schema elements, such as column names.

A further unjustified expectation is to imagine that the above has 3 parameters. The literal '?' is not and never will be subject to replacement via the parameter binding mechanism. It is literally a constant string containing a single question mark and nothing more.

In other words, what you experienced was bugs in your own code, not in SQLite.

(3) By Keith Medcalf (kmedcalf) on 2020-08-25 01:15:15 in reply to 2.1 [link] [source]

Use something like this perhaps:

cursor.execute('UPDATE ASSETS SET [%s] = ? WHERE ID = ?' % (updateColumn,), (setValue, updateIndex))

(5) By schnipdip on 2020-08-25 04:14:38 in reply to 3 [link] [source]

I found that method of substitution to not work. What did work was index substitution

'{0}'.format(variable)

(7.1) By Rowan Worth (sqweek) on 2020-08-25 06:40:39 edited from 7.0 in reply to 5 [link] [source]

Read Keith's answer more closely. He's using python level formatting (via the % operator) to insert the column name into the query, and leaving the column values as question marks/bound parameters to be handled by sqlite.

Note he's also dropped the single quotes you had around your second question mark, so that it is treated as a bound parameter rather than a literal string containing a question mark.

(9) By Keith Medcalf (kmedcalf) on 2020-08-25 07:11:57 in reply to 5 [link] [source]

You could use the .format method and templates as well, as in:

cursor.execute('UPDATE ASSETS SET [{0}] = ? WHERE ID = ?'.format(updateColumn), (setValue, updateIndex))

The symbol-quotes around the identifier name are to ensure that the contents of updateColumn do not contain ill-conceived characters such as those that are illegal inside identifiers (pretty much any non-alphanumeric character except _, the first character being _ or alphabetic).

(4) By schnipdip on 2020-08-25 04:03:47 in reply to 2.1 [link] [source]

if that is true, then why does this work?

conn.execute("""INSERT INTO ASSETS(NAME, LICENSE, QUANTITY, HOSTNAME)\ VALUES (?, ?, ?, ?)""",(name, key_object.encrypt(license), quantity, hostname))

Is it because the object isn't bound in SQL yet?

(6) By Rowan Worth (sqweek) on 2020-08-25 06:34:32 in reply to 4 [source]

This one works because it is a proper use of bound parameters. The purpose of the question marks is to be able to say to SQLite (or other database engine): "Hey here's a template of the query I want to run; I don't have the actual data just yet but go ahead and compile the query and I'll provide the data (via binding) later before executing it"

For this to succeed, the template query cannot have wildcards which would affect its compilation. For your INSERT INTO ASSETS statement this is true, but for your original example it is not:

UPDATE ASSETS SET ? = '?' WHERE ID = ?

If the column which is being set changes, that also changes the compiled query. Therefore the first ? is not in a valid position. ie. you can replace a literal with a ? and bind the value in later, but you cannot do this for all syntactic elements. Does that make sense?

Of course you don't really care about the compilation step, because the API you're using does the query compilation and execution all in one step. Using bound parameters still has another advantage though, because your fix in the ticket causes another bug:

sql_update = "UPDATE ASSETS SET {0} = '{1}' WHERE ID = {2}".format(updateColumn, setValue, updateIndex)

This opens you up to SQL injection attacks. If a malicious user provides a value of "haha'; DROP TABLE ASSETS; --" then you end up building an SQL query which looks like:

sql_update = "UPDATE ASSETS SET username = 'haha'; DROP TABLE ASSETS; --' WHERE ID = 12"

Which is likely to delete all your assets upon execution.

(8) By Keith Medcalf (kmedcalf) on 2020-08-25 06:56:47 in reply to 6 [link] [source]

As this comic depicts:

https://xkcd.com/327/