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 *

You May Also Like…