Auth: case insensitive username and email address

[kvchitrapu]

Username and email address are generally case insensitive. Today, Ambuda auth allows proofer and prooFer as valid users.

Making usernames and email addresses in register and login pages case insensitive requires backward compatibility. On login, if we convert all usernames to lowercase, existing users with uppercase usernames will fail. For a smooth rollout of this feature, existing usernames in the db have to be first converted to lowercase. Then, register and login can apply lowercase conversion on these fields.

[shreevatsa]

I think it may be possible to query the db case-insensitively, without changing any existing usernames. (I'm assuming that we currently don't have two usernames that are identical if compared case-insensitively.)

[kvchitrapu]

Good idea. Something like this should work:

diff --git a/ambuda/queries.py b/ambuda/queries.py
index 1ca0f45..116f9f5 100644
--- a/ambuda/queries.py
+++ b/ambuda/queries.py
@@ -8,7 +8,7 @@ import functools
 from typing import Optional
 
 from flask import current_app
-from sqlalchemy import create_engine
+from sqlalchemy import create_engine, func
 from sqlalchemy.orm import load_only, scoped_session, selectinload, sessionmaker
 
 import ambuda.database as db
@@ -214,6 +214,13 @@ def user(username: str) -> Optional[db.User]:
         .first()
     )
 
+def case_insensitive_user(username: str) -> Optional[db.User]:
+    session = get_session()
+    return (
+        session.query(db.User)
+        .filter(func.lower(username)==username.lower(), is_deleted=False, is_banned=False)
+        .first()
+    )
 
 def create_user(*, username: str, email: str, raw_password: str) -> db.User:
     session = get_session()
diff --git a/ambuda/views/auth.py b/ambuda/views/auth.py
index 7de9a36..c1f7606 100644
--- a/ambuda/views/auth.py
+++ b/ambuda/views/auth.py
@@ -23,6 +23,7 @@ from flask import Blueprint, flash, redirect, render_template, url_for
 from flask_babel import lazy_gettext as _l
 from flask_login import current_user, login_required, login_user, logout_user
 from flask_wtf import FlaskForm, RecaptchaField
+from sqlalchemy import func
 from wtforms import EmailField, PasswordField, StringField
 from wtforms import validators as val
 
@@ -159,15 +160,15 @@ class SignupForm(FlaskForm):
 
     def validate_username(self, username):
         # TODO: make username case insensitive
-        user = q.user(username.data)
+        user = q.case_insensitive_user(username.data)
         if user:
             raise val.ValidationError("Please use a different username.")
 
     def validate_email(self, email):
         session = q.get_session()
         # TODO: make email case insensitive
-        user = session.query(db.User).filter_by(email=email.data).first()
-        if user:
+        user_email = session.query(db.User).filter(func.lower(db.User.email) == email.data.lower()).first()
+        if user_email:
             raise val.ValidationError("Please use a different email address.")