How can decrypt Cakephp3 encrypted data right from MySQL?

Question

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;
    }

    // ...
}

That answer looks pretty awful you should probably first think about the threat you're trying to address. Encrypting your db is almost always pointless. — pvg
@miken32 from CakePHP: ` /** * Security and encryption configuration * * - salt - A random string used in security hashing methods. * The salt value is also used as the encryption key. * You should treat it as extremely sensitive data. */ ` — Sidney
This is not the case. book.cakephp.org/3.0/en/core-libraries/… — miken32
Why don't you show us the code you're using to actually encrypt the data? — miken32

ndm ndm · Accepted Answer · 2016-01-16T09:04:59

Do you really need to do that?

I'm not going to argue about the pros and cons of storing encrypted data in databases, but whether trying to decrypt on SQL level is a good idea, is a question that should be asked.

So ask yourself whether you really need to do that, maybe it would be better to implement the decryption at application level instead, it would probably make things easier with regards to replicating exactly what Security::decrypt() does, which is not only decrypting, but also integrity checking.

Just take a look at what Security::decrypt() does internally.

It should be pretty easy to re-implement that in your other application.

Watch out, you may be about to burn your fingers!

I am by no means an encryption expert, so consider the following as just a basic example to get things started, and inform yourself about possible conceptual, and security related problems in particular!

Handling encryption/decryption of data without knowing exactly what you are doing, is a very bad idea - I can't stress that enough!

Decrypting data at SQL level

That being said, using the example code from my awful (sic) answer that you've linked to, ie using Security::encrypt(), and Security::salt() as the encryption key, will by default leave you with a value that has been encrypted in AES-256-CBC mode, using an encryption key derived from the salt concatenated with itself (first 32 bytes of its SHA256 representation).

But that's not all, additionally the encrypted value gets an HMAC hash, and the initialization vector pepended, so that you do not end up with "plain" encrypted data that you could directly pass to AES_DECRYPT().

So if you'd wanted to decrypt this on MySQL level (for whatever reason), then you'd first of all have to set the proper block encryption mode

SET block_encryption_mode = 'aes-256-cbc';

sparse out the HMAC hash (first 64 bytes) and the initialization vector (following 16 bytes)

SUBSTRING(`column` FROM 81)

and use the first 32 bytes of hash('sha256', Security::salt() . Security::salt()) as the encryption key, and the initialization vector from the encrypted value for decryption

SUBSTRING(`column`, 65, 16)

So in the end you'd be left with something like

SET block_encryption_mode = 'aes-256-cbc';
SELECT
    AES_DECRYPT(
        SUBSTRING(`column` FROM 81), -- the actual encryted data
        'the-encryption-key-goes-here',
        SUBSTRING(`column`, 65, 16) -- the intialization vector
    )
FROM table;

Finally you maybe also want to cast the value (CAST(AES_DECRYPT(...) AS CHAR)), and remove possible zero padding (not sure whether AES_DECRYPT() does that automatically).

Data integrity checks

It should be noted that the HMAC hash that is prepended to the encrypted value, has a specific purpose, it is used to ensure integrity, so by just dropping it, you'll lose that. In order to keep it, you'd have to implement a (timing attack safe) HMAC256 generation/comparison on SQL level too. This leads us back to the intial question, do you really need to decrypt on SQL level?