MySQL Compatible PHP AES Encryption / Decryption

By Michael Kramer on October 24th 2014 @ 3:23 am

MySQL has a unique way of handling AES Encryption when you use a key that is larger than maximum allowed for the encryption algorithm. MySQL uses by default RIJNDAEL 128 bit encryption algorithm.

With RIJNDAEL 128, the allowed key size is 128 bits, however, often-times, people get this confused and up using a key that is 128 bytes long. MySQL will not error when you give it a longer (or shorter) key. However, in PHP, when using mcrypt with the same algorithm, and the same key, you will end up getting a warning that the key size is to large for the algorithm. Not only this, but it will simply truncate the keysize to 128 bits, and the encryption will not be compatible with MySQLs native AES_ENCRYPT and AES_DECRYPT functions.

I came across a person who had the same issue and created a fix for it in Java. ( http://www.justskins.com/forums/aes-encrypt-decrypt-key-216457.html#3 ) Below is how to implement the fix in PHP.

function makeMySQLCompatible($paymentAesKey){
        $finalKey = array();
        $i = 128 / 8;
        
        while($i != 0){
            $finalKey[] = 0x00;
            $i--;
        }
        
        for($i = 0, $d = 0; $i < strlen($paymentAesKey); $i++, $d++){
            if($d == count($finalKey)){
                $d = 0; //reset location of final key to 0
            }
            
            $finalKey[$d] ^= ord($paymentAesKey[$i]);
        }
        
        $output = '';
        foreach($finalKey as $char){
            $output .= chr($char);
        }
        
        return $output;
    }

Simply pass in your AesKey through this function first, before doing your encryption / decryption. To handle the encryption / decryption you must have mcrypt, and the following lines show how to encrypt / decrypt data.

function encrypt($data, $aesKey){  
        return mcrypt_encrypt(MCRYPT_RIJNDAEL_128, $aesKey, $data, MCRYPT_MODE_ECB);
}

function decrypt($data, $aesKey){        
        return mcrypt_decrypt(MCRYPT_RIJNDAEL_128, $aesKey, $data, MCRYPT_MODE_ECB);
}

$originalAesKey = 'e0f1b02877dd491099387f5d2ab16f83e0f1b02877dd491099387f5d2ab16f83e0f1b02877dd491099387f5d2ab16f83e0f1b02877dd491099387f5d2ab1ghyt'; //128 bytes way to long

$aesKey = makeMySQLCompatible($originalAesKey); 
//function that mimics how mysql handles long aes keys

$encrypted = encrypt('test', $aesKey);

$decrypted = decrypt($encrypted, $aesKey); //test

//MySQL Statements
$sql = "SELECT AES_DECRYPT('$encrypted', '$originalAesKey');";

$sql = "SELECT AES_ENCRYPT('$decrypted', '$originalAesKey');"; 
//same as $encrypted above

 

comments powered by Disqus

MySQL Compatible PHP AES Encryption / Decryption

By Michael Kramer on October 24th 2014 @ 3:23 am

MySQL has a unique way of handling AES Encryption when you use a key that is larger than maximum allowed for the encryption algorithm. MySQL uses by default RIJNDAEL 128 bit encryption algorithm.

With RIJNDAEL 128, the allowed key size is 128 bits, however, often-times, people get this confused and up using a key that is 128 bytes long. MySQL will not error when you give it a longer (or shorter) key. However, in PHP, when using mcrypt with the same algorithm, and the same key, you will end up getting a warning that the key size is to large for the algorithm. Not only this, but it will simply truncate the keysize to 128 bits, and the encryption will not be compatible with MySQLs native AES_ENCRYPT and AES_DECRYPT functions.

I came across a person who had the same issue and created a fix for it in Java. ( http://www.justskins.com/forums/aes-encrypt-decrypt-key-216457.html#3 ) Below is how to implement the fix in PHP.

function makeMySQLCompatible($paymentAesKey){
        $finalKey = array();
        $i = 128 / 8;
        
        while($i != 0){
            $finalKey[] = 0x00;
            $i--;
        }
        
        for($i = 0, $d = 0; $i < strlen($paymentAesKey); $i++, $d++){
            if($d == count($finalKey)){
                $d = 0; //reset location of final key to 0
            }
            
            $finalKey[$d] ^= ord($paymentAesKey[$i]);
        }
        
        $output = '';
        foreach($finalKey as $char){
            $output .= chr($char);
        }
        
        return $output;
    }

Simply pass in your AesKey through this function first, before doing your encryption / decryption. To handle the encryption / decryption you must have mcrypt, and the following lines show how to encrypt / decrypt data.

function encrypt($data, $aesKey){  
        return mcrypt_encrypt(MCRYPT_RIJNDAEL_128, $aesKey, $data, MCRYPT_MODE_ECB);
}

function decrypt($data, $aesKey){        
        return mcrypt_decrypt(MCRYPT_RIJNDAEL_128, $aesKey, $data, MCRYPT_MODE_ECB);
}

$originalAesKey = 'e0f1b02877dd491099387f5d2ab16f83e0f1b02877dd491099387f5d2ab16f83e0f1b02877dd491099387f5d2ab16f83e0f1b02877dd491099387f5d2ab1ghyt'; //128 bytes way to long

$aesKey = makeMySQLCompatible($originalAesKey); 
//function that mimics how mysql handles long aes keys

$encrypted = encrypt('test', $aesKey);

$decrypted = decrypt($encrypted, $aesKey); //test

//MySQL Statements
$sql = "SELECT AES_DECRYPT('$encrypted', '$originalAesKey');";

$sql = "SELECT AES_ENCRYPT('$decrypted', '$originalAesKey');"; 
//same as $encrypted above

 

comments powered by Disqus