For security reasons we want to encrypt all email addresses. So in case of a MySQL breach, they get nothing but gyberish text.
But we need to retrieve the email addresses of members to inform them that X has posted a message they want to be informed of, Y wants to buy what they are selling, etc.
So we cannnot use the MySQL built in one way encryption of PASSWORD, since again we need to retrieve the email of members send them notices as outlined above.
So my question is then what is the best way to encrypt a text field in MySQL, such as email addresses, and be able to readily de-crypt it when we need to.
Sorry, but I am not following this code you referenced.
So I am looking for a Php function, lets call it encrypt($value) which you send it the value and it will encrypt it and then a function called decrypt($value), which will decrypt the encrypted data.
What is $iv ?
And how long should the $secret_key be?
And where is the exact code for these mcrypt_encrypt & mcrypt_decrypt?
FYI, the 2nd page you have referred to which is supposed to have the code leads to page not found!
So have you used this encrypt and decrypt code yourself?
Or is this a widely used and 100% proven to be accurate code?
Because once we decrypt all the emails via this encrypt code, if the decrypt does not work 100% then we will be doing Big damage to our members & network.
Also does this encrypt code result in an encrypted code that is same length (in Chars) as the original text or something longer? And if longer, what should we set the VARCHAR length to be for it?
That is not the issue.
The point is how could we know that they decrypt was good or not, since we are encrypting Millions of email addresses via this. So only practical answer, is that this encrypt decrypt has shown itself in real life to be 100% accurate.
Dorm, Ok that is good to hear.
But just to be sure: have you or any one else have used this encrypt/decrypt in real world and have found it to be 100% accurate? Based on Millions of Texts (i.e. emails) encrypt/decrypted?
Since it is built into PHP if it wasn’t 100% accurate over the millions of trillions of encryptions that have been done with it then it would have been fixed long ago to ensure that it was 100% accurate.
A) @felgall is right, since it’s a built-in functionality it would’ve been fixed long ago if inaccurate
B) If you aren’t going to do backups and your do own testing (if you have millions of emails to encrypt) and are going to base your decisions solely on what members of this forum say, you’re an interesting developer.
I recommend that if you’re encrypting emails that you consider whether you can allow duplicates and whether it’s important to be able to identify them. Typically it’s a bad thing to allow duplicates (for example if the email address is a user’s unique identifier) but if you need to ensure that you’re not allowing duplicates then you must always enforce the same initialisation vector, encryption key etc, which defeats the purpose of encrypting, as it becomes much easier to decrypt. Especially knowing that there’s an @ symbol in there. The more records encrypted with the same IV and key, the easier it is to decrypt once you have a handful of records. And by your own admission, you’re talking about millions of records. This would be quite easy to crack by a good hacker.
TBH, I think that encrypting email addresses is a bad idea. I’ve done a lot of work in PCI compliant environments, as well as developing for US financial institutions, and it’s never been required that we encrypt email addresses. Some have asked, but that was mostly through fear, rather than actual necessity. Although they are sort-of personally identifiable information, they’re not actually sensitive. It’s like saying that my house number is sensitive data; it’s not - you can walk up my street and read it straight off of my front door. They’re not like credit card numbers. Spend your time and effort preventing anyone getting in to your DB in the first place. Restrict IP address access to the DB and anything that can connect. Use SSL between the webservers and the DB server. Connect over an SSH tunnel, and use a key to connect, not just a username and password. Ensure that the DB passwords are long and complicated, and don’t use the root account. Set up a simple user with minimal permissions that can access from the web server and only allow it access to the tables that it actually needs. If a hacker can still get into your DB then they’ll probably have all of your source code as well at this point and your encryption keys have been given up, so all the encryption is for nought.
