How can I retrieve the binary data using the mysql python connector?

Question

I'm just trying to store and retrieve a bcrypt password in a mysql database. The column type for the password is BINARY(60) as suggested by this answer. I'm using the mysql python connector. Here is my script.

cnx = mysql.connector.connect(user='akhil', password='apples', host='127.0.0.1', database='test')
cursor = cnx.cursor()

# Generate a password and store it in the database
add_user = ("INSERT INTO login (username, password) VALUES (%s, %s)")
hashed=bcrypt.hashpw(b'secret1', bcrypt.gensalt(14))
cursor.execute(add_user, (1, hashed))
cnx.commit()

# Retrieve the password and validate it
query = ("SELECT username, password FROM login WHERE username=%s")
cursor.execute(query, (1,))

for (username, password) in cursor:
    print(type(password))  # <class 'str'>
    if bcrypt.checkpw(b'secret1', password.encode('utf-8')):
        print('Password matches for user 1')
    else:
        print('Password does not match for user 1')

Now this outputs 'Password matches...' so it works. But here is my login table

+----------+------------+------+-----+---------+-------+
| Field    | Type       | Null | Key | Default | Extra |
+----------+------------+------+-----+---------+-------+
| username | int(11)    | YES  |     | NULL    |       |
| password | binary(60) | YES  |     | NULL    |       |
+----------+------------+------+-----+---------+-------+

Clearly the password datatype is BINARY(60). Why is it getting converted to a string by the mysql python connector? I looked at this answer, which suggested to pass raw=True to the connection constructor, but when I did that (and also removed my manual utf-8 encoding of the returned password from the database), I got the following output/error

<class 'bytearray'>
Traceback (most recent call last):
  File "C:/Users/akhil/School/blackmirror/bernard.py", line 20, in <module>
    if bcrypt.checkpw(b'super secret1', password):
  File "C:\Users\akhil\School\blackmirror\env\lib\site-packages\bcrypt\__init__.py", line 112, in checkpw
    return _bcrypt.lib.timingsafe_bcmp(ret, hashed_password, len(ret)) == 0
TypeError: initializer for ctype 'void *' must be a cdata pointer, not bytearray

Should I just leave the .encode('utf-8'), is that the correct way to do this?

I won't speculate on the "correct" way to do it, but I will note that if you call `bytes` on the `bytearray` bcrypt doesn't raise the error. — snakecharmerb, Mar 08 '20 at 09:23

How can I retrieve the binary data using the mysql python connector?

0 Answers0