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.

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

Answer 2

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

<?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