Trouble using AES to decrypt information from MySQL DB

Forum|Forum|13 years ago
December 4, 2012
1 reply
1961 views

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.

This topic has been closed for replies.

M

Mannequin-Author

Participating Frequently

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?

M

Mannequin-Author

Participating Frequently

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.

M

Mannequin-Author

Participating Frequently

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

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:

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:

...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!

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded