Making Email IDs Case Insensitive

Making Email IDs Case Insensitive

In this Blog-Post, I will show how I made email-id case insensitive so that the open-event-server won’t allow multiple accounts with a single handle.

Previously, email ID was stored in the open-event-server database as raw string, i.e., case sensitive, so if a user entered the email with upper case alphabets, he/she needs to enter the same way during Auth, which could have lead to creation of multiple accounts with one email id and so is a major bug.  

Migration updation:

First, we need to handle the cases on the production database where people have made multiple accounts with a single handle because of this bug. For this, we need to write a complex SQL query, so let’s just make a new migration instead of updating the models.

def upgrade():  
      op.execute("UPDATE users SET deleted_at = current_timestamp, _email =      
   concat(_email, '_') where _email not in (SELECT DISTINCT ON (upper(_email)) _email 
   FROM users);", execution_options=None)

The nested query will return the list of all Emails which are not distinct on upper(_email). In simple words, the following query says: “Get the _email from users table where upper(_email) is distinct”. We want to delete the emails which are not returned in this query. For that, we set “deleted_at = current_timestamp” for those row. 

Make ‘_email’ column case insensitive:

We need to change the column data type to something that treats email as case-insensitive. That’s where “citext” comes into play. But we will still get an error on applying citext to “_email” column.

Any guesses why?

Remember we just updated “deleted_at” column of duplicate _email rows. So there still exists duplicate entries of emails in case we treat them case-insensitive, which defies the “_email” column rule to have unique entries defined in “user” Model. As it’s never a good idea to delete entries on the production database, we add a “_” to distinguish those email ids. 

Now the following lines will change the column type of “_email” to citext.

op.execute("create extension citext;", execution_options=None)

Now, we need  to write the opposite logic in case someone downgrades from the migration.

def downgrade():
    op.execute("alter table users alter column _email type     text;",execution_options=None)
    op.execute("UPDATE users SET deleted_at = null, _email = left(_email, length(_email)-1)        
    where right(_email, 1) = '_';",execution_options=None)
    op.execute("drop extension citext;",execution_options=None)

This will solve our bug, and won’t allow multiple accounts from the same handle.

Resources:

Issue: fossasia/open-event-server#5643
Pull Request : fossasia/open-event-server#5728
Documentation | Postgresql: https://www.postgresql.org/docs/current/sql-select.html#SQL-DISTINCT
Documentation | Citext: https://www.postgresql.org/docs/9.1/citext.html 


Close Menu