mysql email stored and used for recovery

I want to make a table. My users give me their email address, this is then stored in the database. Should they ever require their login details to be resent to them, they can enter this email address, it will be checked in the system to see if it exists, if it does, an email will be sent to them with their login details.

What database structure is best for this?

What is the best way to store the emails that is not visible to everyone who hacks into the database? bearing in mind I will only check another value against the database and never require the actual email address stored as output?

Answers


If you just need to check if the e-mail is in the system, you should probably store a hash of some sort (MD5 or SHA, both of which MySQL supports natively) instead of the actual e-mail address. A hash is a one-way encryption, which means you can't retrieve the e-mail address from the converted hash text, but you can recreate the hash from the e-mail address.

There's a good discussion of how to store SHA1 strings in MySQL here: Storing SHA1 hash values in MySQL

If your table looks something like this:

userid: INT
emailhash: CHAR(40)

You could check if the e-mail address exists with a query like this:

SELECT userid FROM tablename WHERE SHA1([e-mail address from user]) = emailhash;

If it returns a result, then you know the userid that matches the e-mail address and you can send an e-mail to the e-mail address you collected from the user. (Remember, you won't be able to retrieve the e-mail address from your database.)

There are more notes on MySQL's encryption algorithms here: http://dev.mysql.com/doc/refman/5.5/en/encryption-functions.html


Also, you should not resend their password, as you should never have it in clear text anyway.

Either send a new password, (and leave the old one functioning until the new is used, to prevent somebody from changing anothers password) or send a link with a hash to reset the password.


What database structure is best for this?

Use a table where email is a varchar. You should use utf8 so that people can use funky characters in their email and it is handled correctly.

What is the best way to store the emails that is not visible to everyone who hacks into the database?

You can hash the values using md5 and a 'salt' - a salt is just some extra stuff (in this case a string) to add a little more randomness to the results. This way if someone knows the hash and an original email address, they can't simply figure out the hashing function you used.

Then, if you want to see if an email address already exists - take the one provided, add the salt, do the hash, check if that value is in the db.

If you do this, you know how long the hash is so you can make it a char with the correct length. This will make indexes/queries faster.

That said, there is little reason to do this for email addresses. Passwords should always be stored hashed. An email address is a public thing though it it's not "bad" if someone else knows it. It is "bad" if someone knows a person's password.

A bit more info though, you should do as much as possible to prevent people from hacking your database. If they can hack your database, they may also be able to hack your code. If they do that, they can see your hash function and salt.


Need Your Help

Efficiently building a large (200 MM line) dataframe

r

I am attempting to build a large (~200 MM line) dataframe in R. Each entry in the dataframe will consist of approximately 10 digits (e.g. 1234.12345). The code is designed to walk through a list,

Full calendar fit to container and hide scroll

javascript jquery fullcalendar

I cannot figure out how to scale fullcalendar to fit to it's parent container. I want to display week view on a single page for users without need to scroll (So they quickly review items for a week...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.