Uppercase usernames create new accounts

[rjl]

From Chris Paul:

We just noticed it actually that if you log in with any uppercases characters, it will work but then subsequent logins will have to match exactly.

Apparently, Maia generates a new ID for the account. Interesting that the white/blacklists and contents of the quarantine now are assigned to the new login.

Subsequent logins with all lowercase will work but there will be no quarantine/ no white/black-lists.

We're using LDAP auth with PostgreSQL.

[dmorton]

According to RFC's, the username portion of an email address *may* be case sensitive, and so it should be treated as such.

Should we have a system config option to specify whether to be case sensitive?

[dmorton]

[http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html Mysql is not

case sensitive]

[http://archives.postgresql.org/pgsql-php/2003-05/msg00045.php Postgresql is case sensitve] ...

For the 1.0 series, we could add an optional config value to config.php to force lowercase comparison or lowercase storage....

1.1 series should be a config option in the auth table.

[rjl]

This bug affects address-handling throughout every part of the Maia system except for amavisd-maia (which uses $localpart_is_case_sensitive to adjust the case of user-supplied addresses). In particular it is important to control how addresses are written to the database in the first place--they must be written exactly as they are going to be searched for later. This means applying amavisd-maia's case-sensitivity rules to other Perl and PHP scripts as well, so that raw addresses get adjusted before being written to the database. This is especially true for maiadbtool.pl, which could otherwise be writing a batch of raw addresses to the database without considering the local case-sensitivity convention, but it also applies to the administrative "add user" feature--any place we do INSERTs on the 'users' table, really.

[rjl]

Changeset [1235] implements fixes for maiadbtool.pl, including a --preserve-case flag and $preserve_case default setting for maia.conf. By default, e-mail addresses are treated as case-insensitive and are lowercased automatically. If the --preserve-case flag is set or $preserve_case is non-zero, the username portion of e-mail addresses will be treated as case-sensitive.

[mortonda@…]

It seems insane to even allow case sensitivity... Bob@… and bob@… - who would ever think those should be different accounts?

But if we were to lowercase everything before storage, it *could* break when rescuing email, since it might not be accurate. I doubt this would ever happen, but...

OTOH, this means that whenever we search for an email address or username, we need to force it to compare insensitively.

$select = "SELECT id FROM maia_users " .
                  "WHERE user_name = ?";

becomes:

$select = "SELECT id FROM maia_users " .
                  "WHERE LOWER(user_name) = LOWER(?)";

Is this bad on performance? I suppose it shouldn't cost too much extra to do a simple strToLower() call...

But it means a long code review to find every instance where this could be an issue. :(

Hey, I just found a new development: citext data type ... I think that might be a nice choice...

[mortonda@…]

[1328] block a login if it is about to yank away a users row from another account. Looking at the 1.1 trunk, I don't think it is an issue. postfilters can easily do the proper lowercasing if needed.

[mortonda@…]

Note of interest: while testing some code which involved changing the collation in mysql, it gave me this in domainsettings.php:

#1267 - Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (latin1_general_cs,IMPLICIT) for operation '=' 

So mixing collations means we'll have to do some explicit casting elsewhere too. blech.

[mortonda@…]

This is safer now, but the final solution is still elusive. let's keep the issue alive for future versions...