MySQL database encryption using public/private keys

As an additional security measure you can encrypt sensitive data in your database. When a hacker obtains access to your database, the hacker will find the encrypted data. You want your web application to enter new encrypted data into the database and you want to grant users of your web app access to the sensitive data without exposing the decryption key to the hacker.

In this article I will explain one solution for this problem:

  1. General outline of the solution
  2. Discussion of the solution: user management and weaknesses
  3. PHP techniques used for implementation: OpenSSL for asymmetric encryption, Mcrypt AES for symmetric encryption
  4. How to store the keys and data in the database
  5. Securing commenter’s e-mail addresses with the Yii Framework

To solve this problem you can use asymmetric encryption with a public and private key. Data is encrypted by the web app with the public key. This encrypted data can only be decrypted with the secret private key not with the public key. To grant the user of your web app access to, but keep the hacker away from the private key you can encrypt the key with ‘regular’ symmetric encryption using the user’s password.

When the user logs into the web app the private key is fetched from the database and will be decrypted using the password just entered by the user. The decrypted key will be stored in session storage so the private key can be used as long as the user is logged in to access sensitive data.

Flowchart of common use cases

User management

One advantage of using one asymmetric key pair for the data and symmetric encryption for the private key is that when users change passwords only the private key needs to be re-encrypted for that specific user. Changing passwords and creating new passwords is only possible when the private key is available unencrypted which is the case when a user is logged in. So when a user can’t login and needs a new password the administrator can generate a new temporary password for the user.

Weaknesses

The weakness of this system is the temporal storage of the unencrypted private key in the session storage. When a hacker has access to both the database and the session storage he can decrypt the sensitive data. To reduce the risk of exposing the session data you can:

  • keep the time the session is stored on the server as small as possible. Delete the session directly when the user logs off. Log the user off automatically after a period of inactivity.
  • encrypt the sensitive session data with a key available to the application. This is probably useless because a hacker who can access the sessions has most likely also access to all application data.
  • use a binary session handler which controls access to the sessions. This is the hardest to build and the hardest to hack.

Implementing additional security for the sessions is beyond the scope of this article.

PHP

The simplest method to work with asymmetric encryption in PHP is to use OpenSSL. An OpenSSL extension is available for PHP which is easy to install on most systems. Use this code to create a keypair and to use the keypair to encrypt and decrypt some data:

<?php
/* Create the private and public key */
$res = openssl_pkey_new();

/* Extract the private key from $res to $privKey */
openssl_pkey_export($res, $privKey);

/* Extract the public key from $res to $pubKey */
$pubKey = openssl_pkey_get_details($res);
$pubKey = $pubKey["key"];

$data = 'i.amniels.com is a great website!';

/* Encrypt the data using the public key
 * The encrypted data is stored in $encrypted */
openssl_public_encrypt($data, $encrypted, $pubKey);

/* Decrypt the data using the private key and store the
 * result in $decrypted. */
openssl_private_decrypt($encrypted, $decrypted, $privKey);

echo $decrypted;
?>

To encrypt the private key you can use symmetric encryption. Symmetric encryption is available in MySQL with the AES_ENCRYPT and AES_DECRYPT commands. You can also use the Mcrypt PHP extension to encrypt the data. When you use MCRYPT_RIJNDAEL_128 as cipher and MCRYPT_MODE_ECB as cipher mode the Mcrypt functions will be compatible with MySQL encryption. Mcrypt offers two functions which basically wrap all code required to encrypt and decrypt. Example usage:

<?php
$data = 'i.amniels.com is a great website!';
$pass = 'password';

/* Obtain the maximum key length of the cipher and
 * cipher mode. Longer keys are automatically
 * XXX to the maximum size */
echo mcrypt_get_key_size(MCRYPT_RIJNDAEL_128, MCRYPT_MODE_ECB) . "\n"; // 32

/* Encrypt the data. ECB doesn't use IV */
$encrypted = mcrypt_encrypt(MCRYPT_RIJNDAEL_128, $pass, $data, MCRYPT_MODE_ECB);

