Geek asks and answers

SQLAlchemy & PassLib

tl; dr - How do I use a Python-side library such as PassLib for hash passwords before embedding them in a MySQL database with SQLAlchemy?

Okay, so I hit my head on my desk for a day or two, trying to figure it out, so here it is:

I am writing a web application using Pyramid / SQLAlchemy and I am trying to interact with my MySQL database.

Ultimately, I want to do something like the following:

Compare password with hash:

if user1.password == 'supersecret'

Insert new password:

user2.password = 'supersecret'

I would like to use PassLib to hash my passwords before they go to the database, and I don't really like to use the MySQL SHA2 built-in function because it is not salty.

However, just to try, I have this work using the SQL 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

2 http://www.sqlalchemy.org/trac/wiki/UsageRecipes/DatabaseCrypt

, MySQL SHA2 ( func.sha2()) , . PassLib Python.

PassLib : :

from passlib.hash import sha256_crypt

new_password = sha256_crypt.encrypt("supersecret")

sha256_crypt.verify("supersecret", new_password)

, . , , TypeDecorator, , , . , , .

, - = ==, -?

+5
2

PasswordType sqlalchemy-utils . passlib. :

, pbkdf2_sha512, md5_crypt. ; , pbkdf2_sha512.

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

, :

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

, :

  • .
  • , , .
  • , db

, - :

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 ( , , ).

-

Edit: , , PassLib , . , , , sqlalchemy:

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

, : - " " (), .

, User , , , : User (, ). . , , SSO /:

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
+3

More articles:


All Articles