CakePHP 2.4.6 Transitioning to Encrypted DB Columns

Written by James McDonald

April 3, 2014

Note: The encryption created with Cakephp 2.4.6 Security::rijndael can’t be read (by default) using the MySQL AES_DECRYPT() function so once you have it encrypted you can only read and write using the CakePHP application. I believe CakePHP 2.5.x has another couple of AES functions which may allow MySQL to decrypt also but I haven’t tried it. See below for the SQL to read AES encrypted columns.

A table with 2 fields you want to make encrypted:

CREATE TABLE `addresses2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `address` varchar(255) DEFAULT NULL,
  `comment` varchar(255) DEFAULT NULL,
   PRIMARY KEY (`id`),
) ENGINE=MyISAM AUTO_INCREMENT=136 DEFAULT CHARSET=latin1;

Add the encrypted fields:

ALTER TABLE `mydb`.`addresses2` 
ADD COLUMN `address_enc` BLOB NULL AFTER `address`,
ADD COLUMN `comment_enc` BLOB NULL AFTER `comment`;

Add an update_enc action to Controller/AddressesController.php. This will copy the plain text values from the unecrypted source field to the encrypted field

 public function update_enc() {
                $enc_fields = array(
                        'address' => 'address_enc',
                        'comment' => 'comment_enc'
                );
                $this->Address->recursive = -1;
                $addresses =    $this->Address->find('all');

                foreach($addresses as $add_key => $add_value){
                                        foreach($enc_fields as $key => $value){
                                $addresses[$add_key]['Address'][$value] = $address['Address'][$key];
                                // $this->log($text_str);
                                // rijndael($text, $key, $operation)
                        }

                }

                $this->Address->saveAll(
                        $addresses,
                         array(
                            'fieldList' => array(
                                    'Address' => array('address_enc', 'comment_enc')
                                )
                        )
                );
        $addresses = $this->Address->find("all");
                $this->set(compact('addresses'));
        }

Create a view for the update action View/Addresses/update_enc.php

<?php

echo '<pre>';
print_r($addresses);
echo '</pre>';
// debug doesn't display with encrypted data
// debug($addresses);

?>

In the Model/Address.php put the encryption / decryption routines

// include the Security Utility
App::uses('Security', 'Utility');

public function beforeSave($options = array()) {
    foreach($this->encryptedFields as $fieldName){
        if(!empty($this->data[$this->alias][$fieldName])){
            $this->data[$this->alias][$fieldName] = Security::rijndael(
                $this->data[$this->alias][$fieldName],
                Configure::read('Security.key'),
                'encrypt'
                );
        }
    }
    return true;
}

public function afterFind($results = array()) {
//              $this->log(print_r($results, true));

    foreach($this->encryptedFields as $fieldName){
        foreach($results as $key => $value){
                if(!empty($results[$key][$this->alias][$fieldName])){

                    $results[$key][$this->alias][$fieldName] = Security::rijndael(
                        $results[$key][$this->alias][$fieldName],
                        Configure::read('Security.key'),
                        'decrypt'
                        );
                }
        }
    }
            return $results;
}

In Config/core.php near Security.salt / Security.cipher add a value of Security.key

/* 32 chars long */
Configure::write('Security.key', '12345678901234567890123456789012');

Modify all views to include the encrypted fields View/Address/view.php (do index.ctp, edit.ctp, add.ctp etc)

<dt><?php echo __('Address Enc'); ?></dt>
<dd>
    <?php echo h($address['Address']['address_enc']); ?>
    &nbsp;
</dd>
<dt><?php echo __('Comment Enc'); ?></dt>
<dd>
   <?php echo h($address['Address']['comment_enc']); ?>
   &nbsp;
</dd>
<dt><?php echo __('Address'); ?></dt>
<dd>
    <?php echo h($address['Address']['address']); ?>
    &nbsp;
</dd>
<dt><?php echo __('Comment'); ?></dt>
<dd>
   <?php echo h($address['Address']['comment']); ?>
   &nbsp;
</dd>

Run the update_enc code by accessing it via your browser e.g. http://example.com/addresses/update_enc this does the copy and encryption

Finally drop the old fields

ALTER TABLE `mydb`.`addresses2` 
DROP COLUMN `comment`,
DROP COLUMN `address`;

MySQL Reading AES_ENCRYPTed columns

SELECT CAST( aes_decrypt(address_enc, '12345678901234567890123456789012') AS CHAR(255))
AS Decoded
FROM addresses

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

The reCAPTCHA verification period has expired. Please reload the page.

You May Also Like…

Clear HSTS Settings in CHrome

Open chrome://net-internals/#hsts enter the domain in the query field and click Query to confirm it has HSTS settings...

Ubuntu on Hyper-v

It boils town to installing linux-azure # as root or sudo apt-get update apt-get install linux-azure...