convert php mcrypt_encrypt and decrypt to mysql function

Question

I was encrypting and decrypting data using php mcrypt_encrypt and mcrypt_decrypt() with the following arguments:

• $cipher : MCRYPT_RIJNDAEL_256
• $key : 32 bit hash sha1, generated as follow substr(hash("sha1", $some_var),0,32)
• $data : can be number or string
• $mode : MCRYPT_MODE_CBC
• $iv : 32 bit hash sha1 generated as follow substr(hash("sha1", $other_var),0,32)

Then the generated string is passed to base64_encode and the result is the encrypted data. The final function look something like:

function encrypt($data, $key1, $key2) {
    return
            trim( base64_encode( mcrypt_encrypt(
                    MCRYPT_RIJNDAEL_256,
                    substr($key1,0,32),
                    $data,
                    MCRYPT_MODE_CBC,
                    substr($key2,0,32)
            )));
    }

i want to do the same thing using mysql, so i can decrypt data while selecting them. I want to write mysql function that encrypt my data the same as the php encrypt function above.

I searched mysql documentation and found that AES_ENCRYPT can do the same thing as mcrypt_encrypt and TO_BASE64(str) can do the function of base64_encode

CREATE DEFINER=`root`@`localhost` FUNCTION `mcrypt_encrypt`(
    _data text,
    _key1 varchar(40),
    _key2 varchar(40)  
) RETURNS varchar(40) CHARSET utf8
BEGIN
    DECLARE encryData varchar(40);        
    SET @@session.block_encryption_mode = 'aes-256-cbc';
    SET encryData = AES_ENCRYPT(
        _data,
        SUBSTRING(key1,1,32),
        SUBSTRING(key2,1,32)
    );

RETURN TO_BASE64(encryData);

END

but it not working as the php function.

the output of echo encrypt('abcdefg', $key1, $key2); where

• $key1 is '5c2787d529149d3016ce50451290df1779ec93c7'
• and $key2 is '33db3475db4badb7f6ba86489257c873610e9298' is 'FO51NxIIdOO/UQYtCTGhf2O0eq52C0AnRNTr9CxdOZc='

while the output of

Select mcrypt_encrypt(
    'abcdefg',
    '5c2787d529149d3016ce50451290df1779ec93c7',
    '33db3475db4badb7f6ba86489257c873610e9298'
) as a

is 'FQ=='

i use:

• XAMPP for Linux 5.6.8-0!
• PHP Version 5.6.8
• MySQL 5.6.24 - Source distribution
• i disable case sensitivity in mysql

Answer 1

The problem you created is that you are using RIJNDAEL in PHP, which is not the same as AES. AES is a subset of RIJNDAEL, which means that using RIJNDAEL may use parameters that are unavailable to AES - so an encryption with RIJNDAEL may not be decryptable when only using AES.

There is a question on StackOverflow detailing this: Differences Between Rijndael and AES

The key difference: AES always uses 128 bit blocks, and allows using 128, 192 and 256 bit length for the key (with an attack published suggesting that using a 128 bit key is the best security because longer keys may be weakened below 128 bit - IIRC it was in the area 112 bit, still enough, but you never know).

RIJNDAEL as offered by PHP specifies the BLOCK LENGTH - RIJNDAEL-256 uses 256 bits of block length, allowing for 128, 192 or 256 bits of key length. The only scheme compatible with AES is RIJNDAEL-128 with 128 bits of block length.

Two remarks about your other crypto options: I don't particularly like the idea that the key is the result of a hash function, because this reduces the amount of different characters to 16. An attacker, knowing that only 32 characters of hex have been used will truly welcome this info because it reduces the search effort tremendously. 32 bytes 0-255: 1.1E+77 combinations. 32 bytes of 16 chars: 1.2E+24. Using 62 lowercase, uppercase and digits: 2.2E+57 combinations.

Also the initialization vector should be purely random. The general usage is that the created IV is added to the front of the cipher result and split from there before decrypting. This is to prevent encrypting the same clear text to the same cipher text - randomness is simply the best choice you can make - anything else may weaken your cryptographic strength.

One more thing: You have to take care of padding the data you have to full blocks. There are several padding schemes available, starting with "pad with zero bytes", but the current recommended padding scheme seems to be https://en.wikipedia.org/wiki/Padding_(cryptography)#PKCS7