Select CI3 Encrypted data directly from MySQL database

I have an app that has several MySQL fields encrypted with AES-128-CBC using the CI3 Encryption->Encrypt() function.  I need to do some extensive reporting and would like to use a BI tool like Jaspersoft or similar product.  This requires a direct connection and non CI SQL query to the database.

The MySQL query should look something like the code snippet below but it always returns a null.

Code:

SET block_encryption_mode = 'aes-128-cbc';
SELECT  
   AES_DECRYPT(weight,'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',random_bytes(16)) as ue_weight,
   AES_DECRYPT(height,'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',random_bytes(16)) as ue_height,
   AES_DECRYPT(social_history,'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',random_bytes(16)) as ue_social_history
    FROM health_profiles;

A little research on Codeigniter Docs says:

However, the encryption_key is not used as is.
If you are somewhat familiar with cryptography, you should already know that a HMAC also requires a secret key and using the same key for both encryption and authentication is a bad practice.
Because of that, two separate keys are derived from your already configured encryption_key: one for encryption and one for authentication. This is done via a technique called HMAC-based Key Derivation Function (HKDF).

So the question is, how do I get the "actual" key used for the encryption?

THX in advance!!!

Can't happen that way.

Fetch the encrypted data from the DB and decrypt it via PHP.

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

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);

Tried to edit my last post but wasn't approved yet.

I modified Encryption.php adding log messages like this:

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
            );
    }

The data I got from the log file is:

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==)

The data in the call is binary and the data in the database is base64 so I tried to replicate the Encrypt function in MySQL like this:

Set the initial_vector using the first 16bytes of the data

Code:

encryption.php |  $iv = self::substr($data, 0, $iv_size);
MySQL          |  substr(from_base64(height),1,16))

Truncate the first 16 bytes off the data that was used for the initial_vector

Code:

encryption.php | $data = self::substr($data, $iv_size);
MySQL          | substr(from_base64(height),16)

Call openssl_decrypt

Code:

encryption.php | : openssl_decrypt($data,$params['handle'], $params['key'],1, // DO NOT TOUCH!$iv );
MySQL          | SET block_encryption_mode = 'aes-128-cbc';
SELECT  AES_DECRYPT(
       substr(from_base64(height),16),                         //data param = substr() starting at pos 16
       from_base64('*****'),                                   //key from log file                          
       substr(from_base64(height),1,16)) as ue_weight,         //inital vector (first 16 bytes of data)
FROM health_profiles
   where user_id = 954;

The query still returns null.  I know that user 954 has valid data in these fields.  Any ideas what I'm missing here?

I've spent way too much time on this already.  I'm just going to decrypt it in CI one row at a time and dump it to a CSV and report from the CSV.  Terribly in-elegant and unsecure way to do it but it is what it is.