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](http://stackoverflow.com/questions/12212636/sql-alchemy-
overriding), 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: [PasswordType](http://sqlalchemy-
utils.readthedocs.org/en/latest/data_types.html#module-
sqlalchemy_utils.types.password) from [sqlalchemy-
utils](https://github.com/kvesteri/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
    

