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, Jan 15 '16 at 17:45
@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. */ ` — Sidneyrees.com, Jan 15 '16 at 18:38
This is not the case. http://book.cakephp.org/3.0/en/core-libraries/security.html#encrypting-and-decrypting-data — miken32, Jan 15 '16 at 19:09
Why don't you show us the code you're using to actually encrypt the data? — miken32, Jan 15 '16 at 19:09
Edited my question. Source: http://stackoverflow.com/questions/32260229/encyption-decryption-of-form-fields-in-cakephp-3 — Sidneyrees.com, Jan 15 '16 at 19:23
Besides the fact that you cannot easily decrypt with just a call to `AES_DECRYPT`, why do you encrypt the data at all, when you additionally store it unencrypted? — ndm, Jan 15 '16 at 20:37
@ndm Hi! This database is just for testing. If I can make this work, I will implement this on my app. — Sidneyrees.com, Jan 15 '16 at 20:51
And why do you want to decrypt on SQL level, instead of on application level? Note that on SQL level you'd have to additionally implement a timing attack safe HMAC256 generation/comparison if you want to keep the integrity checks that `Security::decrypt()` provides. — ndm, Jan 15 '16 at 21:46

ndm · Answer 1 · 2016-01-17T10:35:33.987

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?

score 1 · Accepted Answer · answered Feb 29 '16 at 14:54

[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.

According to CakePHP documentation, The mcrypt extension has been deprecated in PHP7.1 http://book.cakephp.org/3.0/en/core-libraries/security.html — DJ Far, Oct 01 '16 at 16:45

score 0 · Answer 3 · answered Feb 06 '22 at 02:22

FOR ANYONE STRUGGLING TO DECRYPT WITH MYSQL: This generally applies to anyone using symmetric AES encryption/decryption - specifically when trying to decrypt with AES_DECRYPT.

For instance, if you are using aes-128-ecb, and your encrypted data is 16 bytes long with no padding, you need to add padding bytes to your encrypted data before trying to decrypt (because mySQL is expecting PKCS7 padding). Because MySQL uses PKCS7, you need to add 16 more bytes, in this case those pad bytes are 0x10101010101010101010101010101010. We take the left 16 bytes because when we encrypt the 0x10101010101010101010101010101010, we get 32 bytes, and we only need the first 16.

aes_decrypt(concat(<ENCRYPTED_BYTES>, left(aes_encrypt(<PAD BYTES>, <KEY>), 16)), <KEY>)

How can decrypt Cakephp3 encrypted data right from MySQL?

3 Answers3

Do you really need to do that?

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

Decrypting data at SQL level

Data integrity checks