I would like to do encryption with MySQL AES_ENCRYPT() function used in a stored procedure on the database.

Firstly, I changed the encryption block mode from aes-128-ecb to aes-256-cbc in the MySQL configuration file.

Secondly, my goal is to call this stored procedure from PHP code to encrypt data (IP & MAC addresses here). The picture below is the stored procedure. I would like to know if the implementation is correct, particularly:

Is it ok to generate the IV for the MAC and the IP in the stored procedure with RANDOM_BYTES?

Is it ok to store both the IV for the IP and for the MAC in two additional columns in the table?

Is it ok to store the key in a file on the web server with the right permissions and then send it in each procedure call?

enter image description here

Here is the PHP code that calls the procedure to encrypt data: enter image description here

I think this is not perfect, especially storing the key on the web server without a key management system but we can't afford one.

1 Answer 1


If you're storing your key on the web-host and not in the database, then you're guarding against the possibility that the data are stolen through some other channel, eg. db backup, but access to the web-host was not possible.

If the IV is generated and stored only within the database using RANDOM_BYTES, you're adding entropy from within the database, and guarding against the possibility that an adversary may steal the key from the web-host, and the ciphertext from eg. a cookie, but won't be able to decrypt without the corresponding IV that is stored in the database.

You might decrypt the key from an encrypted disc store into a ram file, eg. /dev/shm/, and refer to that from within php. Or you could provide it on demand using some network service, but at the end of the day your php process needs to send some key material to the db so it can do its job. (There are also commercial add-on php modules that will execute an encrypted php file, which can also frustrate attempts.)

So far so good... but the static key is still exposed on the web-facing host, which is not ideal - about the best that can be done if you also need to decrypt on the web-host. By the way, you will also want to be able to change that static key at some point, and you'll need a way to identify which key was in use from the record it was used to encrypt.

One aspect of the scheme seems to rely on IVs remaining secret, but it's more likely that anyone seeing an IV would consider it open data, and not part of the secret. So one more thing you might consider is to take what the web-host gives the stored proc as key material, and further permute it inside the stored proc, eg. using some keyed hash such as HMAC. (For example this implementation of HMAC for MySQL Pierce'17 and Konrad'11.) Anyone obtaining the key from the web-host, and the encrypted data from eg. a separate db backup, will still be unable to decrypt. (In this case you will need to somehow ensure the HMAC-key isn't included in said backup file.)

On a side note you should also consider calculating the HMAC tag from the ciphertext, aka. encrypt-then-MAC, and storing this in the record also. It will ensure that the ciphertext hasn't been interferred with in storage when you go back to decrypt later.

Still, anyone with access to the web-host and the db will have everything they need. If you don't need to decrypt data on the web-host (ie. encrypt and store only, say, for logging), then you can generate a random key in php, secure it using asymmetric encryption, and then pass the random key as well as the encrypted random key into your stored proc. (At this point you'll want to have a look at sodium_crypto_box.)

MariaDB - it's worth noting that although it shares a common root with MySQL, MariaDB doesn't offer AES in CBC mode, only aes-128-ecb As a result there's no clean way to secure low-entropy data using a call to only AES_ENCRYPT. Another complication is the absence of RANDOM_BYTES prior to 10.10.x making cryptographically secure key material harder to generate within the db on older versions.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .