So what happens if you want to use the mysql functions AES_ENCRYPT / AES_DECRYPT to handle data encryption, but you might also need to write a tool that takes the encrypted data and accesses or updates it outside of the database? This operation can be hit or miss depending on your library's implementation of how it handles the encryption key passed to the actual encryption function. There is no standard on how this is handled.

AES only defines that it accepts an encryption key of AES_KEY_LENGTH/8 bytes. So for 128 bit encyption you need to give it a 16 byte key.

In mysql you can do:
AES_ENCRYPT('mykey','here is a string to encrypt');

But we just passed it a 5 character string, where does the 16 byte key come from? This is the part that can differ between libraries. MySQL takes your string and converts it like this:

static int my_aes_create_key(KEYINSTANCE *aes_key,
enum encrypt_dir direction, const char *key,
int key_length)
{
uint8 rkey[AES_KEY_LENGTH/8]; /* The real key to be used for encryption */
uint8 *rkey_end=rkey+AES_KEY_LENGTH/8; /* Real key boundary */
uint8 *ptr; /* Start of the real key*/
const char *sptr; /* Start of the working key */
const char *key_end=key+key_length; /* Working key boundary*/

bzero((char*) rkey,AES_KEY_LENGTH/8); /* Set initial key */

for (ptr= rkey, sptr= key; sptr <>
{
if (ptr == rkey_end)
ptr= rkey; /* Just loop over tmp_key until we used all key */
*ptr^= (uint8) *sptr;
}



if (direction == AES_DECRYPT)
aes_key->nr = rijndaelKeySetupDec(aes_key->rk, rkey, AES_KEY_LENGTH);
else
aes_key->nr = rijndaelKeySetupEnc(aes_key->rk, rkey, AES_KEY_LENGTH);
return 0;
}

The important bit is in green. That's the code from the mysql source, in mysys/my_aes.c . The algorithm just creates a 16 byte buffer set to all zero, then loops through all the characters of the string you provide and does an assignment with bitwise OR between the two values. If we iterate until we hit the end of the 16 byte buffer, we just start over from the beginning doing ^=. For strings shorter than 16 characters, we stop at the end of the string.

So for our sample password above, the resulting key would be this:

select hex('mykey\0\0\0\0\0\0\0\0\0\0\0');
+------------------------------------+
| hex('mykey\0\0\0\0\0\0\0\0\0\0\0') |
+------------------------------------+
| 6D796B65790000000000000000000000 |
+------------------------------------+

There are two important aspects of this method:

1. For any password you supply that is less than 16 bytes, the resulting AES key will simply be that value padded to 16 bytes with null zeros

2. For any password that is exactly 16 bytes, the resulting AES key is exactly the same as the supplied password.

*WHEW* That was quite a bit, but now lets see how that applies to our attempt to descrypt mysql's values from outside the database or create encrypted strings that mysql can decrypt. For this example lets look at java:

From: http://java.sun.com/developer/technicalArticles/Security/AES/AES_v1.html

       // Get the KeyGenerator
   KeyGenerator kgen = KeyGenerator.getInstance("AES");
   kgen.init(128); // 192 and 256 bits may not be available

   // Generate the secret key specs.
   SecretKey skey = kgen.generateKey();
   byte[] raw = skey.getEncoded();
   SecretKeySpec skeySpec = new SecretKeySpec(raw, "AES");

   // Instantiate the cipher
   Cipher cipher = Cipher.getInstance("AES");

   cipher.init(Cipher.ENCRYPT_MODE, skeySpec);

But wait, where is our password? Well this example they give you has you use KeyGenerator to create a key randomly. That's not very usefull if you ever hope to decrypt your data later. More commonly for password based encryption we would do something like:


byte[] keyBytes = new byte[] { 0x6D, 0x79, 0x6B, 0x65, 0x79, 0x00, 0x00, 0x00, 0x00, 0x00,
0x00, 0x00, 0x00, 0x00, 0x00, 0x00 };

SecretKeySpec key = new SecretKeySpec(keyBytes, "AES");

// Instantiate the cipher
Cipher cipher = Cipher.getInstance("AES");

cipher.init(Cipher.ENCRYPT_MODE, key);


In this keyBytes is simply a byte array defining our 16 byte encryption key. Notice the values, they're the same as our password above padded out with null zeros! In this example the encrypted output would exactly match what mysql would produce. Yay, compatable encryption. I'll leave it as an exercise to the reader to create a function in their language of choice to take a string as input and produce a 16 byte array that matches the result of the mysql source above.