Skip to main content
M
Mannequin-
Participating Frequently
December 4, 2012
Question

Trouble using AES to decrypt information from MySQL DB

  • December 4, 2012
  • 1 reply
  • 1973 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.

1 reply

M
Mannequin-Author
Participating Frequently
December 5, 2012

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
December 10, 2012

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
12Robots
Participating Frequently
December 10, 2012

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

Firefly Community Gallery
Adobe Firefly

Remix with Firefly Community Gallery

Thousands of free creations to fall in love with and remix in Firefly.

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