User email should not be case sensitive

We should check if the problem can be reproduced in Drupal 8.

Unable to reproduce in Drupal 8. (Password reset worked as expected when differing case between registered email address and the email address used in the password reset form.)

Yes this is still an issue in 8.x
I tried ALEX@alexburrows.net and the account was created correctly, but upon doing a forgot password with lowercase I got the attached screenshot.
So it sounds like we need to modify when a user is created functionality and any lookups needed to determine the correct data.

I tried to reproduce this on 8.x HEAD with MySQL and couldn't, @aburrows what are the exact steps to reproduce this?

I tried the steps as mentioned in this issue's description, but not able to reproduce the problem.

This has been tested on 8.0.x branch, and it works well.
Closing the issue.

Hi tvn,

I am not able to reproduce this issue on Drupal 7 too. I had followed the following steps:

1. At '/user/register' registered a new user with mail id as 'CAPITAL@mail.com'.
2. Logged in to the website using administrator user and updated the account for 'CAPITAL@mail.com' from BLOCKED to ACTIVE.
3. Logged out from administrator user.
4. Under request password tab 'user/password'. I have tried with different combination of cases(Capital and non capital alphabets at 'CAPITAL@mail.com').

This worked fine for me. Please guide me if I missed any steps in between....

Regards,
Neeraj Singh

I cannot reproduce this on 7.x.

Given the nature of the issue I assume this could be a problem that only shows up on PostgreSQL databases - @aburrows, can you confirm that's what you were using?

In fact this might be a duplicate of #2324701: User login name is case sensitive when using postgreSQL, and if so that issue may need to be moved to Drupal 8.

I was using MySQL on a vagrant setup but that was a while back, i'll have to check the latest 8.x HEAD to see if it works

This is for PostgreSQL and believe that the issue defined in 2324701 is caused by the same thing. The latest patch there doesn't fix this for the password reset form being case sensitive. Would it would make sense to have that merged into one?

I can confirm that this is indeed an issue in Drupal 8.0.1 with PostgreSQL.

The attached patch set the 'case_sensitive' attribute with false for the email field item. It include a test for "reset the password by email" with case sensitive mismatch.

If this case_sensitive attribute, new since D8, is database backend agnostic then the test-only patch should fail (for mysql and postgresql).

Thus this issue isn't related to #2324701: User login name is case sensitive when using postgreSQL. I mean fix isn't related.

This fix pgsql translateCondition() (which is triggered now since the email field item now use case_sensitive = FALSE).

Small change over #14: fix translateCondition() completely.

Are you sure this is moving in the right direction since as of RFC 5321 section 2.3.11. https://tools.ietf.org/html/rfc5321#section-2.3.11 the local-part of an email address can be case sensitve and it is up to the Domain to decide on this. So capital@domain.com could be a different user than CAPITAL@domain.com. (not very common but possible)

This Issue is a Trade of between minimizing support Issues and Security. To be More Secure Drupal should always treat the localpart case sensitive since there is no means of knowing how the user Domains handles that.

So If going the route of assuming that most of the domains handle their local-part case insensitive which should be fine for most Sites, there should be a config to switch this to the more secure side. And handle the local-part case-sensitve so sites with critical data have a means of changeing that.

I would say that enabling mail's local-part with case sensitive could be quiet cumbersome; users would probably want to reserve each possible combination of capitalisation for the local-part. That's probably the reason why case sensitive isn't much enabled in practice...

That said, having this local-part case sensitive as an option is probably the best solution but I would say this would need to go in a separate issue. Then, this issue is to homogenize the behavior btw database back-ends and I think having case insensitive as a default seems quiet logical...

This patch add case insensitive for SQLite beside PostgreSQL (and MySQL as initially).

This patch works for me with PostgreSQL, tried with Drupal 8.2.x. I haven't tested the SQLite equivalent.

Previously I could log in with 'user@example.com' but not 'USER@example.COM', and after the patch I can use either variation.

Can we get this fixed in Drupal 8.6.x at least?

@mxr576, we need to make sure that this is tested correctly and set to rtbc before we can commit this, and thus fix it in the next drupal release.

