Can't AES_DECRYPT (MySQL) a string encrypted with openssl_encrypt (PHP)

Question

I'm using a PHP script to encrypt strings using "aes-256-cbc" cipher. Those strings are stored in a MySQL database and I would like for MySQL to decrypt them.

Here is my PHP encrypting function :

function encrypt($token) {
    if($token !== "" && $token !== null && $token !== false) {
      $cipher_method = 'aes-256-cbc';
      $iv = "98awg9X7SiI3jxp3";
      $enc_key = hash("sha256", "Pn6nK7Gs9r");
      $crypted_token = openssl_encrypt($token, $cipher_method, $enc_key, 0, $iv);
      return $crypted_token;
    } 
    return $token;
}

echo hash("sha256", "Pn6nK7Gs9r"); // output 67fa26a5570901994ef1eae105b9286fca44b6f79e200a9fbbd5cff897c3a7ce
echo encrypt("test"); // output Xq1CcrpteSqcybePun+6pQ==

Here is my MySQL script :

SET block_encryption_mode = 'aes-256-cbc';
SET @key_str = SHA2('Pn6nK7Gs9r',256);
SET @init_vector = "98awg9X7SiI3jxp3";
SET @encrypted = "Xq1CcrpteSqcybePun+6pQ==";

SELECT @key_str; # output 67fa26a5570901994ef1eae105b9286fca44b6f79e200a9fbbd5cff897c3a7ce
SELECT AES_DECRYPT(@encrypted,@key_str,@init_vector); # output NULL
SELECT cast(AES_DECRYPT(@encrypted,@key_str,@init_vector) as char(100)); # output NULL
SELECT AES_DECRYPT(cast(@encrypted as BINARY),@key_str,@init_vector); # output NULL
SELECT cast(AES_DECRYPT(cast(@encrypted as BINARY),@key_str,@init_vector) as char(100)); # output NULL

As you can see, it always gives NULL. (expected result : test). I tried other things :

SET block_encryption_mode = 'aes-256-cbc';
SET @key_str = SHA2('Pn6nK7Gs9r',256);
SET @init_vector = "98awg9X7SiI3jxp3";
SET @string = "test";

SELECT AES_ENCRYPT(@string,@key_str,@init_vector); # output [BLOB - 16 B]
SELECT cast(AES_ENCRYPT(@string,@key_str,@init_vector) as char(100)); # output empty value

I would have expected AES_ENCRYPT to return an encrypted string (Xq1CcrpteSqcybePun+6pQ==). What am I doing wrong ?

Thanks in advance for your help.

Answer 1

First, when you compute the hash, you should retrieve the binary value to get exactly 256 bits. To do that in PHP, pass true to hash().

<?php
function encrypt($token) {
    $cipher_method = 'aes-256-cbc';
    $iv = '98awg9X7SiI3jxp3';
    $key = hash('sha256', 'Pn6nK7Gs9r', true);
    $crypted_token = openssl_encrypt($token, $cipher_method, $key, 0, $iv);
    return $crypted_token;
}

echo encrypt('test');
?>

Output:

5EMRiQCvOjQjNSlwpYKyfQ==

Note that the result in encoded in Base64.

In MySQL, to get the binary value of the hash, use UNHEX(). To decode the Base64 string, use FROM_BASE64().

SET block_encryption_mode = 'aes-256-cbc';
SET @key = UNHEX(SHA2('Pn6nK7Gs9r',256));
SET @iv = '98awg9X7SiI3jxp3';
SET @ciphertext = FROM_BASE64('5EMRiQCvOjQjNSlwpYKyfQ==');

SELECT AES_DECRYPT(@ciphertext, @key, @iv);

Output:

test

Answer 2

CAST(AES_ENCRYPT(....) AS CHAR) is the problem. AES_ENCRYPT returns "binary" data, not "character" date.

Xq1CcrpteSqcybePun+6pQ== looks like output the output from TO_BASE64(...).