Skip to content
Advertisement

How to reproduce Java MessageDigest SHA-1 secret key to be used in MySQL AES_encrypt

I have a function to prepare secret key for encryption and decryption using Java:

public void prepareSecreteKey(String theKey) {
    MessageDigest sha = null;
    try {
        key = theKey.getBytes(StandardCharsets.UTF_8);
        sha = MessageDigest.getInstance("SHA-1");
        key = sha.digest(key);
        System.out.println(new String(key, StandardCharsets.UTF_8));
        key = Arrays.copyOf(key, 16);

        MessageDigest md = MessageDigest.getInstance("SHA1");
        secretKeySpec = new SecretKeySpec(key, "AES");
    } catch (NoSuchAlgorithmException e) {
        e.printStackTrace();
    }
}

However, for the old records from database, I would like to manually encrypt using a MySQL query. I have tried using the query below, but the result is different from the result by the Java application.

select to_base64(aes_encrypt('123456789032', 'aesEncryptionKey'));

Anyone can advise? or is there any other method to encrypt the existing record in database?

Advertisement

Answer

AES only allows binary keys of 128, 192 or 256 bit length.

However, in older versions of MySQL (<8.x.x) you were expected to provide a string (key_str) as key when using the aes_encrypt(str,key_str) method. Behind the scenes MySQL would then turn this string into a binary key in an undocumented way. Any attempt to replicate this outside of MySQL would require you to figure out how this is done.

This way of providing keys is now discouraged in the MySQL documentation (for a good reason) – and many additional options have been introduced to the aes_encrypt(...) method, that allows you to provide a proper key derivation function (KDF), etc. You should read up on some of the newer MySQL Encryption and Compression Functions in the MySQL documentation.

Advertisement