MySql compatible AES encryption / decryption in Java

MySQL has an aes_encrypt/aes_decrypt pair of functions (Encryption and Compression Functions) that enable encryption and decryption of data using the official AES algorithm.

The functions are easy to use (select AES_ENCRYPT(‘text’,’password’)) and the result is easy to store (insert into secrets values HEX(AES_ENCRYPT(‘text’,’password’))) as hex values.

I used this technique for a while but i wanted to have a more database agnostic version of this encryption and tried to build the same methods with java.

Although it was relatively easy to find the exact cipher mode (which is AES/ECB/PKCS5Padding), i had a real hard time figuring out how the key is calculated from the given password (the key must be 16bytes long, per default MySql uses AES-128). It turns out that the MySQL algorithm just or’s the bytes of a given passphrase against the previous bytes if the password is longer than 16 chars and just leaves them 0 when the password is shorter than 16 chars. So you can generate a secret key spec in Java for an aes_encrypt/decrypt compatible cipher like so:

import java.io.UnsupportedEncodingException;
 
import javax.crypto.Cipher;
import javax.crypto.spec.SecretKeySpec;
 
import org.apache.commons.codec.binary.Hex;
 
public class Demo {
	public static SecretKeySpec generateMySQLAESKey(final String key, final String encoding) {
		try {
			final byte[] finalKey = new byte[16];
			int i = 0;
			for(byte b : key.getBytes(encoding))
				finalKey[i++%16] ^= b;			
			return new SecretKeySpec(finalKey, "AES");
		} catch(UnsupportedEncodingException e) {
			throw new RuntimeException(e);
		}
	}
 
	public static void main(String... args) throws Exception {
		// Decrypt
		final Cipher decryptCipher = Cipher.getInstance("AES");	        				
		decryptCipher.init(Cipher.DECRYPT_MODE, generateMySQLAESKey("your super secret passphrase", "UTF-8"));
		System.out.println(new String(decryptCipher.doFinal(Hex.decodeHex("56A34D7AB6225616799F6559AA388F07C2C9E53983111BDD5F49F36461AAD789".toCharArray()))));
 
		// Encrypt
		final Cipher encryptCipher = Cipher.getInstance("AES");	        				
		encryptCipher.init(Cipher.ENCRYPT_MODE, generateMySQLAESKey("your super secret passphrase", "UTF-8"));		
		System.out.println(String.format("Select aes_decrypt(unhex('%s'), 'your super secret passphrase');", new String(Hex.encodeHex(encryptCipher.doFinal("Hallo nach Aachen".getBytes("UTF-8")))))); 
	}
}

You need Commons Codec to run these.

This isn’t probably the most secure solution from a cryptographic point of view but it just replicates the built-in MySql function for other databases or just for interoperability. I hope to save someone else time with this as i spent about days about those view lines.

13 comments

Post a comment | Trackback URI | RSS 2.0 feed for those comments

Christopher Chaney wrote:

Thanks so much for this! I’ve been pulling my hair out for two days looking for a way to replicate mysql’s encryption in java. Worked like a charm.

Posted on October 30, 2012 at 4:50 PM | Permalink
Michael wrote:

Hi Christopher,

i know that feeling to well 😉

You’re welcome.

I’ve just added a flattr button to this blog, maybe you’re the first to try it.

Happy halloween 🙂

Posted on October 31, 2012 at 6:25 PM | Permalink
John wrote:

Thank you for posting this! I implemented an encryption routine in Pentaho Data Integrator, and I could not figure out why I couldn’t decrypt from MySQL. This was it.

Posted on December 11, 2012 at 6:52 PM | Permalink
Michael wrote:

You’re welcome John, thanks for your feedback.

Posted on December 11, 2012 at 10:04 PM | Permalink
Tom wrote:

Thanks for providing the code. Saved me Time 😀

Posted on August 2, 2013 at 5:06 PM | Permalink
Michael wrote:

You’re welcome!

Posted on August 2, 2013 at 9:55 PM | Permalink
jose wrote:

Wow that worked. Thank you!

Posted on November 9, 2018 at 5:52 PM | Permalink
Vlad wrote:

Two questions:

1. When you say “or’s”, did you mean “xor’s”?
2. It turns out that the MySQL algorithm just or’s the bytes of a given passphrase against the previous bytes if the password is longer than 16 chars and just leaves them 0 when the password is shorter than 16 chars. – according to this https://dev.mysql.com/doc/refman/8.0/en/encryption-functions.html#function_aes-encrypt – it seems that the length is not always 16. What do you think?

Posted on August 29, 2019 at 7:04 PM | Permalink
Davis Osiemo wrote:

Thanks mate! Looking for a Scala implementation of this

Posted on May 12, 2020 at 1:27 PM | Permalink
Sun Rongxin wrote:

Awesome！ It gives me a great help. Thanks for your work

Posted on January 10, 2021 at 12:40 PM | Permalink
Mate wrote:

Hi. This is very helpful. But what would need to be done if the mysql functions used an initialization vector? Like so:

mysql> SET @crypt_str = AES_ENCRYPT(‘text’,@key_str,@init_vector);
mysql> SELECT AES_DECRYPT(@crypt_str,@key_str,@init_vector);

I need a way in java to decrypt data encrypted this way in java. Thank you.

Posted on March 12, 2022 at 5:45 AM | Permalink
Indumathy JayGanesh wrote:

Thanks so much for this! working for me

Posted on March 20, 2024 at 10:39 AM | Permalink
Michael wrote:

My pleasure, thanks for your feedback.

Posted on March 22, 2024 at 4:33 PM | Permalink

One Trackback/Pingback

MySQL encrypted columns in Grails | WNX.com on December 12, 2016 at 7:28 AM

[…] and aes_decrypt(). Of course the MySQL implementation is not standard, so it took some research online to come up with code that looks like […]

info.michael-simons.eu