Search code examples
pythonsqlalchemysanic

SQLAlchemy converts Password to HEX Value


I'm using Sanic + SQLAlchemy for a project of mine.
Basically, I have this endpoint which is used for registering an user

from sanic import Blueprint, response
from sanic.request import Request
from sanic.response import HTTPResponse
import bcrypt
import logging
from sanic_cors import CORS, cross_origin
from services.userservice import create_user, find_by_username


@bp.route("/add",methods=["POST", "OPTIONS"])
@cross_origin(bp, origins='*',automatic_options=True)
async def register(req: Request) -> HTTPResponse:
    """
    Register the user in the system
    :param req: the request sent from the user. Will contain the necessary parameters to save the user in the system
    :return 200 if the user is registered, 500 if an error happens
    """
    LOGGER.info(">>>>>> /user/add")
    chosen_username = req.json['utente']['username']
    chosen_pwd = req.json['utente']['password']
    salt = bcrypt.gensalt(rounds = 10, prefix=b"2a")
    hashed = bcrypt.hashpw(chosen_pwd.encode('utf-8'), salt)
    insert_success = await create_user(chosen_username,hashed,req.app.session_pgsql)
    if insert_success:
        LOGGER.info(f"[{chosen_username}] successfully created!")
        created_user = await find_by_username(chosen_username, req.app.session_pgsql)
        return response.json({'status': 200, 'message': 'OK', 'data': created_user}, 200)
    else:
        LOGGER.error(f"Couldn't add {chosen_username} to the database!")
        return response.json({'status':500, 'message': 'ERROR', 'data': None},500)

The bcrypt instantiates the salt in that way because the authentication process is carried by a Java Microservice.
Then, when the code reaches the create_user method

async def create_user(chosen_username: str, hashed: str, session_pgsql: Session) -> bool:
    """
    Creates the specified user in the database
    :param chosen_username: the chosen username
    :param hashed: the hashed password that the new user chose
    :param session_pgsql: the SQLAlchemy Session Object that will insert the user in the DB
    :return True if the insert was successful, False otherwhise
    """
    new_user = Utente(username=chosen_username, pwd=hashed)
    session_pgsql.add(new_user)
    try:
        session_pgsql.commit()
    except Exception as e:
        session_pgsql.rollback()
        return False
    return True

The save operation goes smoothly but, in my PostgreSQL database (the connection is made with the postgresql+psycopg2 driver URL configuration), I see the password saved as a sequence of hex characters. Even if, for example, I try to

session_pgsql.query(Utente).filter(Utente.id == new_user.id).update({Utente.pwd : hashed}, synchronize_session = False)

or

new_user.pwd = hashed
session_pgsql.commit()

in debug mode with PyCharm, the password is still shown as a sequence of hex characters. Does somebody might have a vague clue of what's going on? As far as I've seen in the debug, the password arrives correctly to the create_user method, so I assume the problem must rely inside SQLAlchemy
The Utente model is a class like this

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Boolean
from sqlalchemy.orm import relationship

Base = declarative_base()

"""
The ORM class that represents the 'Utente' table 
"""
class Utente(Base):
   __tablename__ = 'utente'
   id = Column(Integer, primary_key=True)

   username = Column(String, unique=True)
   pwd = Column(String)
   enabled = Column(Boolean, default=False)
   first_access_done = Column(Boolean, default=False)

EDIT: As requested, here's what happens to my data.
Right before the commit on the database, the Utente object looks like this.

new_user.username
Out[4]: '[email protected]'
In[5]: 
new_user.pwd
Out[5]: b'$2a$10$WhF2CBCCm1MLaoOlwRx4YeA.uMQNtL3XHOI6i09ZbWkKDp5hxA2Fe'

Then, this query is executed by SQLAlchemy

2020-06-05 20:27:02,973 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2020-06-05 20:27:03,016 INFO sqlalchemy.engine.base.Engine INSERT INTO utente (username, pwd, enabled, first_access_done) VALUES (%(username)s, %(pwd)s, %(enabled)s, %(first_access_done)s) RETURNING utente.id
2020-06-05 20:27:03,016 INFO sqlalchemy.engine.base.Engine {'username': '[email protected]', 'pwd': b'$2a$10$WhF2CBCCm1MLaoOlwRx4YeA.uMQNtL3XHOI6i09ZbWkKDp5hxA2Fe', 'enabled': False, 'first_access_done': False}
2020-06-05 20:27:03,037 INFO sqlalchemy.engine.base.Engine COMMIT

But if I go to see the result query in the database, this is what the result actually looks like

id|username              |pwd                                                                                                                       |enabled|first_access_done|
--|----------------------|--------------------------------------------------------------------------------------------------------------------------|-------|-----------------|
 5|[email protected]|\x2432612431302457684632434243436d314d4c616f4f6c775278345965412e754d514e744c3358484f49366930395a62576b4b447035687841324665|false  |false            |

Solution

  • In the end, it happened to be an error by my side.
    The doubt came to me when I saw that the result of the bcrypt's hash, in the debugger, was rendered as b'$2a$10$WhF2CBCCm1MLaoOlwRx4YeA.uMQNtL3XHOI6i09ZbWkKDp5hxA2Fe'.
    I believed that bcrypt's hashpw method returned a string, while it actually returns a bytes object. Thus, if saved to a PostgreSQL varchar field, the database tries to conver the value to an actual string (like Ilja Everilä was suggesting in the comments).