Creating an Encrypted Database with MySQL

Building on the principle of providing security in layers, we must consider the senario of our entire database being exposed or downloaded. We must ask ourselves the question of whether the contents of the database is worth protecting and if it is possible to avoid storing sensitive data in the first place.

Encrypting a database is not full proof and is still vunerable if exposed to an attacker, but it does make it considerably more difficult to extract the sensitive information as it must first be decrypted.

The encryption & decryption process requires the combination of two keys. The first key is stored in the source code of the php file and the second is stored within the database itself, but the database key must vary with each set of set of data and also be sufficiently random.

This tutorial does assume that you have sufficient knowledge of MySQL to understand what is happening with the database operations.

Encrypt and Save to DB

function encrypt($data, $db, $key){

    /* Open the cipher */
    $td = mcrypt_module_open('rijndael-256', '', 'ofb', '');

    /* Create the IV and determine the keysize length */
    $iv = mcrypt_create_iv(mcrypt_enc_get_iv_size($td), MCRYPT_DEV_RANDOM);
    /* Intialize encryption */
    mcrypt_generic_init($td, $key, $iv);

    /* Encrypt data */
    $encrypted = mcrypt_generic($td, $data);

    /* Define other db fields */
    $mysql['encrypted'] = $db->real_escape_string($encrypted);
    $mysql['iv'] = $db->real_escape_string($iv);

    $query = "INSERT INTO table_name(customer_id, encrypted, iv)
    VALUES('', '".$mysql['encrypted']."', '".$mysql['iv']."' ) ";

    $result = $db->query($query);

    /* Terminate encryption handler */
    mcrypt_generic_deinit($td);

}

Retrieve from DB and Decrypt

function decrypt($db, $key, $customer_id){

    $input['customer_id'] = $customer_id;
    $mysql['customer_id'] = $db->real_escape_string($input['customer_id']);

    $query = "SELECT * FROM table_name WHERE customer_id = ". $mysql['customer_id'] ." ";
    $result = $db->query($query);
    if(!$result){
        return FALSE;
    }

    $row = $result->fetch_assoc();

    $encrypted = stripslashes($row['encrypted']);
    $iv = stripslashes($row['iv']);

    /* Open the cipher */
    $td = mcrypt_module_open('rijndael-256', '', 'ofb', '');

    /* Initialize encryption module for decryption */
    mcrypt_generic_init($td, $key, $iv);

    /* Decrypt encrypted string */
    $decrypted = mdecrypt_generic($td, $encrypted);

    /* Terminate decryption handle and close module */
    mcrypt_generic_deinit($td);
    mcrypt_module_close($td);

    return $decrypted;

}

Database Connection Variable

$db = mysqli_connect("host_name", "user_name", "password", "database_name");

/* Check connection */
if (mysqli_connect_errno($con)){
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

Maintaning Unique Data Sets With Encryption

A common problem when encrypting a database is avoiding duplicate entries. One way of coping with this is to create a very difficult to reverse hash of the sensitive data and compare hashes. The method below is very unlikely to provide two hashes that are identical with different data sets, but it is worth noting that this is possible and a direct comparision after decryption is required to ultimately determine duplication of data.

$salt = 'random_string';
$hash = md5($salt . md5($sensitive_data . $salt) );