CODEWITHSUNDEEP
pythonsqlite
Igneel32
Igneel32

Reputation: 35

Python Sqlite3 Database table isn't being updated

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

Upvotes: 1

Views: 713

Answers (2)

roganjosh
roganjosh

Reputation: 13175

A couple of things with your code:

  1. 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.
  2. 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)

Upvotes: 1

Nurjan
Nurjan

Reputation: 6053

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.

Upvotes: 0

Related Questions