0

have Encrypted column in DB via Model like:

from sqlalchemy_utils import StringEncryptedType

 
first_name = sa.Column(
        StringEncryptedType(sa.Unicode, settings.encryption_key), nullable=True
    )

while try to search results its fetching well but when i try to sort the results on first_name its not either working or giving desired results:

here is how I am applying sorting:

    elif sort_by == SortByEnum.first_name._value_:
    query = (query.order_by(cast(Customer.first_name, sa.Unicode).desc())
             if sort == SortEnum.desc._value_
              else query.order_by(cast(Customer.first_name, sa.Unicode).asc()))    ### Give results but not sorted one as per requirement
                #query.order_by(fernet.decrypt(token=Customer.first_name)).asc())  ### Tried Fernet library with same token but gives Error"token must be bytes or str"
kah
  • 71
  • 9
  • 1
    Encrypted columns can't be sorted on the database side (unless you use an order-preserving encryption algorithm). `StringEncryptedType` uses AES under the hood, which doesn't satisfy that requirement (you're always giving up some secrecy when you require order-preserving schemes). You'll have to sort the list after retrieving and decoding it in your own code (using `sorted` for example) instead. – MatsLindh Apr 11 '23 at 14:00
  • true that, solved by following your suggestion @MatsLindh. thanks – kah Apr 12 '23 at 05:05

0 Answers0