[3.0]: Two accounts can register on the same email address on PostgreSQL

[albertlast]

Basic Information

On PostgreSQL two accounts can be registered on what is the same email address, if the capitalisation differs. On MySQL the second registration is refused.

The "is this address already taken" check compares with a plain =, and neither side is normalised. Sources/Actions/Register2.php:536-546:

$request = Db::$db->query(
    'SELECT id_member
    FROM {db_prefix}members
    WHERE email_address = {string:email_address}
        OR email_address = {string:username}
    LIMIT 1',

$reg_options['email'] is taken from the form and only run through filter_var(..., FILTER_VALIDATE_EMAIL) (Sources/Actions/Register2.php:483); it is never case-folded. smf_members.email_address is a plain varchar(255) (Sources/Db/Schema/v3_0/Members.php:144-150) and is stored exactly as typed (Sources/Actions/Register2.php:622).

That makes the check depend entirely on the collation of the column: case-insensitive on MySQL, case-sensitive on PostgreSQL. The database does not catch it either, because idx_email_address is a non-unique index, so uniqueness is enforced only by this query.

The domain part of an address is case-insensitive by definition, and in practice every mail provider treats the local part that way too, so both accounts receive their activation mail at the same mailbox.

Steps to reproduce

1. Install SMF 3.0 on PostgreSQL.
2. Register an account with the email address user@example.com.
3. Register a second account, with a different username, using User@Example.com.

Expected result

The second registration is refused with "That email address is already in use", the same as on MySQL.

Actual result

The second registration is accepted and two accounts now exist on the same address.

Version/Git revision

3.0 Alpha 4 (16af1506e)

Database Engine

PostgreSQL

Database Version

PostgreSQL 17.10

PHP Version

8.4.24

Logs

# PostgreSQL 17.10
smf=> SELECT 'User@Example.com' = 'user@example.com';
 ?column?
----------
 f

# MySQL 8.4.11
mysql> SELECT 'User@Example.com' = 'user@example.com';
+-----------------------------------------+
| 'User@Example.com' = 'user@example.com' |
+-----------------------------------------+
|                                       1 |
+-----------------------------------------+

Additional Information

The username side of registration does not have this problem, because it goes through spoofdetector_name, which is folded in PHP with Utils::casefold() and compared with a plain = (Sources/Unicode/SpoofDetector.php:334-352, Sources/Tasks/UpdateSpoofDetectorNames.php:113-118). That value is identical on both engines regardless of collation. Applying the same treatment to email addresses would settle this one, and would also let idx_email_address become a real unique index.

Related: #3214, on Db::$db->case_sensitive being hardcoded per engine rather than reflecting what the column actually does.

[albertlast]

Filed separately but with the same root cause: #9592 (email bans) and #9593 (searching by user name).

[albertlast]

The code quoted in this report is stale, and the real behaviour is worse than what it describes. I read a checkout that predated 959007f ("[3.0] PostgreSQL didn't always handle bans properly", 28 August) while quoting 16af1506e as the revision, which contains it.

That commit already changed this query. What is actually there:

// Sources/Actions/Register2.php:536-545
'SELECT id_member
FROM {db_prefix}members
WHERE {raw:email_address_field} = {string:email_address}
    OR {raw:email_address_field} = {string:username}
LIMIT 1',
[
    'email_address_field' => Db::$db->case_sensitive ? 'LOWER(email_address)' : 'email_address',
    'email_address' => $reg_options['email'],
    ...

The column is folded on PostgreSQL now. The value is not. So the comparison is LOWER(email_address) = 'User@Example.com', which matches nothing at all:

smf=> SELECT LOWER('User@Example.com') = 'User@Example.com';
 f

On PostgreSQL that means an address containing any uppercase letter is not detected as taken even against itself. Registering User@Example.com twice now succeeds, where before the change only the case variants got through. The reproduction is simpler than the one in the report: register the same address twice, spelled identically, with one capital letter in it.

Sources/Profile.php:1652 has the same shape, from the same commit, for changing an address on an existing account:

AND {raw:email_address_field} = {string:email_address}

so email_taken can be missed there too.

Folding the value as well as the column would fix both, and is what {ci_string:} in #9596 is for. I have not done it there, because that pull request is deliberately behaviour-preserving; it converts these two to {ci:email_address}, which expands to exactly what the ternary above produces. The fix belongs on this issue.

Worth noting separately: idx_email_address is a non-unique index, so nothing at the database level catches a duplicate either way.

[albertlast]

Re-checked against f12217b88, the current head of release-3.0, reading that tree this time.

Both sites are as described in the correction above and unchanged:

Sources/Actions/Register2.php:539    WHERE {raw:email_address_field} = {string:email_address}
Sources/Actions/Register2.php:540        OR {raw:email_address_field} = {string:username}
Sources/Actions/Register2.php:543        'email_address_field' => Db::$db->case_sensitive ? 'LOWER(email_address)' : 'email_address',

Sources/Profile.php:1652             AND {raw:email_address_field} = {string:email_address}
Sources/Profile.php:1655             'email_address_field' => Db::$db->case_sensitive ? 'LOWER(email_address)' : 'email_address',

So the column is folded and the value is not, and on PostgreSQL an address containing any capital letter is not recognised as taken even against an identical existing one.

Working through the rest of the code for the same shape turned up that this is not a one-off. There are five comparisons in the tree that fold one side and not the other, and they split into two directions:

Column folded, value not — matches nothing on PostgreSQL:

• Sources/Actions/Register2.php and Sources/Profile.php, this issue.
• Sources/PersonalMessage/Search.php, now [3.0]: Searching personal messages by author finds nothing on PostgreSQL #9598.

Value folded, column not — same result, arrived at from the other side:

• Sources/User.php:3774 and :3798, now [3.0]: Finding a member by email address fails on PostgreSQL when the stored address has uppercase #9599.

The six other comparisons that fold only their column are correct, because their callers fold the value in PHP first: Admin/Members.php through strtolower(), and AutoSuggest.php, RequestMembers.php and PM.php through Utils::strtolower().

That is what makes this class hard to see in review. A comparison that folds neither side at least looks unfinished. One that folds a column looks handled, and the fault is in code somewhere else that was supposed to fold the value and does not.

#9597 lists all eleven of them, marking which callers fold and which do not, so the ones that do not stay visible. #9599 is out of reach of that test and needs its issue to stay open on its own.

[Sesquipedalian]

It is incorrect to claim that in practice every mail provider treats the local part of an email address as case-insensitive. It is perhaps more common to treat the local part as case-insensitive, and arguably wiser, but it is not a universal practice among mail providers. That in turn means that we would be wrong to absolutely forbid registering with two different email addresses that differ only in case.

However, it would be quite rare in practice to have two different people trying to register forum accounts with two legitimately separate email addresses that differed only in case. In the vast majority of situations, if someone tries to register an account using JDoe@example.com and the forum already has another account using the email address jdoe@example.com, it's probably the same person trying to create a second account. But not always. So the best way to deal with that situation would to intervene in the registration and say something like "It appears that you may already have an account registered on this forum. Do you want help recovering your existing account?" If the user does want that help, we can send them to the password reset page or something. But if the user says no, then we should proceed to register the account using the provided address.

[sbulen]

My sense is that the bigger concern these days would be JDoe@example.com trying to appear to be jdoe@example.com. I'd be more inclined to forbid it, & treat email as ci.

No major consumer email providers are case-sensitive in practice today.

[jdarwood007]

I'm going to agree, we shouldn't handle this and go against the RFC, even if its wrong.

[Sesquipedalian]

No major consumer email providers are case-sensitive in practice today

... except for the few that are.

Moreover, many people use email addresses provided by a company's in-house mail servers, where all bets are off.

Moreover again, when the repertoire of allowed characters in the local part is expanded beyond just ASCII, things get very complicated very quickly. Consider the case of Kostas Samaras, who has the email address ΚώσταςΣαμαράς@example.gr. His mail server might or might not treat some, any, all, or none of the following as aliases of that:

• κώσταςσαμαράς@example.gr
• κώστασσαμαράς@example.gr
• κώστασσαμαράσ@example.gr
• κωσταςσαμαρας@example.gr
• κωστασσαμαρας@example.gr
• κωστασσαμαρασ@example.gr

Four of those six options would make a Greek reader's skin crawl due to the mishandling of the sigmas. But the canonical casefolded form of the original is indeed one of those (specifically, κώστασσαμαράσ@example.gr). So would the mail server treat the casefolded form as the "real" one? And what about those accents? Would the mail server keep them or strip them? Would it allow but ignore them? If it allows but ignores them, would it allow any combination of accents, or would it allow only the original accents if any accents are present? The answer to all these questions is, "Who the heck knows? Could be anything."

The RFC's rules and advice are what they are for very good reasons. Any wise email provider will aim to make their available email addresses case-insensitive. But any software that deals with email addresses that it doesn't control (such as SMF) must also follow the RFC's rules and leave the local part of the email address alone.

[sbulen]

It would be seriously unexpected & dangerous behavior to allow dupes to either names or emails due to case sensitivity.

Further, this would break backwards dependency: Case insensitive is the norm in 2.1.x and 2.0.x today for names & emails.

In 2.1.7 today (MySQL), caps in usernames and emails are checked & refused with the messages:

(Bob2) This name is already in use by another member.
That email address (Bob2@bob2.com) is already being used by a registered member. If you feel this is a mistake, go to the login page and use the password reminder with that address.

In 2.1.7 today (PG), caps in usernames are checked & refused with the messages:

The following errors were detected in your registration. Please correct them to continue:
(Bob2) This name is already in use by another member.

In 2.1.7 today (PG), emails are not checked. I.e., this current bug should be expanded for 2.1, not just 3.0.

In 2.0.19 today (MySQL), caps in usernames & emails are checked & refused with the messages:

The following errors were detected in your registration. Please correct them to continue:
(Bob2) This name is already in use by another member.
That email address (Bob2@bob2.com) is being used by a registered member already. If you feel this is a mistake, go to the login page and use the password reminder with that address.

In the above instances, the bob2 name existed & the bob2@bob2.com email existed on another member.

Pretty sure 1.x behaves the same.

True it's anecdotal, but literally every workplace I've been at as employee or consultant had rigid naming conventions specifically to eliminate the possibility of people's names getting confused. Emails & usernames were case dependent; to do otherwise was considered against best practices. (I was involved in many cross-dept name-clash discussions, since my team supported access to the unix dev environments... Someone with clout was always escalating to break the rules... 🙄) And usernames in such environments are often tied to the email addresses; you know one, you know both.

[sbulen]

Regarding the Greek question, I'd suggest reaching out to spiros on the forum. Total expert in this area; Greek language instruction site, ancient & modern. My understanding is that his reluctance to migrate to 2.1 is because he has custom code in 2.0 for charset quirks; possibly this.