MySql and Java interoperability in AES 256 encryption/decryption-CodePudding

The following encryption and decryption works fine in mysql (aes-256-cbc) mode

SET block_encryption_mode = 'aes-256-cbc';

select
cast(
aes_decrypt(
from_base64('StThdNXA CWvlg of/heJQ=='),
sha2(concat('ssshhhhhhhhhhh!!','ENCRYPTION_KEY$&'),256),
'ssshhhhhhhhhhh!!'
)
as char);

select to_base64(aes_encrypt(
'test_value',
sha2(concat('ssshhhhhhhhhhh!!','ENCRYPTION_KEY$&'),256),
'ssshhhhhhhhhhh!!'
));

I am trying to decrypt value that was encrypted in mysql but no luck.

The following is the key in my mysql query sha256(salt key)

select sha2(concat('ssshhhhhhhhhhh!!','ENCRYPTION_KEY$&'),256);

The same value I am able to get in java :

Hashing.sha256().hashString("ssshhhhhhhhhhh!!ENCRYPTION_KEY$&", StandardCharsets.UTF_8).toString();

Is there a custom way I can make bouncy castle/other API use same secret key to decrypt ?

CodePudding user response：

Reason for the issue:

sha2() on the MySQL side and the Java/Guava code return the SHA256 hash hex encoded, i.e. the 32 bytes hash is represented as a string consisting of 64 hex digits:

select sha2(concat('ssshhhhhhhhhhh!!','ENCRYPTION_KEY$&'),256)
/* 973825f9fc19a51ccacdd91b4b5a50c88d8b600e733d47f7b083426e910f3ae6 */

This value is implicitly UTF-8 encoded on the MySQL side by aes_encrypt() producing a 64 bytes value, which is incompatible with the allowed AES key sizes of 16, 24 and 32 bytes. In this case aes_encrypt() applies a key derivation function, i.e. the key material is interpreted as a passphrase and the actual key is derived from it.

The reason for your failed decryption is most likely that you have not implemented the logic of the key derivation function!

How can the problem be solved? For the following suggestions, SET block_encryption_mode = 'aes-256-cbc' is assumed:

Approach 1 - the easy way:

Just use a valid AES key size. In the current code this can be achieved e.g. by hex decoding the hash with unhex():

select to_base64(aes_encrypt(
    'test_value',
    unhex(sha2(concat('ssshhhhhhhhhhh!!','ENCRYPTION_KEY$&'),256)),
    'ssshhhhhhhhhhh!!'
));
/* L9qmVxXxg1eUh2z27fdhWg= */

The ciphertext can be decrypted with any reliable crypto library, see e.g. here a decryption with CyberChef.

Approach 2 - the harder way:

Implement the key derivation function used by MySQL. This KDF works as follows: The passphrase is split into segments of the target key size. The target key initially contains only 0x00 values. Then a bitwise XOR is performed between the target key and the passphrase (modulo the target key size).

A possible implementation for Java is:

System.out.println(generateKey("973825f9fc19a51ccacdd91b4b5a50c88d8b600e733d47f7b083426e910f3ae6", 32));
/* 01530b5a0405565c5150025d5502575401515b57500b07070d5305070651060e */

private static String generateKey(String passphrase, int keysize) {
    byte[] passphraseBytes = passphrase.getBytes(StandardCharsets.UTF_8);
    byte[] key = new byte[keysize];
    for (int i = 0, len = passphraseBytes.length; i < len; i  ) {
        key[i % keysize] = (byte)(key[i % keysize] ^ passphraseBytes[i]);
    }
    return HexFormat.of().formatHex(key);
}

With the key derived in this way, the ciphertext you posted, StThdNXA CWvlg of/heJQ==, can be decrypted, which can again be verified with CyberChef.

Instead of CyberChef, decryption can of course be done with Java using any library that supports AES/CBC. I leave the implementation to you, there are dozens of examples on the web. If a passphrase is applied instead of a valid key, use the above key derivation function.

CodePudding user response：

MySQL internally uses OpenSSL algorithm with EVP_BytesToKey as the derivation function. Check out this url

MySQL encrypt and decrypt sample:

SET block_encryption_mode = 'aes-128-cbc';
SET block_encryption_mode = 'aes-256-cbc';
select 
cast(
    aes_decrypt(
        from_base64('MKicK vAcZkq/g3wpTKxVg=='),
        'ENCRYPTION_KEY$&',
        'ssshhhhhhhhhhh!!'
    )
as char);


select to_base64(aes_encrypt(
'test_value',
'ENCRYPTION_KEY$&',
'ssshhhhhhhhhhh!!'
));

There is a JAR that supports this EVP_BytesToKey key derivation function.

 JAR : not-going-to-be-commons-ssl-0.3.19

public class AnotherTry {

byte[] key = "ENCRYPTION_KEY$&".getBytes(StandardCharsets.UTF_8);
byte[] iv = "ssshhhhhhhhhhh!!".getBytes(StandardCharsets.UTF_8);

String cipher = "AES-128-CBC";

@Test
public void testEncrypt() throws Exception {
    byte[] data = "test_message".getBytes(StandardCharsets.UTF_8);
    byte[] encrypted = OpenSSL.encrypt(cipher, key, iv, data, true);
    System.out.println(new String(encrypted));
}

@Test
public void testDecrypt() throws GeneralSecurityException, IOException {
    byte[] encrypted = "rQ8Y0ClNu5d4ODZQ3XcQtw==".getBytes(StandardCharsets.UTF_8);
    byte[] decrypted = OpenSSL.decrypt(cipher, key, iv, encrypted);
    System.out.println(new String(decrypted));
}

}

This way interoperability is achieved, finally ! Hope this helps someone who's trying to do the same.