Email case sensitivity

tomkarlo edited this page Apr 23, 2011 · 10 revisions

In versions of Clearance prior to 10.4, when a user would sign up, their email was stored in the exact case they entered.

In Clearance 10.4 and higher, their email is stored in lower case no matter what case they enter.

For MySQL databases, this change does not really matter, because SELECTs on email were case-insensitive anyway. So when a User signs in, even if they type a different case than was actually stored, MySQL will still find their email.

For Postgres databases, the SELECTs were case-sensitive. This was a problem in a few cases. The one we saw most frequently was:

  • The app is running on Postgres on Heroku.
  • The user signs in on their iPhone, automatically uppercasing the first letter of their email address.
  • They are told they can’t sign in.

We wanted to avoid this poor user experience in all databases so we’ve forced all emails to be downcased when they are created and queried during sign in.

Recommendation: run a migration

If you have an app with an existing users table and email column, we recommend that you run a migration that downcases the value of the email column for all the records in the users table.

The RFC 5321 spec

According to the RFC 5321 specification, it is technically possible to have multiple email addresses in a mail server whose local-parts are the same characters but of different case. In practice, that is rare.

Database index

For a few early versions of Clearance, we used the LOWER() SQL function on email for the authenticate method. That was to handle old records that may have uppercase characters. However, this caused the database not to use the database index. In an app with even just a few thousand users, that can make the authenticate method noticeably slower.