Allow uppercase characters in usernames

Usernames are now case-insensitive and name changes and new
registrations are now checked for homograph attacks.

9 files changed, 32 insertions, 16 deletions

diff --git a/lib/Multi/API.pm b/lib/Multi/API.pm
index 1d0ac5cf..b8666b97 100644
--- a/lib/Multi/API.pm
+++ b/lib/Multi/API.pm
@@ -276,7 +276,6 @@ sub login {
     cres $c, ['ok'], 'Login using client "%s" ver. %s', $c->{client}, $c->{clientver};
     return;
   } else {
-    $arg->{username} = lc $arg->{username};
     return cerr $c, auth => "Password too weak, please log in on the site and change your password"
       if config->{password_db} && PWLookup::lookup(config->{password_db}, $arg->{password});
   }
@@ -295,7 +294,7 @@ sub login_auth {
       if $tm-AE::time() > config->{login_throttle}[1];
 
     # Fetch user info
-    cpg $c, 'SELECT id, encode(user_getscryptargs(id), \'hex\') FROM users WHERE username = $1', [ $arg->{username} ], sub {
+    cpg $c, 'SELECT id, username, encode(user_getscryptargs(id), \'hex\') FROM users WHERE lower(username) = lower($1)', [ $arg->{username} ], sub {
       login_verify($c, $arg, $tm, $_[0]);
     };
   };
@@ -307,7 +306,8 @@ sub login_verify {
 
   return cerr $c, auth => "No user with the name '$arg->{username}'" if $res->nRows == 0;
   my $uid = $res->value(0,0);
-  my $sargs = $res->value(0,1);
+  my $username = $res->value(0,1);
+  my $sargs = $res->value(0,2);
   return cerr $c, auth => "Account disabled" if !$sargs || length($sargs) != 14*2;
 
   my $token = urandom(20);
@@ -317,16 +317,16 @@ sub login_verify {
   cpg $c, 'SELECT user_login($1, decode($2, \'hex\'), decode($3, \'hex\'))', [ $uid, unpack('H*', $passwd), unpack('H*', $token) ], sub {
     if($_[0]->nRows == 1 && ($_[0]->value(0,0)||'') =~ /t/) {
       $c->{uid} = $uid;
-      $c->{username} = $arg->{username};
+      $c->{username} = $username;
       $c->{client} = $arg->{client};
       $c->{clientver} = $arg->{clientver};
       pg_cmd 'SELECT user_logout($1, decode($2, \'hex\'))', [ $uid, unpack('H*', $token) ];
-      cres $c, ['ok'], 'Successful login by %s (%s) using client "%s" ver. %s', $arg->{username}, $c->{uid}, $c->{client}, $c->{clientver};
+      cres $c, ['ok'], 'Successful login by %s (%s) using client "%s" ver. %s', $username, $c->{uid}, $c->{client}, $c->{clientver};
     } else {
       my @a = ( $tm + config->{login_throttle}[0], norm_ip($c->{ip}) );
       pg_cmd 'UPDATE login_throttle SET timeout = to_timestamp($1) WHERE ip = $2', \@a;
       pg_cmd 'INSERT INTO login_throttle (ip, timeout) SELECT $2, to_timestamp($1) WHERE NOT EXISTS(SELECT 1 FROM login_throttle WHERE ip = $2)', \@a;
-      cerr $c, auth => "Wrong password for user '$arg->{username}'";
+      cerr $c, auth => "Wrong password for user '$username'";
     }
   };
 }
diff --git a/lib/VNWeb/Auth.pm b/lib/VNWeb/Auth.pm
index 06ca88ec..285367ca 100644
--- a/lib/VNWeb/Auth.pm
+++ b/lib/VNWeb/Auth.pm
@@ -175,7 +175,7 @@ sub login {
     my($self, $user, $pass, $pretend) = @_;
     return 0 if $self->uid || !$user || !$pass;
 
-    my $uid = tuwf->dbVali('SELECT id FROM users WHERE username =', \$user);
+    my $uid = tuwf->dbVali('SELECT id FROM users WHERE lower(username) = lower(', \$user, ')');
     return 0 if !$uid;
     my $encpass = $self->_encpass($uid, $pass);
     return 0 if !$encpass;
diff --git a/lib/VNWeb/HTML.pm b/lib/VNWeb/HTML.pm
index 0b77408e..b1517c8a 100644
--- a/lib/VNWeb/HTML.pm
+++ b/lib/VNWeb/HTML.pm
@@ -86,7 +86,7 @@ sub user_ {
     my $uniname = f 'uniname_can' && f 'uniname';
     a_ href => '/'.f('id'),
         $fancy && $uniname ? (title => f('name'), $uniname) :
-        (!$fancy && $uniname ? (title => $uniname) : (), $capital ? ucfirst f 'name' : f 'name');
+        (!$fancy && $uniname ? (title => $uniname) : (), $capital ? f 'name' : f 'name');
     txt_ '⭐' if $fancy && f 'support_can' && f 'support_enabled';
 }
 
diff --git a/lib/VNWeb/User/Edit.pm b/lib/VNWeb/User/Edit.pm
index 91bc8c3a..9b1dfd5a 100644
--- a/lib/VNWeb/User/Edit.pm
+++ b/lib/VNWeb/User/Edit.pm
@@ -136,7 +136,7 @@ elm_api UserEdit => $FORM_OUT, $FORM_IN, sub {
 
     if($own && $data->{username} ne $username) {
         return elm_NameThrottle if tuwf->dbVali('SELECT 1 FROM users_username_hist WHERE id =', \$data->{id}, 'AND date > NOW()-\'1 day\'::interval');
-        return elm_Taken if tuwf->dbVali('SELECT 1 FROM users WHERE id <>', \$data->{id}, 'AND username =', \$data->{username});
+        return elm_Taken if !is_unique_username $data->{username}, $data->{id};
         $set{username} = $data->{username};
         tuwf->dbExeci('INSERT INTO users_username_hist', { id => $data->{id}, old => $username, new => $data->{username} });
     }
diff --git a/lib/VNWeb/User/List.pm b/lib/VNWeb/User/List.pm
index 210e6a23..a67d5805 100644
--- a/lib/VNWeb/User/List.pm
+++ b/lib/VNWeb/User/List.pm
@@ -67,7 +67,7 @@ TUWF::get qr{/u/(?<char>[0a-z]|all)}, sub {
     )->data;
 
     my @where = (
-        $char eq 'all' ? () : $char eq '0' ? "ascii(username) not between ascii('a') and ascii('z')" : "username like '$char%'",
+        $char eq 'all' ? () : $char eq '0' ? "ascii(lower(username)) not between ascii('a') and ascii('z')" : "lower(username) like '$char%'",
         $opt->{q} ? sql_or(
             auth->permUsermod && $opt->{q} =~ /@/ ? sql('id IN(SELECT y FROM user_emailtoid(', \$opt->{q}, ') x(y))') : (),
             $opt->{q} =~ /^u?([0-9]{1,6})$/ ? sql 'id =', \"u$1" : (),
@@ -80,7 +80,7 @@ TUWF::get qr{/u/(?<char>[0a-z]|all)}, sub {
            FROM users u
           WHERE', sql_and(@where),
          'ORDER BY', {
-                  username   => 'username',
+                  username   => 'lower(username)',
                   registered => 'id',
                   vns        => 'c_vns',
                   votes      => 'c_votes',
diff --git a/lib/VNWeb/User/Login.pm b/lib/VNWeb/User/Login.pm
index fa679325..0aaa1aba 100644
--- a/lib/VNWeb/User/Login.pm
+++ b/lib/VNWeb/User/Login.pm
@@ -34,7 +34,7 @@ elm_api UserLogin => undef, {
     }
 
     # Failed login, log and update throttle.
-    auth->audit(tuwf->dbVali('SELECT id FROM users WHERE username =', \$data->{username}), 'bad password', 'failed login attempt');
+    auth->audit(tuwf->dbVali('SELECT id FROM users WHERE lower(username) = lower(', \$data->{username}, ')'), 'bad password', 'failed login attempt');
     my $upd = {
         ip      => \$ip,
         timeout => sql_fromtime $tm + config->{login_throttle}[0]
@@ -50,7 +50,7 @@ elm_api UserChangePass => undef, {
     newpass  => { password => 1 },
 }, sub {
     my $data = shift;
-    my $uid = tuwf->dbVali('SELECT id FROM users WHERE username =', \$data->{username});
+    my $uid = tuwf->dbVali('SELECT id FROM users WHERE lower(username) = lower(', \$data->{username}, ')');
     die if !$uid;
     return elm_InsecurePass if is_insecurepass $data->{newpass};
     auth->audit($uid, 'password change', 'after login with an insecure password');
diff --git a/lib/VNWeb/User/Page.pm b/lib/VNWeb/User/Page.pm
index 289d1b80..803fff65 100644
--- a/lib/VNWeb/User/Page.pm
+++ b/lib/VNWeb/User/Page.pm
@@ -23,7 +23,7 @@ sub _info_table_ {
             auth->permUsermod ? () : 'AND date > NOW()-\'1 month\'::interval', 'ORDER BY date DESC');
         td_ class => 'key', 'Username';
         td_ sub {
-            txt_ ucfirst $u->{user_name};
+            txt_ $u->{user_name};
             txt_ ' ('; a_ href => "/$u->{id}", $u->{id};
             txt_ ')';
             debug_ $u;
diff --git a/lib/VNWeb/User/Register.pm b/lib/VNWeb/User/Register.pm
index f8938a8e..cd7d4f8e 100644
--- a/lib/VNWeb/User/Register.pm
+++ b/lib/VNWeb/User/Register.pm
@@ -28,7 +28,7 @@ elm_api UserRegister => undef, {
 
     my $num = tuwf->dbVali("SELECT count FROM stats_cache WHERE section = 'vn'");
     return elm_Bot         if $data->{vns} < $num*0.995 || $data->{vns} > $num*1.005;
-    return elm_Taken       if tuwf->dbVali('SELECT 1 FROM users WHERE username =', \$data->{username});
+    return elm_Taken       if !is_unique_username $data->{username};
     return elm_DoubleEmail if tuwf->dbVali('SELECT 1 FROM user_emailtoid(', \$data->{email}, ') x');
 
     my $ip = tuwf->reqIP;
diff --git a/lib/VNWeb/Validation.pm b/lib/VNWeb/Validation.pm
index 9796351b..a365c853 100644
--- a/lib/VNWeb/Validation.pm
+++ b/lib/VNWeb/Validation.pm
@@ -15,6 +15,7 @@ use Exporter 'import';
 our @EXPORT = qw/
     samesite
     is_insecurepass
+    is_unique_username
     form_compile
     form_changed
     validate_dbid
@@ -36,7 +37,7 @@ TUWF::set custom_validations => {
     editsum     => { required => 1, length => [ 2, 5000 ] },
     page        => { uint => 1, min => 1, max => 1000, required => 0, default => 1, onerror => 1 },
     upage       => { uint => 1, min => 1, required => 0, default => 1, onerror => 1 }, # pagination without a maximum
-    username    => { regex => qr/^(?!-*[a-z][0-9]+-*$)[a-z0-9-]*$/, minlength => 2, maxlength => 15 },
+    username    => { regex => qr/^(?!-*[a-zA-Z][0-9]+-*$)[a-zA-Z0-9-]*$/, minlength => 2, maxlength => 15 },
     password    => { length => [ 4, 500 ] },
     language    => { enum => \%LANGUAGE },
     gtin        => { required => 0, default => 0, func => sub { $_[0] = 0 if !length $_[0]; $_[0] eq 0 || gtintype($_[0]) } },
@@ -100,6 +101,21 @@ sub is_insecurepass {
     config->{password_db} && PWLookup::lookup(config->{password_db}, shift)
 }
 
+# Test uniqueness of a username in the database. Usernames with similar
+# homographs are considered duplicate.
+# (Would be much faster and safer to do this normalization in the DB and put a
+# unique constraint on the normalized name, but we have a bunch of existing
+# username clashes that I can't just change)
+sub is_unique_username {
+    my($name, $excludeid) = @_;
+    my sub norm {
+        # lowercase, normalize 'i1l' and '0o'
+        sql "regexp_replace(regexp_replace(lower(", $_[0], "), '[1l]', 'i', 'g'), '0', 'o', 'g')";
+    };
+    !tuwf->dbVali('SELECT 1 FROM users WHERE', norm('username'), '=', norm(\$name),
+        $excludeid ? ('AND id <>', \$excludeid) : ());
+}
+
 
 # Recursively remove keys from hashes that have a '_when' key that doesn't
 # match $when. This is a quick and dirty way to create multiple validation