1

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

Ilja Everilä
  • 50,538
  • 7
  • 126
  • 127
Igneel32
  • 15
  • 1
  • 6
  • 2
    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 Sep 20 '16 at 07:42
  • 1
    I hope you don't have [little Bobby Tables](https://xkcd.com/327/) as a member. – Ilja Everilä Sep 20 '16 at 07:53
  • 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](http://stackoverflow.com/questions/7784148/understanding-repr-function-in-python) 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ä Sep 20 '16 at 08: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 Sep 20 '16 at 08:15
  • Placeholders are always the right thing to do (ok, 1 exception: you cannot pass identifiers). The DB-API already knows how to (correctly) sanitize python values. It turns out for example that `bytes` values should be sent to SQLite as `x'afafafafafafafaf'` literals (SQLite syntax, not to be confused as python). – Ilja Everilä Sep 20 '16 at 08:17
  • @Igneel32 You're welcome. Yes, do not use `format` like this at all. The only time you should use `format` is if you want to make the field name variable in the query (e.g. `password`) and then you should have some sanitisation. It's actually less characters to type to do it properly anyway :) – roganjosh Sep 20 '16 at 08:17
  • Actually, there is one more time you could use `format` and that's for placeholders themselves in long queries. E.g. if you want to insert many values in a row `placeholders = ', '.join(['?' for item in myList])` and `"INSERT... VALUES = {}".format(placeholders)` – roganjosh Sep 20 '16 at 08:22
  • @roganjosh there's also [`executemany`](https://docs.python.org/3/library/sqlite3.html#sqlite3.Cursor.executemany), though it's not exactly the same; it avoids the maximum placeholder limit. – Ilja Everilä Sep 20 '16 at 08:25
  • @IljaEverilä but `executemany` is to repeat the query for something like a nested list - to insert multiple rows in one go? My `format` example is simply to avoid having to type hundreds of '?' for long queries for each row, and would work with `execute` and `executemany` – roganjosh Sep 20 '16 at 08:26
  • @roganjosh Yes and your original example is good and valid. Thought I'd just also point out that `executemany` exists, as people often end up using placeholder catenation to build up `VALUES (?), (?), (?), (?), ...` queries, for which `executemany` is usually the better solution. – Ilja Everilä Sep 20 '16 at 08:29
  • I'm quite new to Stack Overflow, what do I do now with the question? Do I tick Nurzhan's answer or make my own detailing your solution or something other? Thank you once again, all of you, for your help! – Igneel32 Sep 20 '16 at 08:32
  • @IljaEverilä ah, fair enough :) I actually made a small mistake. It should be `"INSERT... VALUES = ({})".format(placeholders)` with the brackets around `{}`. – roganjosh Sep 20 '16 at 08:32
  • 1
    @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 Sep 20 '16 at 08:33

2 Answers2

1

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.

Nurjan
  • 5,889
  • 5
  • 34
  • 54
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)
roganjosh
  • 12,594
  • 4
  • 29
  • 46