CF8 and AES MySQL AES decryption: encodings are not the same

Question

This has become more an exercise in what I am doing wrong than in a critical situation, but I would still like to see that the (simple, probably) error I am making.

I am using mysql (5.1.x) AES_ENCRYPT to encrypt a string. I use CF generateSecretKey ('AES') to make a key (I tried it with defaul and with a length of 128 and 256 bits).

So let my code look like this:

<cfset key = 'qLHVTZL9zF81kiTnNnK0Vg=='/> <cfset strToEncrypt = '4111111111111111'/> <cfquery name="i" datasource="#dsn#"> INSERT INTO table(str) VALUES AES_ENCRYPT(strToEncrypt,'#key#'); </cfquery> 

This works fine, as expected, and I can select it using SELECT AES_DECRYPT (str, '# key #') AS ... without any problems.

What I cannot do is force CF to decrypt it using something like:

  <cfquery name="s" datasource="#dsn#"> SELECT str FROM table </cfquery> <cfoutput>#Decrypt(s.str,key,'AES')#</cfoutput> 

or

  <cfoutput>#Decrypt(toString(s.str),key,'AES')#</cfoutput> 

I keep getting “Input and output encodings are not the same” (including toString () - without this I get a binary data error). The field type for the encrypted string in db is blob.

Answer 1

This entry explains that mySQL processes AES-128 keys in a slightly different way than you might expect:

. The MySQL algorithm is simple or bytes of a given passphrase versus previous bytes if the password is longer than 16 characters and just leaves them 0 when the password is shorter than 16 characters.

Not very verified, but it seems to give the same results (in the hex).

 <cfscript> function getMySQLAES128Key( key ) { var keyBytes = charsetDecode( arguments.key, "utf-8" ); var finalBytes = listToArray( repeatString("0,", 16) ); for (var i = 1; i <= arrayLen(keyBytes); i++) { // adjust for base 0 vs 1 index var pos = ((i-1) % 16) + 1; finalBytes[ pos ] = bitXOR(finalBytes[ pos ], keyBytes[ i ]); } return binaryEncode( javacast("byte[]", finalBytes ), "base64" ); } key = "qLHVTZL9zF81kiTnNnK0Vg=="; input = "4111111111111111"; encrypted = encrypt(input, getMySQLAES128Key(key), "AES", "hex"); WriteDump("encrypted="& encrypted); // note: assumes input is in "hex". either convert the bytes // to hex in mySQL first or use binaryEncode decrypted = decrypt(encrypted, getMySQLAES128Key(key), "AES", "hex"); WriteDump("decrypted="& decrypted); </cfscript> 

Note. . If you use mySQL for encryption, be sure to look at its documentation, which mentions plain text may appear in various journals (replication, history, etectera) and "can be read by anyone who has access to this information."

---

Update: The changes may have changed, but according to this 2004 bug report , the .mysql_history file .mysql_history on Unix. (Keep in mind that there may be other log files). Detailed instructions for cleaning .mysql_history can be found in the manual , but in general:

• Set the MYSQL_HISTFILE variable to / dev / null (every time you log in)
• Create .mysql_history as a symlink to / dev / null (only once)