AES, MySql and Headaches

Question

Hello everyone. I am working on a checkout page for an ecommerce site. In said site, people input their credit card information and it is saved to the order in an AES encrypted format. This worked fine when all they supported was VISA. Now that they support MasterCard and Discover (16 digit numbers), it no longer works. The insert query looks like this:

"insert into order_master set ccnum = AES_ENCRYPT(:ccnum, 'password') ...";

i have also tried the alternative format:

"insert into order_master (ccnum, ...) values (AES_ENCRYPT(:ccnum, 'password')..)";

Both to no avail. Could someone please try to point me into the right direction as to what I am doing wrong? I have gone so far as to spit out the entries query to make sure the values are inserted correctly, etc and nothing. it's quite frustrating and i am hoping someone out there can help me. thanks!

Answer 1

I don't see anyting wrong with your sql but from the mysql documentation ...

If AES_DECRYPT() detects invalid data or incorrect padding, it returns NULL. However, it is possible for AES_DECRYPT() to return a non-NULL value (possibly garbage) if the input data or the key is invalid.

This would explain by you can decrypt you get a value .. but if you encrypt and you get a null value returned, one of the two input values is probably null.

Answer 2

As it turned out, the client had the data type of the column set to varchar (20) - which failed to capture all of the binary data (of only card numbers with 16 characters before encryption). I had him change the length to 255 and it works great now. Thanks for all of your help/suggestions!