AES_ENCRYPT in MySQL and PHP encryption are not giving the same output.

[MaheshBabuKere]

hi all,

Here i am getting problem with encryption of data using PHP and MySQL. Actually we need to encrypt the data with 256 bit. So we have downloaded and compiled the source of MySQL and installed in CentOS. The problem is, suppose we encrypt the data using the PHP function(mcrypt_encrypt() with 256 bit) and if we decrypt the same data using MySQL decrypt function(AES_DECRYPT()) it is not giving the same output. Can you please suggest how to proceed?

[guido2004]

Choose to do both the encrypting and decrypting with PHP or MySQL.

[Aleksejs]

I suspect that MYSQL uses ECB cipher mode, while mcrypt_ecrypt() uses other block cipher mode by default.

[MaheshBabuKere]

Even I too used the ECB in mycrypt_encrypt() function but the output is deferring.

Code snippet:

For Decryption:

$iIVSize = mcrypt_get_iv_size( MCRYPT_RIJNDAEL_256, MCRYPT_MODE_ECB );
$strIV = mcrypt_create_iv( $iIVSize, MCRYPT_RAND );
$strPlaneText = mcrypt_decrypt( MCRYPT_RIJNDAEL_256, $pSecKey, $pContent, MCRYPT_MODE_ECB, $strIV );
return rtrim( $strPlaneText, ( ( ord( substr( $strPlaneText, strlen( $strPlaneText ) - 1, 1 )) >= 0 and ord( substr( $strPlaneText, strlen( $strPlaneText ) - 1, 1 ) ) <= 32 ) ? chr( ord( substr( $strPlaneText, strlen( $strPlaneText ) - 1, 1 ) ) ): null ) ); 

For Encryption:

$iIVSize = mcrypt_get_iv_size( MCRYPT_RIJNDAEL_256, MCRYPT_MODE_ECB );
$strIV = mcrypt_create_iv( $iIVSize, MCRYPT_RAND );
$pContent = str_pad( $pContent, ( 32*( floor( strlen( $pContent ) / 32 ) + ( strlen( $pContent ) % 32 == 0 ? 2 : 1 ) ) ), chr( 32 - ( strlen( $pContent ) % 32 ) ) );
$strCypherText = mcrypt_encrypt( MCRYPT_RIJNDAEL_256, $pSecKey, $pContent, MCRYPT_MODE_ECB, $strIV );
return $strCypherText; 

But when I encrypt data with MySQL AES_ENCRYPT() function and decrypt the data with the function written in PHP with the same key it is not giving the correct output.

[guido2004]

Let me repeat myself: choose to do both the encrypting and decrypting with PHP or MySQL. Not one with PHP and the other with MySQL.

[MaheshBabuKere]

Let me repeat myself: choose to do both the encrypting and decrypting with PHP or MySQL. Not one with PHP and the other with MySQL.

But i have some requirement in which we need to encrypt the data using PHP function and decrypt the data using MySQL function. SO we have chosen this way. Please suggest which mode SQL uses and My PHP function is using the correct mode or not?

[VictorCT]

You should look into TDE for MySQL by CritoTECH. It's called ezNcrypt and it allows you to do encryption without making any changes to your application.