Page 1 of 1

PHP mcrypt interoperating with mysql aes_encrypt/aes_decrypt

Posted: Mon Mar 16, 2009 9:32 pm
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!

Re: PHP mcrypt interoperating with mysql aes_encrypt/aes_decrypt

Posted: Tue Mar 17, 2009 1:15 am
by Mordred
Code?

Re: PHP mcrypt interoperating with mysql aes_encrypt/aes_decrypt

Posted: Tue Mar 17, 2009 9:19 am
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);
 

Re: PHP mcrypt interoperating with mysql aes_encrypt/aes_decrypt

Posted: Tue Mar 17, 2009 10:07 am
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?

Re: PHP mcrypt interoperating with mysql aes_encrypt/aes_decrypt

Posted: Tue Mar 17, 2009 10:52 am
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)

Re: PHP mcrypt interoperating with mysql aes_encrypt/aes_decrypt

Posted: Tue Mar 17, 2009 11:29 am
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) );
 

Re: PHP mcrypt interoperating with mysql aes_encrypt/aes_decrypt

Posted: Wed Mar 18, 2009 9:13 am
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.

Re: PHP mcrypt interoperating with mysql aes_encrypt/aes_decrypt

Posted: Wed Mar 18, 2009 10:58 am
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.

Re: PHP mcrypt interoperating with mysql aes_encrypt/aes_decrypt

Posted: Wed Mar 18, 2009 11:08 am
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.

Re: PHP mcrypt interoperating with mysql aes_encrypt/aes_decrypt

Posted: Thu May 20, 2021 1:28 am
by dhavalmak77
How to sort ascending-descending order in encrypted fields ?