PHP mcrypt interoperating with mysql aes_encrypt/aes_decrypt

Discussions of secure PHP coding. Security in software is important, so don't be afraid to ask. And when answering: be anal. Nitpick. No security vulnerability is too small.

Moderator: General Moderators

Post Reply
rmgraci
Forum Newbie
Posts: 4
Joined: Mon Mar 16, 2009 9:26 pm

PHP mcrypt interoperating with mysql aes_encrypt/aes_decrypt

Post by rmgraci »

I would like to have MySQL encrypt some text with aes_encrypt, and then have PHP decrypt it successfully with the mcrypt library. Likewise, I'd like to have PHP encrypt, and MySQL decrypt with aes_decrypt in a select statement.

This should be doable, right? Both can use RIJNDAEL_128. I can't seem to get the results to mesh, though. I'm trying with a key that is 32 bytes in length. Can anyone point me in the right direction? I have Googled quite a bit, but nothing that comes up has worked.

Thanks!
Last edited by rmgraci on Tue Mar 17, 2009 10:37 am, edited 1 time in total.
User avatar
Mordred
DevNet Resident
Posts: 1579
Joined: Sun Sep 03, 2006 5:19 am
Location: Sofia, Bulgaria

Re: PHP mcrypt interoperating with mysql aes_encrypt/aes_decrypt

Post by Mordred »

Code?
rmgraci
Forum Newbie
Posts: 4
Joined: Mon Mar 16, 2009 9:26 pm

Re: PHP mcrypt interoperating with mysql aes_encrypt/aes_decrypt

Post by rmgraci »

Here's some code:

SQL ENCRYPT / PHP DECRYPT

SQL

Code: Select all

 
insert into debug (debugCol) values (aes_encrypt('this will be encrypted', '12345678901234567890123456789012'));
 
PHP

Code: Select all

 
// Some code before this to fetch encrypted value from DB via query "select debugCol from debug". Store in $encryptedFromDb
mcrypt_decrypt(MCRYPT_RIJNDAEL_128, '12345678901234567890123456789012', $encryptedFromDb, MCRYPT_MODE_ECB);
 
rmgraci
Forum Newbie
Posts: 4
Joined: Mon Mar 16, 2009 9:26 pm

Re: PHP mcrypt interoperating with mysql aes_encrypt/aes_decrypt

Post by rmgraci »

Quick update: works just fine when I use a 16-byte secret key. That's relatively short, though. Is that a limitation I'm going to have to live with, or is there some way to increase the limit?
User avatar
Mordred
DevNet Resident
Posts: 1579
Joined: Sun Sep 03, 2006 5:19 am
Location: Sofia, Bulgaria

Re: PHP mcrypt interoperating with mysql aes_encrypt/aes_decrypt

Post by Mordred »

It appears to be some difference in how padding or something else is applied (I can't test now).
You can easily bypass this, by encrypting and decrypting only on one side of the php/mysql boundary (i.e. use aes_encrypt, and then select aes_decrypt(blabla))
I would recommend en/decrypting on the PHP side though, so you can run AWAY from the ECB mode (insecure) and use a mode with a random IV (you'll need to store it in the database though)
rmgraci
Forum Newbie
Posts: 4
Joined: Mon Mar 16, 2009 9:26 pm

Re: PHP mcrypt interoperating with mysql aes_encrypt/aes_decrypt

Post by rmgraci »

Just realized that the AES_ENCRYPT key length is 128 bits, not bytes. So, our 16 byte key is the maximum number of bits that AES_ENCRYPT supports. Looks like we already have this working (hooray).

To help out anyone who's working on this:

PHP Encrypt / MySQL Decrypt
PHP

Code: Select all

 
$plainData = 'this is a string to encrypt';
// MySQL Padding
$pad_len = 16 - (strlen($plainData) % 16);
$plainData = str_pad($plainData, (16 * (floor(strlen($plainData) / 16) + 1)), chr($pad_len));
 
// Secret key
$sixteenByteKey = '1234567890123456'; // 128 bits, max allowable by MySQL
mt_srand();
$td = mcrypt_module_open(MCRYPT_RIJNDAEL_128, '', MCRYPT_MODE_ECB, '');
mcrypt_generic_init($td, $sixteenByteKey, false);
$encrypted = mcrypt_generic($td, $plainData);
mcrypt_generic_deinit($this->td);
 
MySQL

Code: Select all

 
insert into mytbl (mycol) values (?); // $encrypted
 

MySQL Encrypt / PHP Decrypt
MySQL

Code: Select all

 
insert into mytbl (mycol) values (aes_encrypt('this is a string to encrypt', '1234567890123456'));
select mycol from mytbl;
 
PHP

Code: Select all

 
// Loop through results, and for each result, do this -
$sixteenByteKey = '1234567890123456';
$dec = @mcrypt_decrypt(MCRYPT_RIJNDAEL_128, $sixteenByteKey, $mycolValue, MCRYPT_MODE_ECB);
return rtrim($dec, ((ord(substr($dec, strlen($dec) - 1, 1)) >= 0 and ord(substr($dec, strlen($dec) - 1, 1 ) ) <= 16 ) ? chr(ord(substr($dec, strlen($dec ) - 1, 1))): null) );
 
User avatar
kaisellgren
DevNet Resident
Posts: 1675
Joined: Sat Jan 07, 2006 5:52 am
Location: Lahti, Finland.

Re: PHP mcrypt interoperating with mysql aes_encrypt/aes_decrypt

Post by kaisellgren »

rmgraci wrote:I'm trying with a key that is 32 bytes in length.
To get 256-bit keys.

1) Download MySQL source.
2) Open up "include/my_aes.h".
3) Edit:

Code: Select all

#define AES_KEY_LENGTH 128
to:

Code: Select all

#define AES_KEY_LENGTH 256
4) Save changes.
5) Compile.
User avatar
William
Forum Contributor
Posts: 332
Joined: Sat Oct 25, 2003 4:03 am
Location: New York City

Re: PHP mcrypt interoperating with mysql aes_encrypt/aes_decrypt

Post by William »

rmgraci wrote:Here's some code:

SQL ENCRYPT / PHP DECRYPT

SQL

Code: Select all

 
insert into debug (debugCol) values (aes_encrypt('this will be encrypted', '12345678901234567890123456789012'));
 
PHP

Code: Select all

 
// Some code before this to fetch encrypted value from DB via query "select debugCol from debug". Store in $encryptedFromDb
mcrypt_decrypt(MCRYPT_RIJNDAEL_128, '12345678901234567890123456789012', $encryptedFromDb, MCRYPT_MODE_ECB);
 
Do you "really" need to have the original string and key sent in the MySQL query? If you ever decide to log your query's or you have slow query log enabled there is a chance the original information will be saved in plain text along with the key.
User avatar
kaisellgren
DevNet Resident
Posts: 1675
Joined: Sat Jan 07, 2006 5:52 am
Location: Lahti, Finland.

Re: PHP mcrypt interoperating with mysql aes_encrypt/aes_decrypt

Post by kaisellgren »

William wrote:Do you "really" need to have the original string and key sent in the MySQL query? If you ever decide to log your query's or you have slow query log enabled there is a chance the original information will be saved in plain text along with the key.
That is right. Moreover, if you are connecting to a remove server without SSL you are exposing those values.
dhavalmak77
Forum Newbie
Posts: 1
Joined: Thu May 20, 2021 1:22 am

Re: PHP mcrypt interoperating with mysql aes_encrypt/aes_decrypt

Post by dhavalmak77 »

How to sort ascending-descending order in encrypted fields ?
Post Reply