personal information protection in mysql database

Anything can be encrypted and since security requires to be paranoyd we should state that it worths. But I don't know how many websites do it. Personally, I would encrypt only sensitive data like passwords or credit card numbers, but if your user are secret agents then you should encrypt everything :)
Just to say that it is a decision which depends on the website and user types.

There is a big difference between passwords and other data. You need a credit card number to process payments, but you surprisingly don't need the (decrypted) password to compare it against a reentered password, you instead apply the same hashing to compare this hashes (stored and current hash).

That's enabling to store the secret without really knowing it, as by definition a hash unlike an encrytion algorithm produces something irreversibly, you don't get the clear text back. Also, you don't need to store a secret of encryption, like a key.

That said one of the regular encryptions is possible via MySQL built in AES_ENCRYPT and AES_DECRYPT, their only disadvantage is, they only encrypt data after it arrived in the database, the transfer of credit card info a customer enters (first time) is unecrypted, unless you use SSL (https) for the transfer of such data, which makes an ssl certificate a must have for working with personal data and even more sensible payment data.

There is a simple way out to have a shop using third party services for credit card processing or accept payment via paypal. The main way such services work is handling all the details needed for money transfers without your website needing to know credit card numbers or anything alike. You redirect users to paypal (or other services) at some point and they confirm payment, you even have the benefit of guaranteed payment upfront. You pay for that service, typically, but you have one less burden on you.

Bye, Olaf.

Haha, I really don't have 4 years to learn about it! Maybe I can narrow it down a bit to try get a more specific answer. At this stage, it wouldn't be credit card information because I wouldn't want the sleepless nights of worrying about users credit card details so like Olaf suggested, I would certainly send the user through paypal or stripe etc. (when I finally get to that). But at the moment let's just say a users mobile number. That is quite a sensitive thing and I wouldn't want a hacker to steal all my users mobile numbers.

The only reason I need it, is so that if the user forgets their password and tries to reset it, a code will be sent via sms to them and they need to follow a link and input it and reset their password. (yes, I am so pleased with myself as I just got the Twilio API working)

So, when they initially sign up, could I just use md5 on their mobile number and perhaps append their user ID to it to make it a bit more secure and then decrypt it when the number has to be sent? I'm not really sure what would be best.

No, MD5 does not encrypt anything, it is a hashing function. You wouldn't be able to get back their mobile number from md5('thenumber').

The simplest start would be using AES_ENCRYPT() to store the number and get it back via AES_DECRYPT(). You've already been pointed to the MySQL manual topic about this and more encryption functions.

This still has the problem the mobile number once gets transferred to you unencrypted before it is encyrypted by storing it via AES_ENCRYPT. This might be acceptable as it only happens once and only again, if the user changes to a new mobile number. Also knowing a mobile number doesn't pose the risc an SMS is received by someone else knowing that number, it is received by the mobile having the corresponding sim module.

Bye, Olaf.

Thanks, Olaf. The whole point is, if someone happens to get into the database they could get everyone's mobile number. I would rather have it that if they get into the database, the mobile numbers are all encrypted and thus the numbers are useless to the hacker, but I am still able to let the user change them if required and receive an sms with a code when resetting their password or registering. I thought appending the MD5 with something so that it isn't as easy to crack as simply using MD5 alone.

Any other suggestions or comments before I close this question off?

MD5 is not applicable. How would you get back the mobile number? There is no DeMD5 function.
You have been pointed to the right functions to use, please read back..

And please make yourself known to the difference about encryption and hashing. For example see here: http://stackoverflow.com/questions/4948322/fundamental-difference-between-hashing-and-encryption-algorithms

Bye, Olaf.

Sorry, you are right, Olaf. So, the simplest (but maybe not best) way would be to use the MySQL built in AES_ENCRYPT and AES_DECRYP.

AES is a very good algorithm, that's not the problem. The problem with any encryption is, that it can be decrypted. Well, that's of course a wanted feature, you want the mobile number back clear text to send it to the Twilio API.

The problem with any encryption, no matter how bad or good algorithm available is, you have to store a key or certifcate or anyting alike for decryption. That obviously should not be stored in the users record, it could be part of the code itself, but then you're only protected against someone pulling out data. The problematic part is the key storage. For a first implementation you might hardcode it into your php code,  though that makes it a secret only safe as far as your sources are safe. You obviously don't store a key inside the MySQL database, that's like putting the key under a door mat.

The problem is, you can't ask a user for a key, your php code should be able to get at the key to decrypt the mobile number, so it has to be stored where php can get at and once your site is hacked via ftp access for example, this is a leaked secret and can obviously decrypt any secured data.

Bye, Olaf.

Okay, last question Olaf. Not sure if you can tell me this or not, but for your clients' websites, do you leave their mobile numbers plain text or do you do something else with them? I am just trying to find out if it is really necessary or not and if many websites do it or not. I am trying to learn how to program php using "industry standards" if I can put it like that.

Of course you store such data encrypted. I don't maintain, administer or work on any websites having more personal data than an email address. But talking industry standards you also talk about legal regulations and, that depends on your country. See https://en.wikipedia.org/wiki/Federal_Information_Processing_Standards

Typically if this is of a concern for a company, it's they telling what to comply with, unless you're the consultant. But then there are specialists for that, data protection officers or commisioners.

Bye, Olaf.