0

I am able to use MySQL's AES_ENCRYPT() function in MySQL itself.

Here are the steps I used to enable that:

1) Add the following lines to /etc/my.cnf to use CBC mode

[mysqld]
block_encryption_mode=aes-256-cbc

2) Restart MySQL 3) Check it is working in MySQL:

SELECT @@session.block_encryption_mode;

4) Create random IV:

SELECT RANDOM_BYTES(16);

5) Store the IV in a safe place and set @iv in MySQL

SET @iv = 'output from above'

6) Test inserting data in MySQL

UPDATE some_table
SET column_name = HEX(AES_ENCRYPT("data here", 'enc key here', @iv))
WHERE id=1;

That works fine. I can update columns with tha

However, when I try to add data from PHP PDO, I get the following error:

HY000 1882 The initialization vector supplied to aes_encrypt is too short. Must be at least 16 bytes long

Here is a the query:

UPDATE session_data SET
    session_url=:session_url,
    session_password=:HEX(AES_ENCRYPT(:session_password), :enc_key, @iv),
    session_creation_datetime=now()
    WHERE session_id=:session_id

The query works fine without AES_ENCRYPT(). I suspect that using @iv in the PHP PDO query is the problem.

Does anyone know if this is true and whether there is another way to access @iv in MySQL from PHP?

raw-bin hood
  • 5,839
  • 6
  • 31
  • 45
  • How do you set @iv from the php code? Why don't you use the random_bytes function directly with aes_encrypt? – Shadow Apr 18 '20 at 01:50
  • The reason that I would not use the RANDOM_BYTES function is because that would not store the IV which is needed to decrypt the column data. – raw-bin hood Apr 18 '20 at 02:12

0 Answers0