login is case sensitive using PGSQL and SQLite-CodePudding

So, I am using Laravel 9x with Jetstream and Inertia/Vue

I am noticing using PGSQL and SQLite the login for email is case sensitive. What is the solution to fix this? I know I am not posting code because it doesnt seem necessary but if there is something you want to see let me know.

I have also heard this does NOT happen with MySQL but I have not tested it yet.

CodePudding user response：

I can't speak to Jetstream specifically, but our app uses FormRequest classes for the login, password reset, etc. forms.

In each FormRequest, we use Laravel's prepareForValidation() method to massage the incoming data.

protected function prepareForValidation($attributes) {
    $attributes['email'] = strtolower(trim($attributes['email']));

    return $attributes;
}

Additionally, in the User model, we have an Eloquent mutator on the email property to ensure updates to this field are normalized:

public function setEmailAttribute($value) {
    $this->attributes['email'] =  trim(strtolower($value));
}

(You could also handle this with an Eloquent observer on the saving event.)

After changing your app to do this, you'd likely want to run a one-time update to your database to lowercase existing email values in the users table. Forgetting to do this would make it impossible for users with uppercase characters to log in.

CodePudding user response：

You can make Postgres case-insensitive by creating a case-insensitive index - more specifically by making an index using a lower transform to normalize the casing in the index, but not in the column itself.

For example, given a users table with an email column, you typically create an index using something like this:

create unique index users_email_idx on users (email);

Instead, you can create the index on lower(email):

create unique index users_email_idx on users (lower(email));

This allows the values written to the column to retain their original casing (ie [email protected] is written to the column), while letting the index contain values that are normalized to lowercase ( ie '[email protected]').

There are two things to note:

First, this enforces uniqueness in a case-insensitive way, so the records [email protected] and [email protected] cannot coexist in the database.

Secondly, in order to use the index while querying, you need to adjust your query slightly to match the index:

select * from users where lower(email) = '[email protected]'; -- uses index
select * from users where email = '[email protected]' -- does not use index

The second query is problematic; not only will it miss records where the case doesn't match, it will miss the index entirely and perform a sequential scan on your database, which can often be very slow and expensive.