5

I'm coding a web app using Laravel 4.1 and Postgresql as database. The db is case sensitive, but i'd like to make it case insensitive because, i.e., when a user is logging he should be able to access using upper case or lower case email address (like in every other website). However the column for the hash of the password must be case sensitive because the encryption method i use generates case sensitive strings.

I'm using Eloquen ORM of Laravel so i don't write queries directly.

How can i solve this problem?

Thanks in advance!

Davide Mercurio
  • 51
  • 1
  • 3
  • Leave it case-sensitive but create an index on `lower(email_address)`? Or normalise your email addresses on input? – Rup Jan 24 '14 at 10:47

2 Answers2

16

A bit late, but this is pretty simple. Just use the "ILIKE" operator, e.g.

User::where("email", "ILIKE", $email)->get();
joelennon
  • 189
  • 1
  • 5
  • 1
    However, ILIKE does not use indexes! For that, indexing on the lower value is the way to go in pgsql. – foaly Aug 11 '15 at 08:35
  • 1
    this is DANGEROUS!!! Consider having someone put % at the end of the email to select a random-ish user like instead of writing "john.smith@gmail.com" i write "joh%" and pick your database for emails? – Hop hop Feb 04 '20 at 06:20
0

I had this exact problem- my solution was to pretend to be case-insensitive:

1) add one line in the relevant methods to make the entered email value lowercase

2) did a replacement in the database so that emails were lowercase

3) made sure that new emails come in as lowercase

Details:

1) The relevant methods* are core laravel code, so you override them with a new version that replaces the email value after validating the request: $request['email'] = Str::lower($request['email']);

  • log in flow: postLogin function from AuthenticatesUsers trail, I added to AuthController.php postLogin
  • password reset flow: postEmail and postReset functions from ResetsPassword trail, I added them in PasswordController.php

2) update users set email = lower(email);

3) For me this is easy because I create all users myself (my site is just for family)- but you'd do something similar in the auth flow

Hope this helps!

Diane Kaplan
  • 1,626
  • 3
  • 24
  • 34