I have below function to encrypt data to a MYSQL database,
Note: See in the PHP function below IV is attached to the Data and its stored in the DB with the IV in-front as it is. 'dob' column is varchar 500 and utf8/utf8_bin character set.
/**
* Encrypt via OpenSSL
*
* @param string $data Input data
* @param array $params Input parameters
* @return string
*/
protected function _openssl_encrypt($data, $params)
{
if (empty($params['handle']))
{
return FALSE;
}
$iv = ($iv_size = openssl_cipher_iv_length($params['handle']))
? $this->create_key($iv_size)
: NULL;
$data = openssl_encrypt(
$data,
$params['handle'],
$params['key'],
1, // DO NOT TOUCH!
$iv
);
if ($data === FALSE)
{
return FALSE;
}
return $iv.$data;
}
When i try to decrypt this with MYSQL AES_DECRYPT function i am unable to get rid of the IV part from the Data field. Does anyone know how to do this with Bare SQL functions?
When i used below function i get output as per the screenshot,
SET block_encryption_mode = 'aes-256-cbc';
set @k = From_base64('key');
set @iv = From_base64('iv');
SELECT CAST(AES_DECRYPT(FROM_BASE64(dob), @k, @iv) AS CHAR) as dob FROM client;
