[3.0]: Finding a member by email address fails on PostgreSQL when the stored address has uppercase

[albertlast]

Basic Information

Addressing a personal message or a newsletter to a member by their email address fails on PostgreSQL whenever the stored address contains a capital letter. On MySQL it works.

User::find() folds the names it was given but not the column it compares them against. Sources/User.php:3774:

$names[$i] = trim(Utils::strtolower($name));

and then Sources/User.php:3790-3798:

$comparison = $use_wildcards ? 'LIKE' : '=';
...
$email_condition = '
    OR (email_address ' . $comparison . ' \'' . implode('\') OR (email_address ' . $comparison . ' \'', $names) . '\')';

The member name and display name beside it are folded on both sides — $member_name and $real_name a few lines below get LOWER() on PostgreSQL to match the folded $names. The email column does not, so it is compared as stored against a value that has been lower-cased.

On PostgreSQL that means a member whose address is User@Example.com is never found, because the query asks for email_address = 'user@example.com'. On MySQL the collation folds the comparison and it matches.

This is the mirror image of #9594 and #9598: there a column is folded and the value is not, here the value is folded and the column is not. Either way the two sides can never meet on PostgreSQL.

The email branch requires the moderate_forum permission, so it is reached by moderators and administrators rather than by every member. User::find() is called from:

• Sources/PersonalMessage/PM.php:940, resolving the recipients of a personal message.
• Sources/Actions/Admin/News.php:470, resolving newsletter recipients.
• Sources/Actions/Admin/Maintenance.php:1323.

Steps to reproduce

1. Install SMF 3.0 on PostgreSQL.
2. Register a member whose email address contains a capital letter, for example User@Example.com.
3. As an administrator, start a new personal message and put User@Example.com in the "To" box.
4. Send it.

Expected result

The recipient is resolved and the message is sent, the same as on MySQL.

Actual result

The address is not recognised as a member and the message cannot be sent to them. Typing the address entirely in lower case does not help either, because the stored value is what differs.

Version/Git revision

3.0 Alpha 4 (f12217b88)

Database Engine

PostgreSQL

Database Version

PostgreSQL 17.10

PHP Version

8.4.24

Logs

# PostgreSQL 17.10 — the value has been folded, the column has not
smf=> SELECT 'User@Example.com' = 'user@example.com';
 f

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

Additional Information

Folding the column to match the already-folded values would fix it, which is what {ci:email_address} from #9596 expands to. It is not converted there, because $email_condition interpolates its values into the SQL directly rather than through the substitution layer, so it needs restructuring rather than a type change.

This one is also out of reach of the guard added in #9597: $comparison holds the operator from an earlier line, so no single line carries both the column and the operator for a scanner to pair up. It needs this issue to stay visible.

[albertlast]

Same root cause, filed separately: #9592, #9593, #9594 and #9598. Of those, #9594 and #9598 are the two that fold one side of the comparison and not the other, as this one does, though from the opposite direction.