[FIXED] How can decrypt Cakephp3 encrypted data right from MySQL?

Issue

I have a very specific requirement where some columns need to be encrypted using aes_encrypt / aes_decrypt. We need to encrypt the information at SQL level using a eas so it can be read using another app or directly from MySQL using a query and aes_encrypt / aes_decrypt.

Our app was developed using CakePHP 3 and database is MySQL 5.6.25.

I found and carefully follow the instruction on this selected answer: Encyption/Decryption of Form Fields in CakePHP 3

Now the data is being saved encrypted on the database... the problem is that we still need to be able to use aes_decrypt on MySQL to decrypt the information and it's returning NULL.

On CakePHP 3, config/app.php:

'Security' => ['salt' => '1234567890']

Then encrypted using:

Security::encrypt($value, Security::salt());

Data is saved on MySQL but aes_decrypt() returns NULL

SELECT AES_DECRIPT(address_enc, '1234567890') FROM address;

How can I setup CakePHP 3 to correctly encrypt information so I can later decrypt it on MySQL using aes_decrypt() ?

---

[EDIT]

My MYSQL table:

CREATE TABLE IF NOT EXISTS `address` (
`id` int(11) NOT NULL,
  `address` varchar(255) DEFAULT NULL,
  `address_enc` blob,
  `comment` varchar(255) DEFAULT NULL,
  `comment_enc` blob
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

Note: address and comment are just for testings.

Then, on CakePHP, I created a custom database type:

src/Database/Type/CryptedType.php

<?php
namespace App\Database\Type;

use Cake\Database\Driver;
use Cake\Database\Type;
use Cake\Utility\Security;

class CryptedType extends Type
{
    public function toDatabase($value, Driver $driver)
    {
        return Security::encrypt($value, Security::salt());
    }

    public function toPHP($value, Driver $driver)
    {
        if ($value === null) {
            return null;
        }
        return Security::decrypt($value, Security::salt());
    }
}

src/config/bootstrap.php

Register the custom type.

use Cake\Database\Type;
Type::map('crypted', 'App\Database\Type\CryptedType');

src/Model/Table/AddressTable.php

Finally map the cryptable columns to the registered type, and that's it, from now on everything's being handled automatically.

use Cake\Database\Schema\Table as Schema;

class AddressTable extends Table
{
    // ...

    protected function _initializeSchema(Schema $table)
    {
        $table->columnType('address_enc', 'crypted');
        $table->columnType('comment_enc', 'crypted');
        return $table;
    }

    // ...
}

Solution

[Solution] The solution for this particular requirement (we need to encrypt the information at SQL level using a eas so it can be read using another app or directly from MySQL using a query and aes_encrypt / aes_decryp) was to create a custom database type in CakePHP them, instead of using CakePHP encryption method, we implemented PHP Mcrypt.

Now the information is saved to the database from our CakePHP 3 app and the data be read at MySQL/phpMyAdmin level using eas_decrypt and aes_encrypt.

Answered By - Sidney Rees