Encryption and Decryption of data in MySQL and Codeigniter project

Question

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.

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

Scott Arciszewski · Accepted Answer · 2019-10-25 22:15:39Z

1

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.

answered Oct 25, 2019 at 22:15

Scott Arciszewski

34.3k17 gold badges94 silver badges212 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

redsd Over a year ago

AES_ENCRYPT() supports CBC, which can be set like this: SET @@SESSION.block_encryption_mode = 'aes-256-cbc';

Scott Arciszewski Over a year ago

@redsd AES-CBC is still not good enough. See robertheaton.com/2013/07/29/padding-oracle-attack

Collectives™ on Stack Overflow

Encryption and Decryption of data in MySQL and Codeigniter project

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related