Select CI3 Encrypted data directly from MySQL database - Printable Version +- CodeIgniter Forums (https://forum.codeigniter.com) +-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5) +--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24) +--- Thread: Select CI3 Encrypted data directly from MySQL database (/showthread.php?tid=68027) |
Select CI3 Encrypted data directly from MySQL database - eflyerman - 05-11-2017 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'; A little research on Codeigniter Docs says: Quote:However, the encryption_key is not used as is. So the question is, how do I get the "actual" key used for the encryption? THX in advance!!! RE: Select CI3 Encrypted data directly from MySQL database - Narf - 05-12-2017 Can't happen that way. Fetch the encrypted data from the DB and decrypt it via PHP. RE: Select CI3 Encrypted data directly from MySQL database - xenomorph1030 - 05-12-2017 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/blob/develop/system/libraries/Encryption.php#L371 RE: Select CI3 Encrypted data directly from MySQL database - eflyerman - 05-12-2017 (05-12-2017, 04:14 AM)xenomorph1030 Wrote: 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/blob/develop/system/libraries/Encryption.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) Which produced the log: Code: DEBUG - 2017-05-12 11:43:57 --> encryption->_openssl_decrypt(predata=gdAl+49y1SWCagGyTCQAV2L4mA8O81sbMxMzrtpjFvI=) Code: SET block_encryption_mode = 'aes-128-cbc'; 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); RE: Select CI3 Encrypted data directly from MySQL database - eflyerman - 05-12-2017 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) The data I got from the log file is: Code: DEBUG - 2017-05-12 11:43:57 --> encryption->_openssl_decrypt(predata=gdAl+49y1SWCagGyTCQAV2L4mA8O81sbMxMzrtpjFvI=) 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); Truncate the first 16 bytes off the data that was used for the initial_vector Code: encryption.php | $data = self::substr($data, $iv_size); Call openssl_decrypt Code: encryption.php | : openssl_decrypt($data,$params['handle'], $params['key'],1, // DO NOT TOUCH!$iv ); 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. |