Is PHP OpenSSL safer than just using MySQL AES_ENCRYPT/DECRYPT alone?

Question

I wish to encrypt/decrypt data in my MySQL database stored on my server. I use a salted hash for my passwords. All encryption/decryption would occur on the server. I use PHP end points which my remote clients access. The end points then access the MySQL database.

I've got the following PHP code to cipher/decipher a message:

<?php
    $key = "mySecretKey";
    $cipher = "aes-128-gcm";
    $ivlen = openssl_cipher_iv_length($cipher);

    $iv = openssl_random_pseudo_bytes($ivlen);
    $ciphertext = openssl_encrypt("message to be encrypted", $cipher, $key,     $options=0, $iv, $tag);

    $original_plaintext = openssl_decrypt($ciphertext, $cipher, $key, 0, $iv, $tag);

    echo "original message: ".$original_plaintext."\n";

    // $iv and $tag change each time we encrypt the data, so store these
?>

... which I was going to use to store encrypted data in my database. However, I will also need to store $iv and $tag with my data as these change for each openssl_encrypt executed.

On the other hand, I could just use MySQL's AES_ENCRYPT functions. This option would much simplify my code, because I can just add AES_ENCRYPT(data, key) to my SQL code.

However, I get the feeling the PHP solution is more secure than AES_ENCRYPT alone. Is this true?

On the other hand, I'm not so sure. For example, if somebody got hold of my AES_ENCRYPT key, then they could quite as easily get hold of my PHP $key in which case they would also be able to decrypt the openssl_encrypt-ed messge too, since the $iv$ and $tag information would probably be as easy to get hold of if they've got their hands on the $key somehow.

Or maybe I'm going about my PHP solution the wrong way. Maybe $iv should not be generated each time encrypt a piece of information? Maybe $iv should be final, i.e. always the same, in which case $tag is always the same...

Answer 1

If you would do the encryption on the MySQL-server side, you would have to send the (unencrypted) data to the MySQL-server. To do this securely, you would need to ensure an encrypted connection to the MySQL-server to protect the data in-transit. Depending on your setup, the data could (partially) end up in a log file somewhere. This all adds a layer of complexity, without added security benefit.

Also read: Is “Why Should You Avoid AES In MySQL?” true?.

Encrypt on the PHP side
If you encrypt the data on the PHP side, you can make use of user-friendly packages (SymmetricEncryption or php-encryption spring to mind), and just store the encrypted string in your DB, which is a lot less complex.
Given your question about, for example, the requirement for the $iv, I strongly suggest you use a standard library!

So, in summary: Yes, using PHP OpenSSL is safer than MySQL AES_ENCODE/DECODE.

Answer 2

Do not use AES_ENCRYPT unless you fully understand the issues and how to use it properly!

There are several significant issues with AES_ENCRYPT:

It has poor defaults

The default block_encryption_mode is aes-128-ecb. Anyone who understands what ecb is and sees it here as a default is probably cringing right now. Suffice it to say that it can leak a significant amount of information.

It recommends insecure key derivation

From the documentation:

A passphrase can be used to generate an AES key by hashing the passphrase. For example:

INSERT INTO t
VALUES (1,AES_ENCRYPT('text', UNHEX(SHA2('My secret passphrase',512))));

Do not pass a password or passphrase directly to crypt_str, hash it first. Previous versions of this documentation suggested the former approach, but it is no longer recommended as the examples shown here are more secure.

Apparently they previously recommended using a password directly as the key! Now they've improved it to a single iteration of a fast hash function with no salt. For why this is still pretty terrible see here. The correct method would be to use a password based key derivation function such as PBKDF2.

It doesn't support authenticated encryption

The supported modes are ECB, CBC, CFB1, CFB8, CFB128, OFB. Most of these modes are malleable to a certain extent, and some of them allow you to flip a specific bit in the ciphertext to flip the same bit in the plaintext. Your question suggests you'd be using GCM which prevents this by checking the authenticity.

Even if I didn't want authenticated encryption I would personally prefer to avoid using something with such insecure defaults and recommendations, and as Jacco points out, encrypting in PHP is better if you can't guarantee that the connection between PHP and MySQL uses TLS.