SQLAlchemy & PassLib

Question

tl;dr -- How do I use a Python-side library such as PassLib to hash passwords before inserting them into a MySQL DB with SQLAlchemy?

Alright, so I've been banging my head on my desk for a day or two trying to figure this out, so here it goes:

I am writing a web application using Pyramid/SQLAlchemy and I'm trying to interface with my MySQL database's Users table.

Ultimately, I want to do something like the following:

Compare a password to the hash:

if user1.password == 'supersecret'

Insert a new password:

user2.password = 'supersecret'

I'd like to be able to use PassLib to hash my passwords before they go to the database, and I'm not really a fan of using the built-in MySQL SHA2 function since it's not salted.

However, just to try it, I do have this working using the SQL-side function:

from sqlalchemy import func, TypeDecorator, type_coerce
from sqlalchemy.dialects.mysql import CHAR, VARCHAR, INTEGER
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column

class SHA2Password(TypeDecorator):
  """Applies the SHA2 function to incoming passwords."""
  impl = CHAR(64)

  def bind_expression(self, bindvalue):
    return func.sha2(bindvalue, 256)

  class comparator_factory(CHAR.comparator_factory):
    def __eq__(self, other):
      local_pw = type_coerce(self.expr, CHAR)
      return local_pw == func.sha2(other, 256)

class User(Base):
  __tablename__ = 'Users'
  _id = Column('userID', INTEGER(unsigned=True), primary_key=True)
  username = Column(VARCHAR(length=64))
  password = Column(SHA2Password(length=64))

  def __init__(self, username, password):
    self.username = username
    self.password = password

This was copied from the example 2 at http://www.sqlalchemy.org/trac/wiki/UsageRecipes/DatabaseCrypt

So that works and allows me to use the built-in MySQL SHA2 function (by calling func.sha2()) and do exactly what I want. However, now I'm trying to replace this with PassLib on the Python side.

PassLib presents two functions: one to create a new password hash, and one to verify a password:

from passlib.hash import sha256_crypt

new_password = sha256_crypt.encrypt("supersecret")

sha256_crypt.verify("supersecret", new_password)

I can't quite figure out how to actually implement this. Having read all the documentation, I think it is either a different form of TypeDecorator, a custom type declaration, a hybrid value, or a hybrid property. I tried following this, but it doesn't really make sense to me nor does the code suggested there actually run.

So, to sum up my question -- how do I overload the = and == operators so that they run things through the appropriate hash functions?

Answer 1

PasswordType from sqlalchemy-utils should be the best fit for this issue. It uses passlib. Snipped from the docs:

The following usage will create a password column that will automatically hash new passwords as pbkdf2_sha512 but still compare passwords against pre-existing md5_crypt hashes. As passwords are compared; the password hash in the database will be updated to be pbkdf2_sha512.

class Model(Base):
    password = sa.Column(PasswordType(
        schemes=[
            'pbkdf2_sha512',
            'md5_crypt'
        ],
        deprecated=['md5_crypt']
    ))

Verifying password is as easy as:

target = Model()
target.password = 'b'
# '$5$rounds=80000$H.............'
target.password == 'b'
# True

Answer 2

As I understand it, what you want is this:

1. Encrypt the user's password when creating the account. Use your salt and algorithm
2. When the user logs in, hash the incoming password the same way you did when you stored it
3. Compare the two hashes using regular string comparison in your db request

So, something like this for the login code:

from passlib.hash import sha256_crypt
passHash = sha256_crypt.encrypt(typed_password)
// call your sqlalchemy code to query the db with this value (below)

// In your SQLAlchemy code assuming "users" is your users table
// and "password" is your password field
s = users.select(and_(users.username == typed_username, users.password == passHash))
rs = s.execute()

rs would be the resultset of matching users (should be zero or one of course).

Disclaimer - I did not test any of this

Edit: Thank you for pointing out that PassLib uses a different salt each time it's run. Your best bet in that case, since there doesn't seem to be a straightforward way to do it with sqlalchemy, is the below:

s=users.select(users.username == typed_username)
rs = s.execute()
userRow = rs.fetchone()
if (sha256_crypt.verify(userRow.password)):
    # you have a match

Also, to address your request for abstracting: a common methodology for handling this operation is to create a "security" utility class for getting the user (object) that matches the passed login credentials.

The problem with your current setup is that the User constructor has two different operational goals that, though related, are not necessarily the same thing: authenticating a user and getting a User object (for, say, a list of users in a group). The constructor becomes needlessly complex in that case. It's better to put that logic where it can be encapsulated with other security or login-related functionality such as logging in a user via session ID or SSO token instead of username/password:

security.loginUser(username, password)
# or security.loginUser(single_sign_on_token), etc. for polymorphic Security
loggedInUser = security.getLoggedInUser()

... later ...
otherUser = User(username) #single job, simple, clean