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

  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
  7. jose wrote:

    Wow that worked. Thank you!

    Posted on November 9, 2018 at 5:52 PM | Permalink
  8. 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
  9. Davis Osiemo wrote:

    Thanks mate! Looking for a Scala implementation of this

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

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

    Posted on January 10, 2021 at 12:40 PM | Permalink
  11. 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
  12. Indumathy JayGanesh wrote:

    Thanks so much for this! working for me

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

    My pleasure, thanks for your feedback.

    Posted on March 22, 2024 at 4:33 PM | Permalink
One Trackback/Pingback
  1. 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 […]

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 michael@simons.ac 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 support@wordpress.com, with subject “Deletion of Data stored by Akismet”.
Required fields are marked *