StringEncryptedType, like & ilike support

[fedevx]

Hello!

I couldn't find this in the documentation so I'm trying to see if this is a bug or expected behavior. My tests were carried out with:

SQLAlchemy-Utils==0.36.6

I have an Audit table in which I created a column with: action = Column(StringEncryptedType(String(), SECRET_KEY, AesEngine, 'pkcs5')

I can see data was encrypted correctly and is decrypted correctly as well. I then tried to filter the values where action contains the text 'user':

auditdata = Audit.query.filter(Audit.action.ilike("%user%")).all()

The above returns no matches even when they are present in the DB (SQLite).

To see if I was getting it wrong, I tried the following as well:

Let's say a record for that column has "Created account for user"

Query	Result
auditdata = Audit.query.filter(Audit.action.ilike("%user%")).first()	None
auditdata = Audit.query.filter(Audit.action.ilike("created account for user")).first()	None
auditdata = Audit.query.filter(Audit.action.ilike("Created account for user")).first()	Match returned

It seems then ilike (and similarly like) do not work correctly with StringEncryptedType. I was not able to use wildcards, only a full exact match would return the record (ilike is supposed to convert to lowercase and then match but that didn't work either).

Is what I'm experiencing expected (a limitation due to how encryption is implemented) or is it something that should be fixed?

Thanks!

[hansel-cmd]

Do you have an update regarding this issue? I am experiencing the same thing right now.

[M1LKYWVYs]

+1

[Damirkhon]

+++++1

[4robed]

class Example(BaseModel):
    value = Column(
        StringEncryptedType(
            type_in=String(100),
            key=SECRET_KEY,
            engine=AesEngine,
            padding='pkcs5'
        ),
    )

def search(search_text: str):
    """remove .lower() from value_searchable and search_text to make the search case sensitive (like)"""

    examples = session.query(Example).all()
    search_text = search_text.lower()
    filtered_examples = []

    for example in examples:
        value_searchable = example.value.lower()
        if search_text in value_searchable:
            filtered_examples.append(example)
    return filtered_examples

This is my workaround solution. It will not offer the best performance, but I hope it helps you guys.