Adding server-side encryption to your MySQL database with PHP-Defuse.

Summary

This post will just go over how to add server-side encryption to an application that will be based on a user’s password and this PHP encryption library called php-defuse. I will use memcache to store the temporary key that is used to encrypt/decrypt while a user is logged in. This method only requires including a .phar file. This method does not take into consideration database-level encryption like AWS RDS.

My setup is a LAMP stack with a Debian 9 web server.

Design considerations

Offhand I can’t provide numbers, I would have to do some testing but with the way encryption/decryption works, I think it is expected to have some delay, although I’m not sure how noticeable it would be.

Take for example a simple SQL search in a column:

That’s pretty straight forward/expected. But if everything is encrypted, you have to first decrypt everything, then search for what you want.

So instead you would have a pre-process like this:

So you can see you first select everything in a particular column, decrypt them, then do your comparison for the fruit apple. I realize this code could be better just an example I made up on the spot.

There is a catch for failed decryption above, this has happened a few times when I was first working with encryption. That can suck, because you can’t decrypt it if the wrong key is used.

This encryption approach is meant to be based on a user’s password, so the database manager has no way to decrypt the user’s content because they don’t know(aren’t supposed to know) the user’s password. I store it as a bcrypt hash.

Steps to implement encryption

defuse-crypto.phar file

First you’ll need to get yourself a copy of the defuse-crypto.phar file.

memcache

Since this encryption approach that I’m taking is going to use memcache to store the encryption/decryption key while a user is logged in, you’ll want to make sure that memcache is available on your server.

I used the grep method:

php -m | grep memcache

It’s not available for me on this server so I have to install it.

I’m using Debian so I used the method:

apt-get install php-dev php-pecl
pecl install memcache

I actually got a an error regarding zlib. Installed zlib

apt-get install zlib1g-dev

I’m actually not sure if I need the memcache extension but it seems I can’t say no when prompted to make a choice.

Oh yay, another error.

‘make’ failed

Ooh this sucks, PHP7 incompatability it appears. I last used this memcache approach with CentOS so using Debian is a different experience. Alright will fix using this ported memcache for PHP7.

This part you may want to figure out on your own. I’m using the ported memcache for PHP 7 as mentioned above, but yeah just get memcache working. If you’re cool with using PHP 5.6 then that’s probably a simpler approach/more reliable.

Looks like I need memcached too.

Well… this is not going well. Also that link about is plesk specific. I think I’ll just downgrade to PHP 5.6.

Alright… disabled/removed PHP 7 and downgraded to PHP 5.6 was able to install memcache as expected using:

apt-get install php5.6-memcache
apt-get install memcached

Make sure to install php5.6-mysql as well, you’ll probably get a “driver not found” with any interactions to your mysql database after removing/updating PHP.

Next you’ll want to test that this works before integrating it within your normal flow of execution regarding your user login/logout, then querying of data.

Checking memcache through PHP

This is going to be part of the encrypt/decrypt process so you’ll want to run this code and make sure that memcache is indeed available.

I actually got “memcache not available” and had to install memcached as well, I updated the command above.

This memcache test code is not mine, I found it in a Stack Overflow post a while back.

Check setting a key

So with that code above that verifies memcache is available, you’ll want to set a key to be used for encryption/decryption. It’ll just be a word for now but eventually you will use the user’s name after they’ve logged in. This way you can set their name to a session and use the session to make sure they can encrypt/decrypt(they are still logged in).

Here, the $name will be the username and then the $name_key will actually be part of php-defuse namely the user key saved to an AsciiSafeString.

Then you can retrieve the key and use it for encryption/decryption like so:

Sample decrypt/encrypt

Let’s just encrypt a simple phrase like “Encrypt me peasant.”

The output is this, which note it’s different every time you run it.

Encrypt me peasant
encrypted: def50200070401bea50a0f6b306220b97346413b8c1033b747ddef9b6949595cecb8c7e9aa31a87c0ff19b74cd0d65f37dbe5539cffb75a4195ef0771901f69eabc954503bd9e69f0819e4c1e42bc99b35b45c0a20d9fe4e48bcbf60eac41e0799675bb1b814
decrypted: Encrypt me peasant

Second run

Encrypt me peasant
encrypted: def50200534dcfe2ac6bce4cd9b0d7afc2cf30199327fdd52c58c70466cf502a0e58117eda6f108fb4847b4adb04969751fc20a0bd19776340dd12092242886dbac37a34499b5aef35f9843a2fb376bfe51f0123548cdb8a93b49fc3a5e94b030a6ba398ab3e
decrypted: Encrypt me peasant

So this is everything in one go, and how you would implement this is, in your login process, check if this user has an ascii_key entry in a protected_key database that you create on your server. If not, generate one and save it. Then the next time they log in, grab it, and use that for the next time the user logs in.

Specifically I’m talking about this line:

$protected_key = KeyProtectedByPassword::loadFromAsciiSafeString($protected_key_encoded);

$protected_key_encoded is what you save. You generate it from their password when they login, not the hash. The hash of their password is what you store for your login system.

$protected_key = KeyProtectedByPassword::createRandomPasswordProtectedKey($pass);
$protected_key_encoded = $protected_key->saveToAsciiSafeString();

So the user logs in, grab their key, use that to decrypt their data in the database, and use it to encrypt before storage. On subsequent pages that require interaction with the database specifically encrypted content, use the session with their username to grab the key from memcache, then use that to decrypt their data.

Then when they log out, delete their key from memcache:

$memcache->delete(‘keyname’);

Hope this is helpful, currently this is my method to encrypt data server side with the LAMP stack.