Searching for values in an encrypted MySQL column

Hi there,

I have a model that encrypts several attributes, before saving them to a MySQL table. After find(), it decrypts them.

This is all done with the encryptByKey and decryptByKey methods of the yii\base\Security class in Yii2, using a predefined encryptionKey constant, and all works fine.

All values are saved in BLOB fields in the table. Now for the next challenge.

I want to be able to search within this columns. I know MySQL has encryption and decryption functions, but can’t seem to get it to work.

It has to be something like this:

Test::find()->where('SOME_FUNCTION(column, 'key') = "value"');

Any encryption king out there that can provide the answer?

Its an encryption novice replying while you wanted king to reply.

As per my understanding, encrypted columns are not meant to be searched and you can make another index column only for searching without exposing the contents of the encrypted column.

If the encryptionkey is used for all rows and the search is for the entire blob content, did you try to first use encryptByKey on the search string?

(Sorry if I’m missing something obvious I should have known)

I did, of course

It seems that for one string, a different encrypted blob is created each time you encrypt.

Obviously I must read more about encryption methods (not now though).

I guess the encryption creates something like multiple exclusive "factors" representing the unencrypted data. Decryptable using the encryptionkey constant.