personal information protection in mysql database

I understand that it is necessary to encrypt users passwords but what about their other details, specifically their mobile number? Is it possible/worth encrypting their mobile number or any other personal info?

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.

Important thing to understand before choosing the "answer" to this question. Information Technology Security is a full-time four year college major at the University of Maryland. In other words, you're not going to get a good answer here because it would take us four years to teach you what you need to know.

So here's the short answer: Encrypt anything that you would not want to have exposed. If you would not want your email address or phone number getting exposed to the world, encrypt it.

Here's the better answer, short of four years in college: Join OWASP and become active in their discussions. It will be very eye-opening, I promise!

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.

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.

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.

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.

Just a note: I often suffer from the same bad habit of deep diving and getting stuck at something. Let's look back a bit. Remember Ray said in another thread the kind of security level you need depends much on what you need to protect, you don't need to apply FIPS or other regulations for everything.

There are also other ways of easy second factor verifications. One very obvious: Let a user register a secondary mail address and also send a code there, too. Another possible second facotr of authentication/verification is using an Athenticator app. You don't need to provide one, there are apps from Google and Microsoft called Authenticator, Apple also provides Google Authenticator as iPhone app. Someone having that installed can add a secret you share with them via displaying some code or qr barcode and after that this app creates a one time code valid for 30 seconds. Within that timeframe the user can copy/type the code to your website and it can veryfy this is what you'd also create using the same algorithm for the OTP. For reference: https://en.wikipedia.org/wiki/Google_Authenticator and https://github.com/chregu/GoogleAuthenticator.php

There are very many ways to do two factor authentication and these two don't necessarily need sensitive personal data you would only store encrypted.