Python Sqlite3 Database table isn't being updated

Question

I'm creating a change-password page for a website, which requests the new password and the current password. The old password is hashed and salted using the scrypt library then compared to the password stored in the sqlite3 database, and if these are a match, the new password is hashed and the database is updated. However I am having difficulty executing the update command, as it throws a sqlite3.OperationalError: unrecognised token: "\" error. The execute statement currently has the following code:

c.execute("UPDATE users SET password = \'{0}\' WHERE memberID = \'{1}\'".format(newPas, memID))

Initially we believed this error to have been caused by the use of ' in the string formatting due to the presence of ' within the new password itself, so this was run again as:

c.execute("UPDATE users SET password = \"{0}\" WHERE memberID = \"{1}\"".format(newPas, memID))

This successfully runs, but doesn't actually change anything in the database. We also attempted to create a query string and then execute the string.

query = "UPDATE users SET password = {0} WHERE memberID = {1}".format(newPas, memID)
c.execute(query)

This caused a sqlite3.OperationalError: near "'\xa1\x91\x9f\x88\xfb\x81\x12\xd4\xc2\xf9\xce\x91y\xf0/\xe1*#\x8aj\xc7\x1d\xd3\x91\x14\xcb\xa4\xabaP[\x02\x1d\x1b\xabr\xc7\xe4\xee\x19\x80c\x8e|\xc0S\xaaX\xc6\x04\xab\x08\x9b\x8e\xd7zB\xc6\x84[\xfb\xbc\x8d\xfc'": syntax error. I believe that this is caused by the presence of ' and " characters within the password, but I am unsure how to get around this issue as these are added by the hashing process and thus removing them would change the password. The password I would like to add is:

b'\xa1\x91\x9f\x88\xfb\x81\x12\xd4\xc2\xf9\xce\x91y\xf0/\xe1*#\x8aj\xc7\x1d\xd3\x91\x14\xcb\xa4\xabaP[\x02\x1d\x1b\xabr\xc7\xe4\xee\x19\x80c\x8e|\xc0S\xaaX\xc6\x04\xab\x08\x9b\x8e\xd7zB\xc6\x84[\xfb\xbc\x8d\xfc'

I was wondering if anyone could share some insights into why it isn't liking the "\" character or why it isn't updating the database, and point me in the right direction to making it work. If you need more information or code snippets or just want to yell at me, please don't hesitate to! Thank you in advance :)

A couple of more general things. You should not format queries this way, you should use the sqlite placeholder e.g. query = "UPDATE users SET password = ? WHERE memberID = ?" and then c.execute(query, (newPas, memID)). Also, I assume you commit() the changes? I don't know if this would fix your problem, but you definitely should not use format to create a query as you're liable to sql injection. — roganjosh
– roganjosh, Commented Sep 20, 2016 at 7:42
On a serious note, (one of) your problem(s) in the above snippet is formatting a bytes object in to your query string. Passing it directly as-is to format() will effectively just inline the representation of the bytes object in to the query, which is definitely not what you want. Not to mention that formatting query strings from values like that is a huge no-no. As others have already pointed out, use placeholders. — Ilja Everilä
– Ilja Everilä, Commented Sep 20, 2016 at 8:10
Oh my I feel like such a fool I forgot to commit the changes, thank you so much for pointing it out roganjosh! I sanitize my inputs before they get to this stage, but is it still better to do it as you said? Thank you thank you thank you! :) — Igneel32
– Igneel32, Commented Sep 20, 2016 at 8:15
@Igneel32 since the issue was actually commiting the changes from my initial comment, I will write up an answer now so that we can close this — roganjosh
– roganjosh, Commented Sep 20, 2016 at 8:33

roganjosh · Accepted Answer · 2016-09-20 09:05:21Z

A couple of things with your code:

You should not use format to build your queries like this. This leaves you liable to SQL injection and, whilst you might sanitise your inputs in this case, it's a bad habit that will bite you.
All changes need to be committed to the database to actually take effect. This is why your second query did not throw an error but equally did not make any changes to the database.

The correct formatting of this query would be:

conn = sqlite3.connect('my_db.db')
c = conn.cursor()

query = "UPDATE users SET password = ? WHERE memberID = ?"
c.execute(query, (newPas, memID))

conn.commit() # To finalise the alteration

As a side note, the cursor expects a tuple in this case, so a common stumbling block comes when passing single values:

query = "UPDATE users SET password = ? WHERE memberID = 'abc'"
c.execute(query, (newPas)) # Throws "incorrect number of bindings" error

# Use this instead i.e. pass single value as a tuple
c.execute(query, (newPas,))

You could use format to create variable field names in a query, since placeholders are not allowed in this case:

fields = ['a', 'b', 'c']

query = "UPDATE users SET {} = ?".format(random.choice(fields))

in addition to using it to help you build big queries where it would be tedious to manually type all the placeholders, and difficult to ensure that you had the correct number if your code changed:

my_list = ['a', 'b',...., n]
placeholders = ', '.join(['?' for item in my_list])

query = "INSERT .... VALUES = ({})".format(placeholders)

user671150 · Accepted Answer · 2016-09-20 07:54:41Z

0

You should use parametrized queries something like this:

c.execute("""UPDATE users SET password = ? WHERE memberID = ?;""", (newPas, memID))

It will allow to avoid nasty things like SQL-injections.

answered Sep 20, 2016 at 7:54

user671150

2 Comments

roganjosh Over a year ago

That's just a mirror of my comment, without knowing whether it actually solves the problem...

user671150 Over a year ago

@roganjosh I didn't see your comment when I was writing the answer. Sorry for that.

Collectives™ on Stack Overflow

Python Sqlite3 Database table isn't being updated

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related