Cant insert sha256 hash into sqlite DB

Question

This is my insert code for Flask project.

    login = request.form['login']
    password = str(generate_password_hash(request.form['password']))
    connection = sqlite3.connect(current_directory + '\chat.db')
    cursor = connection.cursor()
    cursor.execute(f'insert into User values({login}, {password})')
    connection.commit()
    connection.close()

And I get this kind of error:

sqlite3.OperationalError: near ":sha256": syntax error

Which refers to a line with query execution. Where is a mistake?

Hashing the password without salting is meaningless - it's *very* calculate hashes for various passwords until a match is found. Worse, the code uses string interpolation to generate the query so it's wide open to SQL injection. In fact, that's what happened - the contents of `password` were injected into the raw query, resulting in an invalid query. Even if you quote the password and user values, nothing prevents someone from entering a username containing `'` like `O'Reilly` — Panagiotis Kanavos, Oct 12 '20 at 15:34
@PanagiotisKanavos okay, so how do I insert a hashed value in DB? — ArbiterNick, Oct 12 '20 at 15:38
Is this a registration form or login form? If this is a login form I think you should use "check_password_hash" instead of generate_password_hash. — Nadiantara I Wayan, Oct 12 '20 at 15:26
@ArbiterNick first use a library like [passlib](https://passlib.readthedocs.io/en/stable/narr/hash-tutorial.html#hash-tutorial) to both salt and hash the password. By default `pbkdf2_sha256` uses 29000 iterations. Then pass the username and hash as *parameters* to execute, as shown in the duplicate `cursor.execute('insert into User values(%s, %s)',(login,hash))`. This sends the values as separate, strongly-typed parameters to the server. This way you avoid numeric or date conversion errors too, as dates, numbers (or byte arrays) are sent without conversion to text — Panagiotis Kanavos, Oct 13 '20 at 05:12

score 0 · Answer 1 · answered Oct 12 '20 at 15:48

0

The values in sql insert should be in quotas

insert into User values ('login1', 'password1')

In your case could be

cursor.execute(f'insert into User values(\'{login}\', \'{password})\' ')

You should also include column names

INSERT INTO table (column1, column2, ..)
VALUES( value1, value2, ..);

https://sqlite.org/lang_insert.html

answered Oct 12 '20 at 15:48

Ivo Gjorgjievski

129
3

This is still vulnerable to SQL injection attacks or problems caused by input with single quotes, eg `O'Reilly` as a username – Panagiotis Kanavos Oct 12 '20 at 17:05
The question is about syntax error, "Where is a mistake?" So I answered where is the mistake with his code, there was no question about security. @Panagiotis-Kanavos – Ivo Gjorgjievski Oct 12 '20 at 17:31
The error wasn't fixed - no amount of quoting will fix the inherent problems in string interpolation or concatenation – Panagiotis Kanavos Oct 13 '20 at 04:54
Besides, fixing the problem is very easy - use a parameterized query. This removes any need for quotation and avoids string conversion problems with numbers, dates or binary values. It's actually easier to write `cursor.execute('insert into User values(%s, %s)',(login,hash))` than try to fix the conversion problems caused by string concatenation/interpolation – Panagiotis Kanavos Oct 13 '20 at 05:14

Cant insert sha256 hash into sqlite DB

1 Answers1