Select CI3 Encrypted data directly from MySQL database

In the future, you can always look at the library and functions in question. It isn't too difficult to decipher (no pun intended). https://github.com/bcit-ci/CodeIgniter/b...n.php#L371

Thanks.  I tried to update my post last night but it had not been approved yet.


I modified Encryption.php by adding log_messages like this:

PHP Code:

protected function _openssl_decrypt($data, $params)
    {
        if ($iv_size = openssl_cipher_iv_length($params['handle']))
        {
            log_message('debug',"encryption->_openssl_decrypt(predata=".base64_encode($data).")");
            $iv = self::substr($data, 0, $iv_size);
            $data = self::substr($data, $iv_size);
        }
        else
        {
            $iv = NULL;
        }

        log_message('debug',"encryption->_openssl_decrypt(iv_size=$iv_size, iv=".base64_encode($iv)." handle=".$params['handle']." key=".base64_encode($params['key']).")");
        log_message('debug',"encryption->_openssl_decrypt(data=".base64_encode($data).")");

        return empty($params['handle'])
            ? FALSE
            : openssl_decrypt(
                $data,
                $params['handle'],
                $params['key'],
                1, // DO NOT TOUCH!
                $iv
            );
    } 


   

Which produced the log:

Code:

DEBUG - 2017-05-12 11:43:57 --> encryption->_openssl_decrypt(predata=gdAl+49y1SWCagGyTCQAV2L4mA8O81sbMxMzrtpjFvI=)
DEBUG - 2017-05-12 11:43:57 --> encryption->_openssl_decrypt(iv_size=16, iv=gdAl+49y1SWCagGyTCQAVw== handle=aes-128-cbc key=*****)
DEBUG - 2017-05-12 11:43:57 --> encryption->_openssl_decrypt(data=YviYDw7zWxszEzOu2mMW8g==)

   

Code:

SET block_encryption_mode = 'aes-128-cbc';
SELECT  
    AES_DECRYPT(substr(from_base64(weight),16), from_base64('*****'),substr(from_base64(weight),1,16)) as ue_weight,
    AES_DECRYPT(substr(from_base64(height),16), from_base64('[size=small][font=Monaco, Consolas, Courier, monospace]*****[/font][/size]'),substr(from_base64(height),1,16)) as ue_height,
    AES_DECRYPT(substr(from_base64(social_history),16), from_base64('[size=small][font=Monaco, Consolas, Courier, monospace]*****[/font][/size]'),substr(from_base64(social_history),1,16)) as ue_social_history
    FROM health_profiles 
    where user_id = 954;

To break this down step by step I'll show what I was trying to replicate from Encrypt to MySQL:

$iv = self:ubstr($data, 0, $iv_size);