/* Decrypt using the same cipher, mode and pass */
echo mcrypt_decrypt(MCRYPT_RIJNDAEL_128, $pass, $encrypted, MCRYPT_MODE_ECB);
?>

Database

Before you can send encrypted raw binary data to the database with a query, you need to encode the data to a string. With PHP you can use base64_encode() to encode the data to a string and base64_decode() to decode the data back to its original state. You can store the data with MySQL in a VARBINARY field or in a BLOB field.

Securing commenter’s e-mail addresses with the Yii Framework

To show how you can implement this encryption solution I will show you how I encrypt the e-mail addresses entered by commenters on my blog. My blog app consists of the Yii Framework Blog demo with some modifications. If you don’t use the Yii framework for your application, my code examples are probably still useful to read.

database scheme

This are the two relevant database tables of my blog app. The User table stores the authentication information and the encrypted private key. All users of my blog are allowed to view all e-mail addresses (I’m the only user). The e-mail address of a commenter is saved encrypted with the comment in the Comments table.

Yii offers a security component which is accessible from anywhere within your application with Yii::app()->getSecurityManager(). It offers among others methods for symmetric 3DES encryption (encrypt() and decrypt()) and methods for cookie validation. I have extended this component to offer asymmetric encryption everywhere in the app. The component is available on GitHub. Working of the component is explained in the code comments.

To access the private key in the database I added two functions to the User model (which is a model for the User table):

/**
 * Set the private key
 * @param string $key
 * @param string $password
 */
public function setPrivateKey($key, $password){
    if($this->validatePassword($password)){
        $this->encrypted_private_key = base64_encode(Yii::app()->getSecurityManager()->encrypt($key, $password));
    }else{
        throw new CException('Wrong password');
    }
}
/**
 * Get the private key
 * @param string $password
 * @return string
 */
public function getPrivateKey($password){
    if($this->validatePassword($password)){
        return Yii::app()->getSecurityManager()->decrypt(base64_decode($this->encrypted_private_key), $password);
    }else{
        throw new CException('Wrong password');
    }
}

When the user logs in you need to get the private key (using $user->getPrivateKey($password);) to store it in the session (using Yii::app()->getSecurityManager->storeAsymPrivateKey($key);). I added this line to the UserIdentity authenticate() function:

Yii::app()->getSecurityManager()->storeAsymPrivateKey($user->getPrivateKey($this->password));

The authenticate function is of course the wrong place for this code, but it is easy since all required data is available there so I added it nevertheless.

I automated the encryption and decryption of the email field by adding this code to the Comment model:

/**
 * This is invoked before the record is saved.
 * @return boolean whether the record should be saved.
 */
protected function beforeSave()
{
    if(parent::beforeSave())
    {
        if($this->isNewRecord)
            $this->create_time=time();
        $this->email = Yii::app()->getSecurityManager()->asymEncrypt($this->email);
        return true;
    }
    else
        return false;
}
/**
 * This is invoked after the record is fetched.
 * @return boolean true
 */
protected function afterFind(){
    if(!Yii::app()->user->isGuest){
        if(!Yii::app()->getSecurityManager()->hasAsymKeys()){
            throw new CException ('Not all keys are loaded for asymmetric en/ decryption.');
        }
        $this->email = Yii::app()->getSecurityManager()->asymDecrypt($this->email);
    }
    return true;
}

After implementing all the code above and the SecurityManager class, you can generate a key pair using Yii::app()->getSecurityManager()->generateAsymNewKeyPair() and store the private key in the database. Then you are ready to use asymmetric encryption in your own blog.

I showed you the principles of simple asymmetric data encryption and a simple implementation in a blog app. For a larger application you would have to integrate key management with user management. I'm not going to publish a plug-n-play asymmetric Yii extension since that would require a lot of extra effort.

If you have questions or remarks about this article. Please don't hesitate to write a comment. You can enter your (not required) e-mail address without worries here. It is safe in my database ;-)

19 Apr 11 Comments ( )