Securely encrypting customer details at rest in MySQL database using PHP

Question

I'm developing an application with PHP that stores some customer details in a MySQL database. (Name, email address, phone number, mailing address) I'm not storing any 'sensitive' information like banking/credit card details, SSN/SIN, DOB, etc. Just basic customer details.

But while obviously security precautions will be in place, should a hacker ever get a copy of the database, I want a decent and relatively simple method to make this data very difficult to be useful (by having it encrypted).

I've read that performing the encryption in the MySQL query is less secure, since the encryption key will be cached in query logs on the database.

So that something like this is not recommended:

UPDATE customers SET email = AES_ENCRYPT('[email protected]', SHA2('encryption key here', 512));

I've looked at numerous questions on Stack Overflow along with other resources. However, many suggestions are at least 5 years old, and there might be simpler best practices now with PHP 7.2 (and later).

I've looked at Defuse, but I tend to avoid third-party libraries where I don't absolutely need them. (I prefer to understand the code and minimize it to my needs.)

Looking at the PHP documentation (https://www.php.net/manual/en/function.openssl-encrypt.php) I found this user contributed suggestion, which looks fairly simple and easy enough to implement:

--- Create Two Random Keys And Save Them In Your Configuration File ---
<?php
    // Create The First Key
    echo base64_encode(openssl_random_pseudo_bytes(32));

    // Create The Second Key
    echo base64_encode(openssl_random_pseudo_bytes(64));
?>
--------------------------------------------------------
<?php
    // Save The Keys In Your Configuration File
    define('FIRSTKEY', 'Lk5Uz3slx3BrAghS1aaW5AYgWZRV0tIX5eI0yPchFz4=');
    define('SECONDKEY', 'EZ44mFi3TlAey1b2w4Y7lVDuqO+SRxGXsa7nctnr/JmMrA2vN6EJhrvdVZbxaQs5jpSe34X3ejFK/o9+Y5c83w==');
?>
--------------------------------------------------------
<?php
    function secured_encrypt($data)
    {
        $first_key = base64_decode(FIRSTKEY);
        $second_key = base64_decode(SECONDKEY);

        $method = "aes-256-cbc";
        $iv_length = openssl_cipher_iv_length($method);
        $iv = openssl_random_pseudo_bytes($iv_length);

        $first_encrypted = openssl_encrypt($data, $method, $first_key, OPENSSL_RAW_DATA, $iv);
        $second_encrypted = hash_hmac('sha3-512', $first_encrypted, $second_key, TRUE);

        $output = base64_encode($iv.$second_encrypted.$first_encrypted);
        return $output;
    }
?>
--------------------------------------------------------
<?php
    function secured_decrypt($input)
    {
        $first_key = base64_decode(FIRSTKEY);
        $second_key = base64_decode(SECONDKEY);
        $mix = base64_decode($input);

        $method = "aes-256-cbc";
        $iv_length = openssl_cipher_iv_length($method);

        $iv = substr($mix, 0, $iv_length);
        $second_encrypted = substr($mix, $iv_length, 64);
        $first_encrypted = substr($mix, $iv_length+64);

        $data = openssl_decrypt($first_encrypted, $method, $first_key, OPENSSL_RAW_DATA, $iv);
        $second_encrypted_new = hash_hmac('sha3-512', $first_encrypted, $second_key, TRUE);

        if (hash_equals($second_encrypted, $second_encrypted_new))
            return $data;

        return false;
    }
?>

Do you think this is fairly secure? I was thinking I'd probably specify AES-256-GCM instead, since I gather that GCM is better than CBC.

Is such encryption overkill for my needs? (Again, no highly sensitive customer details are being stored, and 'hopefully' this level of security (database fields encrypted at rest) is redundant anyway.)

What if I would I skip the hash_hmac sha3-512 portion and just use the openssl_encrypt function?

Answer 1

I've looked at Defuse, but I tend to avoid third-party libraries where I don't absolutely need them. (I prefer to understand the code and minimize it to my needs.)

Since your question title is Securely encrypting customer details at rest in MySQL database using PHP, I'm going to have to split my answer into two.

The recommendation you (and anyone else) will receive in the context of protecting customer data is to use a trustworthy library.

Cryptography is incredibly difficult to get right, even for experts. Just this month, there were attacks against many low-level cryptography libraries. However, the libraries that I've recommended in the PHP community for years (i.e. libsodium) remain impervious (mostly by design) to these attacks.

The libraries that I and other experts recommend are meant to maximize security, minimize the potential for misuse, and are easy to audit. Eschewing these recommendations because you don't want to use third-party libraries is a dangerous position to take with cryptography in particular.

If your desire to "avoid third-party libraries" happens to be a higher priority for you than protecting customers, you should probably tell your customers what you're doing, why, and also what the conventional wisdom of the security industry is; so they can decide if they want to still be your customers anymore.

If you, conversely, said something to the effect of, "This is for my own self education, no real world production systems," then that's a totally separate matter. Writing crypto to learn is a good thing, after all.

Securely Encrypting Customer Details at Rest in PHP + MySQL

Recommendation: Use CipherSweet.

composer require paragonie/ciphersweet:^2

Remember, this part of the answer is "I need to protect customer data". Your desire for not installing a third-party library needs to sit this one out; protecting your customers is a higher priority.

CipherSweet goes several steps further than Defuse: Instead of just solving the "symmetric at-rest data encryption" problem, CipherSweet also solves the "I want to encrypt data but still search on it" problem.

CipherSweet is pluggable; we provide two backends (FIPSCrypto and ModernCrypto) but provide an interface that could be used if anyone needed to write their own. (For example, if you for some reason needed SHA3 and AES-GCM, you could write your own FIPSCrypto variant.)

The CipherSweet documentation is available online.

It aims to not only explain what's going on under the hood, but guide developers towards using it securely. If you run into any trouble with it, feel free to ask for help.

If you decide you don't need any of the "searchable" bits, you can still use CipherSweet without creating blind indexes for a simple AEAD interface.

Alternatively, Defuse and Halite are good options.

But the important thing is, unless you're a cryptography engineer capable of reinventing one of these third-party libraries without accidentally introducing a vulnerability (say: a timing attack, or AES-CBC + HMAC but forgetting to include the IV in the authentication tag calculation), then you should almost certainly use a third-party library.

There Are No Customers Involved, I Just Want To Learn

I found this user contributed suggestion, which looks fairly simple and easy enough to implement:

The code you provided is not secure. Specifically, this part of the code doesn't cover the IV in the HMAC tag calculation:

$first_encrypted = openssl_encrypt($data, $method, $first_key, OPENSSL_RAW_DATA, $iv);
$second_encrypted = hash_hmac('sha3-512', $first_encrypted, $second_key, TRUE);

Notice how $first_encrypted is passed to hash_hmac() but $iv isn't? This is an exploitable vulnerability.

Here's a proof of concept exploit:

// Encrypt a message    
$ciphertext = secured_encrypt('{"is_admin":0,"user_id":12345}');

$decoded = base64_decode($ciphertext);
$extractedIv = mb_substr($decoded, 0, 16, '8bit');
$flip = "\x00\x00\x00\x00\x00\x00\x00\x00" . 
        "\x00\x00\x00\x00\x01\x00\x00\x00";
$extractedIv = $extractedIv ^ $flip;

// Put alternative IV in place of existing IV.
$spliced = $extractedIv . mb_substr($decoded, 16, null, '8bit');
$reEncoded = base64_encode($spliced);

// Decrypt message
$decrypted = secured_decrypt($reEncoded);

var_dump($decrypted, json_decode($decrypted, true));

You should be presented with the following:

string(30) "{"is_admin":1,"user_id":12345}"
array(2) {
  ["is_admin"]=>
  int(1)
  ["user_id"]=>
  int(12345)
}

For this reason, CBC+HMAC is perilous for developers.

You're far better off using libsodium and learning how to write your own high-level protocol atop the primitives it supplies than trying to get CBC+HMAC to be secure.

If you wanted to fix their code, you'd want to make these changes.

Patch for secured_encrypt()

- $second_encrypted = hash_hmac('sha3-512', $first_encrypted, $second_key, TRUE);
+ $second_encrypted = hash_hmac('sha3-512', $iv . $first_encrypted, $second_key, TRUE);

Patch for secured_decrypt()

- $second_encrypted_new = hash_hmac('sha3-512', $first_encrypted, $second_key, TRUE);
+ $second_encrypted_new = hash_hmac('sha3-512', $iv . $first_encrypted, $second_key, TRUE);

Answers to Your Questions

I was thinking I'd probably specify AES-256-GCM instead, since I gather that GCM is better than CBC.

AES-GCM is better than CBC because GCM is authenticated and CBC is not.

However, you can only encrypt about 2^36 messages under a given key for AES-GCM. If this is insufficient for your goals, AES-GCM isn't a good fit either.

Is such encryption overkill for my needs? (Again, no highly sensitive customer details are being stored, and 'hopefully' this level of security (database fields encrypted at rest) is redundant anyway.)

If it weren't for the vulnerability I disclosed above, it might be.

What if I would I skip the hash_hmac sha3-512 portion and just use the openssl_encrypt function?

You could only safely get away with it if you switched to AES-GCM, or to libsodium. Unauthenticated encryption should never be trusted.