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.
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
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(...).