Trouble using AES to decrypt information from MySQL DB

[Mannequin-]

Hi there,

I inherited a website that was developed in CFMX & MS SQL several years ago. The site was moved to a CF10 server with a MySQL DB which seems to have caused a few issues here and there. One issue I am currently experiencing is decrypting information stored in a SQL database. I have the key it was inserted with, and I know that it was inserted via AES.

So here's the query I'm trying to run:

Select *, AES_DECRYPT(cardnumber,'#cckey#') as ccnumber from orderinfo where orderno='123'

When I output the query and try to pull up #ccnumber# it throws the hatred generic "Internal Server Error 500" and gets me nowhere. If I try to output #cardnumber# instead, the actual column name in the database, I am given a long string of jarbled letters, numbers, and symbols. I figured that mabe I could use the Decrypt() function in ColdFusion to decrypt that if it's not working at the database level, but I don't get anywhere with this method either; I get the same generic error.

What would you recommend I do in this case?

Any help is appreciated.

[Mannequin-]

Moving forward, what might be a better method of inserting credit cards into our DB for online orders?

Would you recommend using the Encrypt() function in ColdFusion to insert and then the Decrypt() function to view in our administrative backend?

[Mannequin-]

Well, no one responded to me but I ended up using the method I outlined above and it worked. Essentially, I encrypted the form data (credit card information) using the Encrypt() function in ColdFusion and inserted that into the database with the key. Then on output I used the Decrypt() function, rather than having MySQL do the encrypting/decrypting. The only problem is that the information has to go into a VARCHAR field in the MySQL DB, which I hear is bad, (BLOB being preferred); but it works at least.

[12Robots]

I hope you're still using AES and not the default CFMX algorithm of encrypt() and decrypt().  As for how you store it, storing in a varchar should be fine if the resulting cipher is encoded as a string and not binary.

jason

[Mannequin-]

I tried to force AES but was getting the "Internal Server" error message and couldn't get beyond that, so I let it go to whatever the default is. The code goes something like this:

     <cfset ccnumber = encrypt(form.ccentry, thekey)>

Then I run a basic SQL query to insert the order into the database along with this encrypted information. When I tried adding "AES" after the 'key' component of the function it threw an error. I tried something like:

     <cfset ccnumber = encrypt(form.ccentry, thekey, "AES")>

...and various versions, but to no avail. After banging my head a few times I gave up. Tell me if I've gone wrong here. Thank you for the response!

[12Robots]

You've gone wrong.

The default "encryption" algortihm in CF is barely encryption at all.  Calling CFMX compat "encryption" is like calling a wagon a vehicle. In the strictest sense it may be a vehicle, but it is not a very good one.

CFMX Compat crypto is certainly not acceptable for securing credit card information.

I'd love to help you figure out what you're doing wrong, but "internal server" is not a very helpful error message.  Can you offer anything else?  Also, you should not be using just AES. You should be using AES with Cipher Block Chaining. It would look something like this:

<cfset ccnumber = encrypt(form.ccentry, thekey, "AES?CBC/PKCS5Padding")>

Chances are that the error you are getting is related to the key size. Your key needs to be 128-bit. Anything less is unacceptable and anything more will require the unlimited strength policy files in your CF install to be upgraded, which is not hard to do, but perhaps you just need ot get what you have working first.

Cryptography is really, really hard to get right.  There is a LOT more to it than just using the right function. Key management is especially difficult. If you are just picking a key, throwing it in a variable and dropping it into your function with a plan to use it forever then you have also gone wrong there.

Here are some references to help:

http://helpx.adobe.com/coldfusion/kb/strong-encryption-coldfusion-mx-7.html

http://csrc.nist.gov/groups/ST/toolkit/key_management.html

[Mannequin-]

I really appreciate the feedback, thank you. I inherited this stupid old website from a bunch of sloppy developers and I haven't done any encryption before or any real ColdFusion programming since about 2004. I'm the only one around who knows something, so I was the lucky candidate. On the plus side, we will be hiring a company to revamp the website and redo everything so the burden of proper encryption and everything else will eventually fall onto them.

I'm writing this at home but tomorrow I'll take a look at your links and encryption methods. Thanks again!