I'm pretty sure this will also need a change record.

I found a couple of things that need changing.

1. +++ b/core/lib/Drupal/Core/Entity/Query/Sql/sqlite/Condition.php
   @@ -0,0 +1,36 @@
   +/**
   + *  * Implements entity query conditions for SQLite databases.
   + *   */
   

   The comment looks wrong.

2. +++ b/core/lib/Drupal/Core/Entity/Query/Sql/sqlite/Condition.php
   @@ -0,0 +1,36 @@
   +class Condition extends BaseCondition {
   +  /**
   

   Needs a newline

3. +++ b/core/modules/simpletest/src/TestBase.php
   @@ -1634,4 +1634,24 @@ protected function getConfigSchemaExclusions() {
   +  function strCaseInverse($s) {
   

   I don't think this method should go in this base class. We shouldn't introduce new methods on base classes if they only have one usage, or at least that's how I understand it.

4. +++ b/core/modules/user/src/Tests/UserPasswordResetTest.php
   @@ -179,6 +179,19 @@ function testUserPasswordReset() {
   +  function testUserPasswordResetCaseInsensitive() {
   

   This should probably go in a functional test instead of a simpletest.

   I don't think it's a good way to test this either.

   We can be more explicit in testing this by using an email address that explicitly has the correct casing?

Is this being back-ported to Drupal 7 at all? Never mind, I see the tag. Curious if anyone has attempted it though?

D7 version: #2324701: User login name is case sensitive when using postgreSQL

Thanks!

A quick re-roll. Have moved login tests to functional tests, but didn't fancy doing the same for password reset tests at the moment as they haven't all been ported yet anyway.

Arggh.

Here's a proposed refocusing of this issue:

1. Storage must preserve case
From RFC 2821:

The local-part of a mailbox MUST BE treated as case sensitive. [...] Mailbox domains are not case sensitive. In particular, for some hosts the user "smith" is different from the user "Smith".

Therefore as agreed in #280310: Force {users}.mail to lowercase we MUST NOT force emails to lower case because (albeit in very rare circumstances) mail will not get delivered correctly if the capitalisation is wrong.

2. Assuming that email querying is case insensitive is a security liability
If your email provider is case sensitive and allows two different users to have the addresses smith@example.com and Smith@example.com, then in some circumstances your Drupal account can be compromised by someone deliberately obtaining an address equal to yours but different in capitalisation.

To prevent this:
(a) password reset emails must be sent to the stored email address not the user entered email
(b) email uniqueness validation on the user entity should be case insensitive
(c) external identification integrations like LDAP should take extra steps to verify the exact case matching of a Drupal email and the external email if there is any chance of untrusted case-only-differentiated emails in their system.

3. Making querying by email case sensitive can lead to unexpected behavior
Users frequently enter the case of emails wrongly, and developers frequently forget that users may have miscapitalised their input. e.g. #2876155: Functions to load user by name or email should be case insensitive Therefore Drupal's OOTB UX and DX would be enhanced if email fields were case insensitive by default.

4. In scope here
To do this we need to:
(a) ->setSetting('case_sensitive', FALSE); on EmailItem per#19
(b) test that password reset form sends email to stored email not form-inputted email
(c) test that email uniqueness validation on the user entity does not allow emails that differ only in case
(d) test that using UserStorage::loadByProperties(['mail'=>...) is case insensitive
(e) test that using a user entity query with a mail condition is case insensitive.

5. Out of scope here:
(a) Making login by username case insensitive per #30/31. That should be a seperate issue.
(b) adding Sqllite support for entity query case sensitivity. That should be a seperate issue, if necessary this can be postponed on that.

Re-rolling for my benefit. If it turns out there's any interest in getting this into core then I'd be willing to help, but it doesn't seem to be bothering many people apart from me :)

Absolutely, we use Postgres databases mainly for geo mapping features and case-insensitive emails have been a royal pain for us, though most of our users are on a Drupal 7 site, all our new sites are D8.

I'm happy to keep reviewing if people make patches in the direction of #36.

Ok, thanks folks, you're golden.

1. Agreed.
2. That would be my assumption also, although it might be good to get a few more eyes on to confirm it. Does this only stand true when the email address is used for authentication/users?
4. I'll look to add tests back in.
5. Sounds good also.

Uggh. There's a problem I didn't consider. What do we do about duplicate but (case-differentiated) emails already in the system. It's a BC nightmare. We might have to turn case-insensitivity off by default on existing installs.

Why not make a view that lists duplicates and a status report that flags them as an error state? Let the site admins decide how to handle it with their users however they see fit?

#42 is going in the right direction.

We should check for duplicates using hook_requirements ($phase === 'update') and return a REQUIREMENTS_ERROR if there are duplicates.

Maybe we could just put the duplicates in the status message as there should be so few, it shouldn't be too problematic even on a site with a huge number of users. In fact, if there were more than 100 duplicates, we don't need to list them because that site is probably going to need to develop a custom solution to fix them anyway.

I'm relieved, this solution is easier than I feared it would be.

@kalpaitch patches not working giving error would be suggest how i can check it sharing screenshot ....

Updated to 9.2. Patch updated.

NW for #43

Updated with suggestion from #43 and some ideas from https://www.drupal.org/project/drupal/issues/2464481#comment-14544486

Ooops! Proper patch.

We are not going to use the LOWER operator on PostgreSQL. The decision has been made to use GIST indexes instead. For that is the pg_trgm extension required for Drupal 10. See: https://www.drupal.org/docs/system-requirements/database-server-requirem....
The biggest problem is with performance problems with the path alias queries. The patch from that issue also add the code to create those indexes. See: #2988018-71: [PP-1] Performance issues with path alias generated queries on PostgreSQL . When that has landed, we can use that solution in other places, like with #2490294: User email should not be case sensitive.

@VladimirAus: Thank you for working on case insensitive queries on PostgreSQL. It is also something that I would like to get fixed. It would be great if you could do a review of #2988018-71: [PP-1] Performance issues with path alias generated queries on PostgreSQL .

The patch in #51 breaks a site when using Exists or notExists conditions.

Fix provided.

Actually the patch in #53 breaks other stuff. The latest one is based on @VladimirAus 's two patches.

+    // Load all users on update to check duplcate emails in various cases.
+    $users = \Drupal::entityTypeManager()->getStorage('user')->loadMultiple();

The memory impact of a call like this in a simple hook_requirements() implementation can be huge, even drastic on sites with thousands of users. In addition, loading full user objects are completely unnecessary when only their IDs and email addresses are needed. I'd use plain entity (or even database) queries for this.

Let's do a count query first. If there's more than 100, then don't list them as the site will probably want to figure out a custom solution. If there's less than 100, it will be fine to use loadMultiple() anyway.

Re-rolled #45 for Drupal 9.5.

@andrew.wang the update hook that was available in #54 is missing from your patch in #58, what is the reason behind that?

Let's do a count query first. If there are more than 100, then...

This can be a good approach, but how about instead of that magic number (100) we would depend on the value of Settings::get('entity_update_batch_size')?

how about instead of that magic number (100) we would depend on the value of Settings::get('entity_update_batch_size')?

That seems excessive for a minor developer convenience related to a one time update.

That seems excessive for a minor developer convenience related to a one time update.

Well, my reasoning behind suggesting that was that we cannot know if 100 entities are too much or too less on a given downstream project (hosting platform). Downstream developers should know and already have this setting value customized accordingly.

I believe this should be closed now - the use case of having multiple users with the same email in different capitalization was addressed in https://www.drupal.org/sa-core-2024-004 .

I've just tested the behaviour on the Drupal 10.3.10 and PostgreSQL and it looks perfectly in line with various proposals in this issue:
- account registered for testEmail@test.com
- account registration attempted for testemail@test.com - failed with "Address already taken" message
- password reset for attempt for testemail@test.com sent a password to stored in the system testEmail@test.com

I've clearly mixed some wires here - user mail field is case sensitive.

Sorry for the noise.