Encryption and Decryption of data in MySQL and Codeigniter project

0
votes

Earlier I was storing data in Database without Encrypting. But Now i need to Encrypt both the existing data as well as newly inserted data.

So here What i did: For encryption of existing data I used AES_ENCRYPT() method ,

Update table SET poster_id = AES_ENCRYPT('poster','Mykey') ;

It is working fine and i am able to DECRYPT data using AES_DECRYPT() method .

In code i have done changes as :

$config['encryption_key'] = 'Mykey'; //in config.php page.

and encrypted value using

$encrypted_string = $this->encrypt->encode($poster_id);

This is also working fine as i am able to ENCRYPT NEW inserting data to database.

But when i am trying to DECRYPT all the data now by using AES_DECRYPT() function (both existing and newly inserted data ).I am getting correct value for old data and getting Null value for the newly inserted data . Please suggest on this or give any alternative solution.

Thank You.

1
phpmysqlencryption
Your new PHP-based encryption doesn't work. Hard to tell why, though, seeing as you've not posted any actual code. – Sammitch
AES_DECRYPT returns NULL if it detects invalid data. It sounds like codeigniter generated a bad encryption – patrick3853
Why do you think $this->encrypt->encode() is identical to mysql's AES_ENCRYPT? – zerkms
yeah both are not identical. So what can be the solution can you please suggest. – Alok

1 Answers

1
votes

So here What i did: For encryption of existing data I used AES_ENCRYPT() method ,

That's where you went wrong.

CodeIgniter's Encryption class doesn't just do a naked AES encryption of the data. It takes a lot of steps to protect against adaptive chosen-ciphertext attacks:

  • HKDF-SHA256 is used to split your key into two keys (one for AES, the other for HMAC-SHA256).
  • The data is encrypted with AES-CBC (MySQL's AES_ENCRYPT() only provides ECB) with a random IV.
  • The IV and ciphertext are authenticated with HMAC-SHA256.

They're simply incompatible with each other, and the correctly implemented one is CodeIgniter, not MySQL. You want CodeIgniter's design.