Hashing algorithms like MD5 are good answers to what you're
looking for, but first, I'd recommend SHA1. MD5 is known to
have some weaknesses, and SHA1 produces a longer, more
secure 160-bit string (called a message digest). MySQL
versions 4.0.2 and up have SHA1 built-in.
Whether you use MD5 or SHA1, be careful! What you're doing
is simply transforming some plaintext string into a
fixed-length set of bits. That transformation process is
constant, though, so if you simply rely on this:
mysql> SELECT SHA1('[EMAIL PROTECTED]');
+------------------------------------------+
| SHA1('[EMAIL PROTECTED]') |
+------------------------------------------+
| 6d01e80554a8a6c560bfb6a47aede430b98189fe |
+------------------------------------------+
I might see that authentication string and think to myself,
"Self, doesn't that look like a SHA1 digest?" If I run my
email address through the SHA1 algorithm, I'll get the same
result:
$ perl -MDigest::SHA1=sha1_hex -e "print sha1_hex('[EMAIL PROTECTED]')"
6d01e80554a8a6c560bfb6a47aede430b98189fe
Since the resulting strings match, I know that all you're
doing is printing an MD5 digest of the email address. I can
then use that knowledge to generate a digest of your email
address:
$ perl -MDigest::SHA1=sha1_hex -e "print sha1_hex('[EMAIL PROTECTED]')"
885e8118a0de793e9158b9bc31ac9db33fc6308d
and use that to subscribe you to alt.hot.nasty.yeti or
whathaveyou.
There are several solutions: the easiest is simply prefixing
a string (called a salt) to the email address:
mysql> select SHA1(CONCAT('SEEKRIT', '[EMAIL PROTECTED]'));
+------------------------------------------+
| SHA1(CONCAT('SEEKRIT', '[EMAIL PROTECTED]')) |
+------------------------------------------+
| 1444458670f2bc972e8147c6e33764e27e69b315 |
+------------------------------------------+
So without knowing that you prefix email addresses with that
string, I can't create the same digest. Of course, if I get
you drunk and you confess the secret string, then all bets
are off. The advantage here is that you don't need to store
anything: as long as you have the email address and the
secret string, you can recreate the digest. So, in this
scenario:
1) The user requests a subscription change.
2) A digest is generated as above and mailed to user.
3) The user clicks on the link.
4) The user submits her email address and the mailed digest,
confirming the subscription change.
5) A digest is generated from the submitted email address
and compared against the digest submitted.
6) If they match, the subscription change is made.
The weakness here is that the digest is /always/ valid; that
is, the user can use that same digest over and over again.
Thus, if a blackhat intercepts this mail or stumbles over it
after the fact, he or she has carte blanche to make
subscription changes for that person for all eternity.
A slightly more complicated but somewhat safer method is to
generate a one-time digest using RAND() or similar, plus the
email. This gives you a new digest every time you request
one:
mysql> select SHA1(CONCAT(RAND(), '[EMAIL PROTECTED]'));
+------------------------------------------+
| SHA1(CONCAT(RAND(), '[EMAIL PROTECTED]')) |
+------------------------------------------+
| 8b79c52a35a613fd6a014a66c608c9d98c95372e |
+------------------------------------------+
mysql> select SHA1(CONCAT(RAND(), '[EMAIL PROTECTED]'));
+------------------------------------------+
| SHA1(CONCAT(RAND(), '[EMAIL PROTECTED]')) |
+------------------------------------------+
| eff49d94ccc1f3524e828cb91195767628c7bc43 |
+------------------------------------------+
mysql> select SHA1(CONCAT(RAND(), '[EMAIL PROTECTED]'));
+------------------------------------------+
| SHA1(CONCAT(RAND(), '[EMAIL PROTECTED]')) |
+------------------------------------------+
| 0a56be414d9be2f4676fff90836c72e695f1a013 |
+------------------------------------------+
so you /can't/ recreate the digest. It'd work like this:
1) The user requests a subscription change.
2) A random digest is generated and stored along with the
user's email address in a "requests pending" table.
3) The digest is mailed to user.
4) The user clicks on the link.
5) The user submits her email address and the mailed digest,
confirming the subscription change.
6) The email address and digest from the link is checked
against the the "requests pending" table.
7) If found, the digest is deleted from "requests pending".
8) The subscription change is made.
If a blackhat intercepts this email, they can only make one
change. If a blackhat finds the email after the real user
has made the change, the blackhat can't do anything, because
the digest is no longer in the table. Plus, you can add a
timestamp to the "requests pending" table and delete digests
older than one day, further limiting treachery.
Most importantly, you should know that no hashing algorithm
is perfect, RAND() isn't entirely random, and security is
Not Easy. Here's some reading material:
http://www.linktionary.com/h/hash_function.html
http://en.wikipedia.org/wiki/SHA-1
http://aspnet.4guysfromrolla.com/articles/112002-1.aspx
____________________________________________________________
Eamon Daly
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
