MySql compatible AES encryption / decryption in Java

July 18, 2011 by Michael

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.


  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 October 30, 2012 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 October 31, 2012 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 December 11, 2012 at 6:52 PM | Permalink
  4. Michael wrote:

    You’re welcome John, thanks for your feedback.

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

    Thanks for providing the code. Saved me Time 😀

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

    You’re welcome!

    Posted on August 2, 2013 at 9:55 PM | Permalink
One Trackback/Pingback
  1. MySQL encrypted columns in Grails | 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 […]

Post a Comment

Your email is never published. We need your name and email address only for verifying a legitimate comment. For more information, a copy of your saved data or a request to delete any data under this address, please send a short notice to from the address you used to comment on this entry.
By entering and submitting a comment, wether with or without name or email address, you'll agree that all data you have entered including your IP address will be checked and stored for a limited time by Automattic Inc., 60 29th Street #343, San Francisco, CA 94110-4929, USA. only for the purpose of avoiding spam. You can deny further storage of your data by sending an email to, with subject “Deletion of Data stored by Akismet”.
Required fields are marked *