1

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?

David19801
  • 11,214
  • 25
  • 84
  • 127

3 Answers3

1

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

Community
  • 1
  • 1
thehiatus
  • 1,575
  • 13
  • 23
  • Thanks, this is exactly what I needed. I was just thinking, is it usually ok to store text email addresses in a database? I mean is it secure enough usually? – David19801 Dec 28 '10 at 21:27
  • 1
    Yes - see my answer. Emails are public things. People knowing them isn't bad - although they can do semi-bad things with them. Passwords are private things. People knowing them is bad. – evan Dec 28 '10 at 21:29
  • Weird. My name is Evan too and I was pretty confused there for a sec. Anyway, I agree with my namesake that it's generally not a bad thing to store unencrypted e-mails in your database unless you're working in healthcare or some other sensitive field. – thehiatus Dec 28 '10 at 22:58
1

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.

Leif Neland
  • 1,416
  • 1
  • 17
  • 40
0

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.

evan
  • 12,307
  • 7
  • 37
  • 51