On Jun 21, 2011, at 7:55 PM, Roy Hyunjin Han wrote: > Hi Michael, > > In the documentation you state that the Hybrid Value pattern is useful > for encrypted values. > http://www.sqlalchemy.org/docs/orm/extensions/hybrid.html > Is it possible for you to provide an example of the Hybrid Value > pattern as applied to encrypted values? > > In my case, I want to enforce case-insensitive comparison for > encrypted email addresses. The email addresses are currently > encrypted model-side by overriding TypeDecorator to encrypt on the way > in and decrypt on the way out. > > I have tried applying a > column_property(comparator_factory=CaseInsensitiveComparator) but that > applies lower() to the encrypted value instead of the decrypted email > address. > > I thought the Hybrid Value pattern might apply in this case, but the > encrypt() and decrypt() functions I am using are strictly Python > without SQL equivalents. Is there a way to enforce case-insensitive > encrypted comparison from the model?
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/HashProperty proposes using DB side functions for this scenario. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
