Mysql/MariaDB search thru AES Encrypted data

Question

I have a table with aes encrypted data in it and wanted to search thru it with a select statement with a LIKE in it. Now since I have to decrypt it first I am using the HAVING but for some reason the LIKE is case sensitive. I have tried putter LOWER() around the data but that doesn't seem to be changing the situation.

SELECT fd.id, AES_DECRYPT(fd.data, 'mysecretkey') AS data
FROM field_data fd
HAVING LOWER(data) LIKE '%test%'

Answer 1

you can use a collation that is case insensitive

CREATE TABLE field_data (id int,data  BLOB)

INSERT INTO field_data
VALUES (1,AES_ENCRYPT('testmedata', UNHEX(SHA2('My secret passphrase',512))))
,(2,AES_ENCRYPT('TESTmedata', UNHEX(SHA2('My secret passphrase',512))));

SELECT fd.id, AES_DECRYPT(fd.data, UNHEX(SHA2('My secret passphrase',512))) AS decdata
FROM field_data fd
HAVING decdata  LIKE '%test%' COLLATE utf8mb4_general_ci

id | decdata   
-: | :---------
 1 | testmedata
 2 | TESTmedata

db<>fiddle here