Email Address Authentication (dots and capitalization)

Question

As you can know there are a lot of characters that can be used in a e-mail address. People nowadays split some keywords in their e-mail address to improve readability, eg:

[email protected]
[email protected]

However, the dot character . when using Gmail (as well as capitalization) only improves readability, it is not taken in account as a part of e-mail address. Thus, we can say that:

[email protected] is the same as [email protected]

[email protected] is the same as [email protected]

---

The problem

Most of websites use a database-driven authentication. Assume that I registered a email (without dots) [email protected] then, such a MySQL query will not work as the strings differ:

SELECT * FROM users WHERE email = "[email protected]" ;

---

The question

Is there any workaround to solve this kind of problem? In fact different e-mail servers use different rules. So do users have to always remember, on which website they have used dots or capital letters, even that e-mail address is still the same?

---

UPDATE

Just imagine some service, where you could make tonns of accounts using the same e-mail. Original email is [email protected], aliases:

[email protected]
[email protected]
[email protected]

And still you will be getting e-mail confirmations on the same address. Sounds strange, huh?

Answer 1

If you really really want to solve you issue the way you propose, you will have to store your email address as some kind of canonical form. Say all lower-cases and by removing "dots" or any other special character you want to ignore.

You could "automatize" the thing by using a trigger (http://sqlfiddle.com/#!2/81689/1):

create table email(addr char(80), canon char(80) UNIQUE);
CREATE TRIGGER ins_email BEFORE INSERT ON email
FOR EACH ROW
    SET NEW.canon = REPLACE(LOWER(NEW.addr), ".","");

INSERT IGNORE INTO email(addr) VALUES ("[email protected]"),
   ("[email protected]"),
   ("[email protected]");

This will only insert one row in the table based on the canonized form. Please look carefully at the last example. The domain is "ex.ample.com" which is canonized by my simple trigger as "examplecom". Which is probably not desirable. This is just to pinpoint that correct canonization might probably be a little bit more complex than REPLACE(LOWER( ... . In addition you will probably need to create a duplicate ON UPDATE of this trigger. But...

---

... I won't go further in that direction as:

"capitalization [..] is not taken in account"

This is a common misconception: domain names (at right of @) are not case sensitive. But local parts (at left of @) are case sensitive. Except for the special case postmaster.

Most MTA are configured to ignore case sensitivity of the "local part". But this is absolutely not required. In fact RFC5321 section 2.2 clearly state that "The local-part of a mailbox MUST BE treated as case sensitive."