Skip to main content
D
Digdug
Participant
November 19, 2013
Question

MySql AES_Encrypt values and Coldfusion Encrypt/Decrypt

  • November 19, 2013
  • 1 reply
  • 1310 views

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?

    This topic has been closed for replies.

    1 reply

    pete_freitag
    pete_freitag
    Participating Frequently
    November 19, 2013

    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.

    D
    DigdugAuthor
    Participant
    November 20, 2013

    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.

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices