> > In my case, I want to enforce case-insensitive comparison for
> > encrypted email addresses.
>
> The hybrid can be made to work for encryption very nicely if you use a
> comparator that coerces the value to its encrypted form before passing to the
> database. It's worth publishing so I created an example at
> http://www.sqlalchemy.org/trac/wiki/UsageRecipes/SymmetricEncryption.
>
> The issue the approach has however is if you're dealing with salted
> encryption and attempting to compare crypted values in the database - you
> can't encrypt on the Python side since each comparison would require the salt
> of each value to be compared. Ants' older recipe at
> http://www.sqlalchemy.org/trac/wiki/UsageRecipes/HashPropertyproposes using
> DB side functions for this scenario.
Thanks so much for your advice. I took your SymmetricEncryption
recipe and modified it to support case-insensitive comparison on the
encrypted values. I created a working example below as a reference
for others.
from sqlalchemy import create_engine, func, Column, Integer,
LargeBinary
from sqlalchemy.ext.hybrid import hybrid_property, Comparator
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import scoped_session, sessionmaker
EMAIL_LEN_MAX = 256
encrypt = lambda x: x.encode('utf-8').replace('---', '===') # Dummy
function
decrypt = lambda x: x.decode('utf-8').replace('===', '---') # Dummy
function
db = scoped_session(sessionmaker())
Base = declarative_base()
class CaseInsensitiveUnicode(unicode): # !!!
def __lt__(self, other):
return self.lower() < other.lower()
def __le__(self, other):
return self.lower() <= other.lower()
def __eq__(self, other):
return self.lower() == other.lower()
def __ne__(self, other):
return self.lower() != other.lower()
def __gt__(self, other):
return self.lower() > other.lower()
def __ge__(self, other):
return self.lower() >= other.lower()
class CaseInsensitiveEncryptComparator(Comparator):
def operate(self, op, other, **kwargs):
return op(self.__clause_element__(), encrypt(other.lower()),
**kwargs) # !!!
class EmailMixin(object):
'Mixin class for a case-insensitive encrypted email address'
email_ = Column(LargeBinary(EMAIL_LEN_MAX * 2)) # Doubled for
unicode addresses
@hybrid_property
def email(self):
return CaseInsensitiveUnicode(decrypt(self.email_)) # !!!
@email.setter
def email(self, email):
self.email_ = encrypt(email.lower())
@email.comparator
def email(cls):
return CaseInsensitiveEncryptComparator(cls.email_)
class User(Base, EmailMixin):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
engine = create_engine('sqlite:///', echo=True)
db.configure(bind=engine)
Base.metadata.bind = engine
Base.metadata.create_all(engine)
db.merge(User(email='[email protected]'))
db.merge(User(email='[email protected]'))
user = db.query(User).filter_by(email='[email protected]').first()
assert user.email == '[email protected]'
assert user.email == '[email protected]'
emails = [x.email for x in db.query(User)]
assert sorted(emails) == ['[email protected]', '[email protected]']
assert '[email protected]' in emails
#
https://github.com/invisibleroads/invisibleroads-templates/blob/master/invisibleroads/paster_templates/core/+package+/models.py_tmpl
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
