PHP mcrypt interoperating with mysql aes_encrypt/aes_decrypt
Moderator: General Moderators
PHP mcrypt interoperating with mysql aes_encrypt/aes_decrypt
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!
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.
Re: PHP mcrypt interoperating with mysql aes_encrypt/aes_decrypt
Here's some code:
SQL ENCRYPT / PHP DECRYPT
SQL
PHP
SQL ENCRYPT / PHP DECRYPT
SQL
Code: Select all
insert into debug (debugCol) values (aes_encrypt('this will be encrypted', '12345678901234567890123456789012'));
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
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
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)
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
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
MySQL
MySQL Encrypt / PHP Decrypt
MySQL
PHP
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);
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;
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) );
- 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
To get 256-bit keys.rmgraci wrote:I'm trying with a key that is 32 bytes in length.
1) Download MySQL source.
2) Open up "include/my_aes.h".
3) Edit:
Code: Select all
#define AES_KEY_LENGTH 128Code: Select all
#define AES_KEY_LENGTH 2565) Compile.
Re: PHP mcrypt interoperating with mysql aes_encrypt/aes_decrypt
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.rmgraci wrote:Here's some code:
SQL ENCRYPT / PHP DECRYPT
SQLPHPCode: Select all
insert into debug (debugCol) values (aes_encrypt('this will be encrypted', '12345678901234567890123456789012'));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);
- 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
That is right. Moreover, if you are connecting to a remove server without SSL you are exposing those values.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.
-
dhavalmak77
- Forum Newbie
- Posts: 1
- Joined: Thu May 20, 2021 1:22 am
Re: PHP mcrypt interoperating with mysql aes_encrypt/aes_decrypt
How to sort ascending-descending order in encrypted fields ?