Search LIKE with AES_ENCRYPT and AES_DECRYPT in mysql

Question

I use this technique to crypt some field of my database:
How to use AES_ENCRYPT and AES_DECRYPT in mysql

It works very well but I am facing a problem.
Now that the contents of the field are encrypted, I can not do LIKE in a classic way!

I try to do the like on the decrypt field but sql does not recognize the field !!
Here is the structure (very simple):

CREATE TABLE `messages` (
  `id` int(11) NOT NULL,
  `message` varchar(250) NOT NULL,
  `crypt_key` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `messages`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;

Insert:

INSERT into messages (message) VALUES (AES_ENCRYPT('Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Aenean commodo ligula eget dolor. Aenean massa. Cum sociis natoque penatibus et magnis dis parturient montes, nascetur ridiculus mus. ', '123456'));

Simple select:

SELECT 
       CAST(AES_DECRYPT(message, '123456') AS CHAR(50)) decrypt 
FROM  messages 

Perfect, all work!

---

Now, let's imagine that I want to search with a LIKE in the 'message' field:

SELECT 
       CAST(AES_DECRYPT(message, '123456') AS CHAR(50)) decrypt 
FROM  messages WHERE decrypt LIKE '%Lorem%'

I get this error:

Unknown 'decrypt' field in where clause

And same error for this query:

SELECT 
       CAST(AES_DECRYPT(message, '123456') AS CHAR(50)) AS decrypt 
FROM  messages WHERE decrypt.message LIKE '%Lorem%'

---

In principle, I have the message encrypt, the decryption key and the algorithm for decryption! It should be possible to search by sql in the decrypt field but I can not find a solution.

With a request stack? but this is not very optimize...

I am taker of any solution and any opinion!

Answer 1

Column aliases are not permitted in the WHERE clause. But you can use them in the HAVING clause:

SELECT CAST(AES_DECRYPT(message, '123456') AS CHAR(50)) decrypt 
FROM messages
HAVING decrypt LIKE '%Lorem%'

You can also just copy the full expression and use it in the WHERE clause (like Bernd Buffen suggested in the comments):

SELECT CAST(AES_DECRYPT(message, '123456') AS CHAR(50)) decrypt 
FROM messages
HAVING CAST(AES_DECRYPT(message, '123456') AS CHAR(50)) LIKE '%Lorem%'

But I don't see a reason for the code duplication here. The performance should be the same, since a table scan will be performed either way.

Answer 2

This is not a Answer. Its only for @Paul Spiegel.

The biggest difference is that HAVING first create a result-set with all ROWs and after this it filtered the result with the HAVING clause. So the Query will take a lot more memory to store the first result.

If you use a WHERE clause, its also a full table scan, but mysql puts only the result row in the result set.

See the sample. i have create a table with only 1000 rows and only every 10th row there is a "LOREM" in the string. see Column "filtered" in the EXPLAIN.

mysql> select id,CAST(message AS CHAR(20)) from messages limit 10;
+----+---------------------------+
| id | CAST(message AS CHAR(20)) |
+----+---------------------------+
|  1 | Lorem ipsum dolor si      |
|  2 | XXXXX ipsum dolor si      |
|  3 | XXXXX ipsum dolor si      |
|  4 | XXXXX ipsum dolor si      |
|  5 | XXXXX ipsum dolor si      |
|  6 | XXXXX ipsum dolor si      |
|  7 | XXXXX ipsum dolor si      |
|  8 | XXXXX ipsum dolor si      |
|  9 | XXXXX ipsum dolor si      |
| 10 | XXXXX ipsum dolor si      |
+----+---------------------------+
10 rows in set, 10 warnings (0,00 sec)

mysql> EXPLAIN SELECT
    ->        message  decrypt
    -> FROM  messages
    -> HAVING decrypt LIKE '%LOREM%';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | messages | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0,00 sec)

mysql>
mysql> EXPLAIN SELECT
    ->        message  decrypt
    -> FROM  messages
    -> WHERE message LIKE '%LOREM%';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | messages | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 |    11.11 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0,00 sec)

mysql>