summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--elm/User/Login.elm2
-rw-r--r--elm/User/Register.elm4
-rw-r--r--lib/Multi/API.pm12
-rw-r--r--lib/VNWeb/Auth.pm2
-rw-r--r--lib/VNWeb/HTML.pm2
-rw-r--r--lib/VNWeb/User/Edit.pm2
-rw-r--r--lib/VNWeb/User/List.pm4
-rw-r--r--lib/VNWeb/User/Login.pm4
-rw-r--r--lib/VNWeb/User/Page.pm2
-rw-r--r--lib/VNWeb/User/Register.pm2
-rw-r--r--lib/VNWeb/Validation.pm18
-rw-r--r--sql/schema.sql2
-rw-r--r--sql/tableattrs.sql1
-rw-r--r--util/updates/2021-10-28-username-casefold.sql2
14 files changed, 39 insertions, 20 deletions
diff --git a/elm/User/Login.elm b/elm/User/Login.elm
index c1c55dfe..11eb5dd5 100644
--- a/elm/User/Login.elm
+++ b/elm/User/Login.elm
@@ -63,7 +63,7 @@ type Msg
update : Msg -> Model -> (Model, Cmd Msg)
update msg model =
case msg of
- Username n -> ({ model | invalid = False, username = String.toLower n }, Cmd.none)
+ Username n -> ({ model | invalid = False, username = n }, Cmd.none)
Password n -> ({ model | invalid = False, password = n }, Cmd.none)
Newpass1 n -> ({ model | newpass1 = n, noteq = False }, Cmd.none)
Newpass2 n -> ({ model | newpass2 = n, noteq = False }, Cmd.none)
diff --git a/elm/User/Register.elm b/elm/User/Register.elm
index 915c9dbe..6d888629 100644
--- a/elm/User/Register.elm
+++ b/elm/User/Register.elm
@@ -50,7 +50,7 @@ type Msg
update : Msg -> Model -> (Model, Cmd Msg)
update msg model =
case msg of
- Username n -> ({ model | username = String.toLower n }, Cmd.none)
+ Username n -> ({ model | username = n }, Cmd.none)
EMail n -> ({ model | email = n }, Cmd.none)
VNs n -> ({ model | vns = Maybe.withDefault model.vns (String.toInt n) }, Cmd.none)
@@ -82,7 +82,7 @@ view model =
[ formField "username::Username"
[ inputText "username" model.username Username GUR.valUsername
, br_ 1
- , text "Preferred username. Must be lowercase, between 2 and 15 characters long and consist entirely of alphanumeric characters or a dash."
+ , text "Preferred username. Must be between 2 and 15 characters long and consist entirely of alphanumeric characters or a dash."
, text " Names that look like database identifiers (i.e. a single letter followed by several numbers) are also disallowed."
]
, formField "email::E-Mail"
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
diff --git a/sql/schema.sql b/sql/schema.sql
index 138e11d3..b39e1897 100644
--- a/sql/schema.sql
+++ b/sql/schema.sql
@@ -988,7 +988,7 @@ CREATE TABLE users (
perm_tag boolean NOT NULL DEFAULT true, -- [pub] (public because this is used in calculating VN tag scores)
perm_tagmod boolean NOT NULL DEFAULT false,
perm_review boolean NOT NULL DEFAULT true,
- username varchar(20) NOT NULL UNIQUE, -- [pub]
+ username varchar(20) NOT NULL, -- [pub]
uniname text NOT NULL DEFAULT '',
ip inet NOT NULL DEFAULT '0.0.0.0',
skin text NOT NULL DEFAULT '',
diff --git a/sql/tableattrs.sql b/sql/tableattrs.sql
index 942c465a..f79e20cc 100644
--- a/sql/tableattrs.sql
+++ b/sql/tableattrs.sql
@@ -161,4 +161,5 @@ CREATE UNIQUE INDEX changes_itemrev ON changes (itemid, rev);
CREATE UNIQUE INDEX chars_vns_pkey ON chars_vns (id, vid, COALESCE(rid, 'v1')); -- 'v1' is an invalid release id, but works as a 'no release specified' value in the UNIQUE qualifier.
CREATE UNIQUE INDEX chars_vns_hist_pkey ON chars_vns_hist (chid, vid, COALESCE(rid, 'v1'));
CREATE INDEX ulist_vns_voted ON ulist_vns (vid, vote_date) WHERE vote IS NOT NULL; -- For VN recent votes & vote graph. INCLUDE(vote) speeds up vote graph even more
+CREATE UNIQUE INDEX users_username_key ON users (lower(username));
CREATE INDEX users_ign_votes ON users (id) WHERE ign_votes;
diff --git a/util/updates/2021-10-28-username-casefold.sql b/util/updates/2021-10-28-username-casefold.sql
new file mode 100644
index 00000000..88bc1238
--- /dev/null
+++ b/util/updates/2021-10-28-username-casefold.sql
@@ -0,0 +1,2 @@
+ALTER TABLE users DROP CONSTRAINT users_username_key;
+CREATE UNIQUE INDEX users_username_key ON users (lower(username));
generated by cgit v1.2.3 (git 2.25.1) at 2024-04-20 14:15:33 +0000