MySql AES_Encrypt values and Coldfusion Encrypt/Decrypt

[Digdug]

So, I'm having trouble figuring this out, and I haven't found an answer online.  Hoping someone here has tried something similar.

I'd like to figure out how to use the Coldfusion Encrypt function so that it generates the same binary value as MySql AES_ENCRYPT.  Similarly, how can I use Coldfusion Decrypt to decrypt values generated by AES_ENCRYPT?

I've tried:

Decrypt( QUERYCOLUMN, "AES", "hex")

Decrypt( toBase64(QUERYCOLUMN), thisKey.getKey(), "AES", "hex")

Decrypt( toString(toBase64(QUERYCOLUMN)), thisKey.getKey(), "AES", "hex")

Always returns empty string.

Thoughts?

[pete_freitag]

Since you are converting the binary value to base64 you should be specifying "base64" not "hex" as the last argument.  Also your key should be base64 encoded as well. Not promising it will work with those changes but I would start there.

[Digdug]

Thanks for the lead.  I ended up using

HEX(AES_ENCRYPT('JONATHAN', <cfqueryparam cfsqltype="cf_sql_binary" value='#BinaryDecode(thisKey.getKey(), "Base64")#'>))

in mySql to produce the same results as

<cfqueryparam cfsqltype="cf_sql_varchar"  value='#Encrypt("JONATHAN", thisKey.getKey(), "AES", "Hex")#'>

And it works great!  I didn't have to base64 encode the key - I read somewhere that CF converts the key behind the scenes, which sounded weird, but seems to be true?

Thanks for the help, Peter.