Force email addresses to be lower case

[carlobeltrame]

This issue needs to be re-specified, it's not ready for implementation right now.

We currently allow to store email addresses containing capital letters. This leads to some minor bugs, e.g. it is possible to invite the same email address multiple times with different capitalization: castor@example.com is treated as different from Castor@example.com. This circumvents our unique index on the inviteEmail column of CampCollaboration.

For modifying the input which users submit in various input fields, we have input filters. One example is InputFilter\Trim, which is used to trim whitespace from the start and end of input fields.

To fix the mixed-case email addresses, we should:

• Create a new input filter InputFilter\Lowercase, along with the InputFilter\LowercaseFilter class which actually performs the modification
• Add the #[InputFilter\Lowercase] annotation to Profile#email and Profile#newEmail
• Add the #[InputFilter\Lowercase] annotation to CampCollaboration#inviteEmail
• Add the #[InputFilter\Lowercase] annotation to ResetPassword#email
• Add unit tests for the InputFilter\LowercaseFilter, similar to the tests for the trim filter
• Add API tests for all API operations which use the modified fields (create and update on these three entities), similar to the already existing trim tests
• After the oauth flow we need to lowercase the email too, + search for the existing email with the lowercase email from the oauth flow
• Optional: Write a data migration to convert all existing data in the database to lower case. ⚠️ If there are already duplicate emails, we need a conflict resolution.

Maybe possible alternative:

• Use a collation for this field which ignores the case.

[usu]

I believe email addresses are actually case sensitive. Even though no large email provider will treat emails as case sensitive, we could not entirely rule out, someone would use an own server which treats addresses case sensitive, right?

https://stackoverflow.com/a/1503679
(the answers here are quite old, but generally I believe still valid; unless the standard has changed in the meantime)

[manuelmeister]

Core Meeting Decision

We want to keep case sensitive email addresses for sending email.
However we will disallow creating accounts with same email with different cases.
We will store the email address case sensitive and add a lowercase index with a unique constraint (for inviteEmail & email,…?). Besides that we create a data migration for the existing 26 user accounts.
When matching an invite email with an existing user account we will need to do this in a case insensitive way.