Saving Personal Information (Name, Address, Phone, Email) in MySQL Database

Question

I'm going to build a web app for people to sign up and pay for a membership. The transaction information will all be processed through Authorize.NET but we save the rest of the information (Name, Membership Type, Amount, Email, Address, Phone) about the transaction in MySQL so it can be processed through an admin interface.

I'm thinking of using AES_Encrypt to save the information into a secure mysql server and then using AES_Decrypt to output the information through an admin interface for processing.

The idea is that the web server where the gift will occur (secure) will be different than the mysql server where the information will be stored and the admin interface that access the information will be on a different server as well.

My question is - does this seem like a sound approach? Is there something better I should be doing to encrypt this information in case it was compromised?

Answer 1

Sorry to disappoint you, but your security model doesnt work in reality. Let me explain by using an example workflow of a user editing his profile.

1. The user logs in, browses to his profile page and enters his data to be saved.
2. When saving, you need your symmetrical key for AES. This can be either a global password, in use for all private data on your server or a user-owned password that contains his password at some point (as all other data is publicly available / stored)

2a: Global Password

Using a global password, no matter where you store it, will always be easy to compromise. Lets assume your server requests the global password from a second server (that can be compromised itself too anyways) whenever a user (admin or not) accesses it. This can easily be sniffed as an attacker knows your internal endpoint. If you hardcode it, it can easily be read.

2b: User-owned Password

This has to contain an information so secret no one with access to the server can read it, like the password (as its supposed to be only stored inside a hash). This way the user would be able to read & save his private data but you as an admin, having the same opportunity as an attacker, wouldnt be able to read or edit them.

So what now?

Its as simple as disappointing - an attacker always has the same amount of possibilities as you (the admin) so there is no way to keep the data perfectly save from either one. What should be done instead as a matter of best-practice is the need-to-know concept - only save data you really need for your service to be available at all time like his email, name and birthdate. All other data can be saved and encrypted by a user-owned token and be used at runtime (only!) to decrypt data to offer it to the user like his address for a purchase process. This will reduce your possibilities regarding data analysis by far, but data security and broad analysis never like each other anyways.

Answer 2

if you store sensitive Customer Information please ensure that you keep the possible Risk of losing to many sensitive Informationen at once rather low. It's generally a good idea to look at the Rules of Database Normalization, for example in your case i would create a Database with an ID and Name as primary Key and would add the IDs of the Tables Mail, Address and Phone, and also Membership Type as foreign Key.

Example:

TBL_Customerdata ID Name FK_ID_Mail FK_ID_Address FK_ID_Phone FK_ID_Membership

TBL_Membership FK_ID_Membership Type Amount

TBL_Phone FK_ID_Phone Number

TBL_Address FK_ID_Address Street House Housepart FK_ID_PoSt Country

TBL_Post FK_ID_PoSt Postcode Town

TBL_Mail FK_ID_Mail Adress FK_ID_Provider

TBL_Provider

FK_ID_Provider Provider

If you use the above Scheme it would prevent an Attacker from Acessing to many Datapieces at once. AES seems for Encryption okay.

Hope this helps.