Skip to content
accelerando

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.

6 Comments

  1. 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 30-Oct-12 at 4:50 pm | Permalink
  2. 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 31-Oct-12 at 6:25 pm | Permalink
  3. 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 11-Dec-12 at 6:52 pm | Permalink
  4. Michael wrote:

    You’re welcome John, thanks for your feedback.

    Posted on 11-Dec-12 at 10:04 pm | Permalink
  5. Tom wrote:

    Thanks for providing the code. Saved me Time :D

    Posted on 02-Aug-13 at 5:06 pm | Permalink
  6. Michael wrote:

    You’re welcome!

    Posted on 02-Aug-13 at 9:55 pm | Permalink

Post a Comment

Your email is never published nor shared. Required fields are marked *
*
*

*