Mysql PHP Encryption AES_ENCRYPT

Ok I know credit cards are a security issue to store.  Enough Said on that.

To save client headaches and for client ease the company has decided to keep that info in the DB.
I've been reading about AES_ENCRYPT AES_DECRYPT  

I have seen different opinions on what type field I should use.  VARCHAR or BLOB or TINYBLOB.  If VARCHAR it needs to be bigger than the size of the credit card number.  CC is 16 digits and the encryption makes it 22.

What is the best way to store the key variable?  It seems to me that if somebody hacks the server they would get that variable as well.  Which would let them unlock the card numbers.  Could I host the key on seperate server.  I guess If it is down that would be a problem... But not as big a problem as all the client card numbers getting out. ;-)

For internal fraud the only time a sales person can see the customers card number is when they first enter it.  After that it only displays the last 4 digits to the sales person so they can veriy the correct card with the customer.  

Google has provided me with plenty of problem cases with decrypting.  However it seems that most of these problems were with encrypting and decrypting text.  Since these are numbers that are always the same length I hope to avoid that problem.  Any thoughts on that?

Any general opinions other than....Don't store credit cards or Buy this software... would be appreciated.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Blob, not varchar.  Using varchar _might_ mean bad data, because of trailing space truncation or other attempts to convert binary to ASCII.

AES_ENCRYPT will turn 16 into 32, not 22 digits.  16*(trunc(string_length/16)+1) = 16*(trunc(16/16)+1) = 16*(1+1) = 32

I don't see how storing the key on a separate sever will help - if a hacker can get into one, he presumably can get into the other.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
livegirlloveAuthor Commented:
Well presumable they would have to hack the second server to get the key.  Hopefully in the mean time the hack gets stopped....In theory....

Having things reside on different servers bothers me though.  So is it best practice to store the key in a table or in the source code.
livegirlloveAuthor Commented:
tx on the math.  I was guessing...  

Will I be better using TINYBLOB since the string length of the encrypted data will only be 32 characters? Will that improve speed / decryption problems?
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

Probably not speed or decryption issues, but it will save 1 byte per record.
The best recommendation I can find regarding storing the key simply say to keep it away from the data if possible.  So don't put the key in the table.  But I'd advise against putting the key directly in the source.  Instead, put the key in a file someplace that is not in the web server's document root path.  Assign that file as strict access privileges as possible, allowing only the user running your scripts to read it.
livegirlloveAuthor Commented:
mysql_query("UPDATE results SET cc = (AES_ENCRYPT(1234567890123456,aaf33) WHERE id = $lastid ")or
   die("Your Error2 " . mysql_error());  

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE id = 169' at line 1

MySQL 4.0.24-standard

Can I check to see that AES is available?
Missing a closing parenthesis, or rather, has an extra opening one.

mysql_query("UPDATE results SET cc = AES_ENCRYPT(1234567890123456,aaf33) WHERE id = $lastid ")or
   die("Your Error2 " . mysql_error());  
livegirlloveAuthor Commented:
Thank ;-)  

For anybody else that sees this later....
mysql_query("UPDATE results SET cc = AES_ENCRYPT($cc , '$yekffa') WHERE id = $lastid ")or die("Your Error2 " . mysql_error());  
DECRYPT and Display Last 4 digits of Credit card number
$resultd = mysql_query("SELECT AES_DECRYPT(cc,'$key') AS cc FROM results WHERE id = $_GET[edit]");
$rowd = mysql_fetch_assoc($resultd);

$CCVar = substr_replace($rowd[cc],'XXXXXXXXXXXX',0,12); echo $CCVar;
Assuming that you accept Visa, Mastercard, and/or Discover.  If you take American Express, you'll need to change the number of x's and the 12 in your substr_replace.
livegirlloveAuthor Commented:
only visa/MC

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.