Email address authentication (points and capitalization)

Question

As you know, there are many characters that can be used in an email address. People are currently changing some keywords on their email address to improve readability, for example:

[e-mail] [e-mail] 

However, a dot symbol . when using Gmail (as well as capitalization) only improves readability, it is not taken into account as part of the email address. Thus, we can say that:

[e-mail] same as [e-mail]

[e-mail] same as [e-mail]

---

Problem

Most websites use database-based authentication. Suppose I registered an email (without dots) [e-mail] , then such a MySQL query will not work, because the lines are different:

SELECT * FROM users WHERE email = " [e-mail] " ;

---

Question

Is there any solution to solve this problem? In fact, different mail servers use different rules. That is, users should always remember on which website they used dots or capital letters, even this email address is the same?

---

UPDATE

Imagine some service where you can create tons of accounts using the same email. Original email [e-mail] , aliases:

 [e-mail] [e-mail] [e-mail] 

And yet you will receive confirmation emails at the same address. Sounds weird, huh?

Answer 1

If you really want to solve the problem, as you suggest, you will need to save your email address as a kind of canonical form. Say all lowercase letters and delete the “dots” or any other special character that you want to ignore.

You can "automate" a thing 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 (" [e-mail] "), (" [e-mail] "), (" [e-mail] "); 

This will only result in inserting one row into the table based on the canonicalized 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 undesirable. It just indicates that proper canonicalization may be a little more complicated than REPLACE(LOWER( ... Also, you probably need to create a duplicate ON UPDATE this trigger. But ...

---

... I will not move further in this direction:

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

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

Most MTAs are configured to ignore case sensitivity of the "local part". But this is absolutely not required. In fact, RFC5321, section 2.2 clearly states that "The local part of the mailbox MUST be considered case sensitive."