PHP Openssl decrypt an AES Mysql Encryption

Question

So i'm just doing some basic data encryption on my mysql tables. I followed the guidelines found here https://dev.mysql.com/doc/refman/5.6/en/encryption-functions.html#function_aes-encrypt

But i'm running into an issue. While i know i can just use aes_decrypt in the mysql query to decrypt the data. I want to also have the ability for php to do so itself.

I've gotten this part working. If MySQL does the very basic AES_ENCRYPTION like so

INSERT INTO tablename (dataset) VALUES (AES_ENCRYPT('testvalue','mysecretphrase'))

I'm able to decrypt this with php like so

openssl_decrypt(base64_encode($dR['dataset']), 'aes-128-ecb', 'mysecretphrase')

My problem shows up when i use the recommended UNHEX(SHA2('mysecretphrase',512)) that MySQL mentions in the url above.

The php sha* functions i tried using and can confirm that they both generate the same string as MySQLs sha2()

openssl_digest('mysecretphrase', 'sha512')
// AND
hash('sha512', 'mysecretphrase')

And lastly to work around the UNHEX() that mysql uses, after some research I turns out that PHP hex2bin == unhex http://www.php.net/manual/en/function.hex2bin.php

However, i'm just not getting any result when decrypting the data. This is where it keeps failing. I feel as though i'm either missing something but this just does not decrypt the data and only returns empty results.

openssl_decrypt(base64_encode($dR['dataset']), 'aes-128-ecb', hex2bin(openssl_digest('mysecretphrase', 'sha512')))

Any help, pointers or hints would be greatly appreciated.

Kalk

As long as you only encrypt on insert and don't select multiple records ever, I'd just use PHP for the encryption because it has no real impact on performance or functionality then. – Coker

Answer 1

I had the same issue, and thanks to MIvanIsten's answer I figured out how to correctly encrypt with MariaDB/MySQL and decrypt with PHP:

Encrypt:

SELECT HEX( AES_ENCRYPT( 'secret data', 
SUBSTR(UNHEX(SHA2('mysecretphrase', 512)), 1, 16) )) AS encrypted;

Decrypt:

openssl_decrypt(
   base64_encode(hex2bin($encrypted_string_from_mysql)), 
   'aes-128-ecb',
   substr(hex2bin(openssl_digest('mysecretphrase', 'sha512')), 1, 16)  
);

Skite

Answer 2

openssl_encrypt() and openssl_decrypt() silent cuts the key to max 16 bytes length (at least for aes-128-ecb).

passphrase documentation

If the passphrase is shorter than expected, it is silently padded with NUL characters; if the passphrase is longer than expected, it is silently truncated.

<?php
    $key1 = hex2bin(openssl_digest('mysecretphrase', 'sha512'));
    $key2 = substr($key1, 0, 16);
    $key3 = substr($key1, 0, 15);
    $method = 'aes-128-ecb';
    $in = 'testvalue';
    $data1 = base64_decode(openssl_encrypt($in, $method, $key1));
    $data2 = base64_decode(openssl_encrypt($in, $method, $key2));
    $data3 = base64_decode(openssl_encrypt($in, $method, $key3));
    var_dump(
        array(
            'key1'=>bin2hex($key1),
            'key2'=>bin2hex($key2),
            'key3'=>bin2hex($key3),
            'data1'=>bin2hex($data1),
            'data2'=>bin2hex($data2),
            'data3'=>bin2hex($data3),
            'data1==data2'=>($data1===$data2),
            'data1==data3'=>($data1===$data3)
        )
    );
?>

result:

   array(8) {
        ["key1"]=>string(128) "5fe76dfd5b75cf7cf68fae85d26fcc9b7951806ad6daaa71d843c6ec0e0ec9233a828ad9b60986a43d734983c8a0a50d3a0a49ec5ac196cfcc136aa16e0c5f89"
        ["key2"]=>string(32) "5fe76dfd5b75cf7cf68fae85d26fcc9b"
        ["key3"]=>string(30) "5fe76dfd5b75cf7cf68fae85d26fcc"
        ["data1"]=>string(32) "eb69e89312c1f7b9522d0e66346f2029"
        ["data2"]=>string(32) "eb69e89312c1f7b9522d0e66346f2029"
        ["data3"]=>string(32) "664f5a28d241f959beac350f2314b079"
        ["data1==data2"]=>bool(true)
        ["data1==data3"]=>bool(false)
    }

In mysql the full length key is used by AES_ENCRYPT() and AES_DECRYPT()

SELECT 
    HEX(AES_ENCRYPT('testvalue',UNHEX(SHA2('mysecretphrase',512)))) AS l_full,
    HEX(AES_ENCRYPT('testvalue',SUBSTR(UNHEX(SHA2('mysecretphrase',512)),1,16))) AS l_16,
    HEX(AES_ENCRYPT('testvalue',SUBSTR(UNHEX(SHA2('mysecretphrase',512)),1,15))) AS l_15;

result:

l_full                           | l_16                             | l_15
---------------------------------|----------------------------------|----------------------------------------
A88DD1EFB377FD31A0EFA55EA29BA8C6 | EB69E89312C1F7B9522D0E66346F2029 | 664F5A28D241F959BEAC350F2314B079

Isiahisiahi

Answer 3

I'm answering here because it's cheaper than commenting apparently...

The post above tells you quite exactly what the problem is but doesn't really say how to address it.

openssl_encrypt() and openssl_decrypt() silent cuts the key to max 16 bytes length (at least for aes-128-ecb)

And there is no way to change this, therefore you will need to shorten your key by creating a substring of it when using AES_ENCRYPT in MySQL.

INSERT INTO tablename (dataset) 
VALUES (AES_ENCRYPT('testvalue',SUBSTR( UNHEX(SHA2('mysecretphrase',512)), 1, 16))))

Notice how the l_16 column in the above answer is the same as ["data2"] now that you use the substring? (may need to do a strtolower() in php)

If this raises security concerns for you, you will need to find an alternative encryption algorithm that doesnt have this restriction

Guardianship