How to retrieve passwords from a database

Question

I am building a registration system for my web application wherein users provide a username and a password. This data is stored in a postgresql database. I am using bcrypt to generate a salted hash of the user entered password as follows

import bcrypt
hashed = bcrypt.hashpw(PasswordFromWebForm.encode('UTF-8'), bcrypt.gensalt()) 

This creates a salted password that looks something like this - b'$2b$12$GskbcRCMFHGuXumrNt3FLO'

I am storing this value in a postgresql database. Next, when a user tries to login to the system, I want to verify his/her credentials. To do this, I intend to do something along the lines of -

import psycopg2
conn = psycopg2.connect("dbname=test user=me")
cur = conn.cursor()

saltedpassword = cur.execute("SELECT saltedpassword FROM test WHERE loginid = %s", (LoginIDFromWebForm,))

if bcrypt.hashpw(PasswordFromWebForm.encode('UTF-8'), saltedpassword) == saltedpassword:
    print("Success")

This does not work. It throws the following TypeError: Unicode-objects must be encoded before hashing error.

I suspect that this error is because the variable saltedpassword stores the value as a string like this "b'$2b$12$GskbcRCMFHGuXumrNt3FLO'" instead of just plain b'$2b$12$GskbcRCMFHGuXumrNt3FLO' (Notice the quotes enclosing the salted password in the former)

How do I get around this problem? What is the best way to store the salted hashed password in a database and how do I go about retrieving it when needed for verification? Apologies for the rather longish question - please help.

Answer 1

psycopg2 stores Unicode text, you must decode the salted password before inserting into the database:

 cur.execute("INSERT INTO test VALUES (%s, %s)",
             (LoginIDFromWebForm,  saltedpassword.decode('ascii')))

This prevents the str() conversion being inserted instead (giving you "b'....'" in the database).

Next, when querying saltedpassword is not a string, because cursor.execute() does not return the results; it returns None instead.

You'll need to fetch the result row:

cur.execute("SELECT saltedpassword FROM test WHERE loginid = %s", (LoginIDFromWebForm,))
row = cur.fetchone()
if not row:
    # No such login ID, handle accordingly
saltedpassword =  row[0].encode('ascii')

Since rows contain Unicode text, you need to first encode to a bytestring before passing it to bcrypt. You also want to use the bcrypt.checkpw() function to check the password:

if bcrypt.checkpw(PasswordFromWebForm.encode('UTF-8'), saltedpassword):
    print("Success")

bcrypt.checkpw() avoids timing attacks when comparing strings, something your code is vulnerable to.