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 com

Solution 1:

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)

Solution 2:

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.