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` (
  `address` varchar(255) DEFAULT NULL,
  `comment` varchar(255) DEFAULT NULL,
   PRIMARY KEY (`id`),

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)


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

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


echo '<pre>';
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){
            $this->data[$this->alias][$fieldName] = Security::rijndael(
    return true;

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

    foreach($this->encryptedFields as $fieldName){
        foreach($results as $key => $value){

                    $results[$key][$this->alias][$fieldName] = Security::rijndael(
            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>
    <?php echo h($address['Address']['address_enc']); ?>
<dt><?php echo __('Comment Enc'); ?></dt>
   <?php echo h($address['Address']['comment_enc']); ?>
<dt><?php echo __('Address'); ?></dt>
    <?php echo h($address['Address']['address']); ?>
<dt><?php echo __('Comment'); ?></dt>
   <?php echo h($address['Address']['comment']); ?>

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


Submit a Comment

Your email address will not be published.

You May Also Like…

MacOS USB Creator

Just toasted my Windows 10 Pro install with a Windows 11 upgrade. Think it will be unrecoverable (because of Bitlocker...