Skip to content

Tag Archives: AES

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 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.