PHP based OpenSSL encrypted data how to MySQL AES Decrypt?

Viewed 99

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;

Output is as below

0 Answers
Related