summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--lib/VNDB/DB/Users.pm51
-rw-r--r--lib/VNWeb/User/Edit.pm1
-rw-r--r--lib/VNWeb/User/Register.pm2
-rw-r--r--util/sql/func.sql4
-rw-r--r--util/updates/update_20191003b.sql1
5 files changed, 6 insertions, 53 deletions
diff --git a/lib/VNDB/DB/Users.pm b/lib/VNDB/DB/Users.pm
index 9fa7c3ac..cccac169 100644
--- a/lib/VNDB/DB/Users.pm
+++ b/lib/VNDB/DB/Users.pm
@@ -6,8 +6,7 @@ use warnings;
use Exporter 'import';
our @EXPORT = qw|
- dbUserGet dbUserEdit dbUserAdd dbUserDel dbUserLogout
- dbUserEmailExists dbUserGetMail dbUserSetMail dbUserSetPerm
+ dbUserGet dbUserDel
dbNotifyGet dbNotifyMarkRead dbNotifyRemove
dbThrottleGet dbThrottleSet
|;
@@ -92,28 +91,6 @@ sub dbUserGet {
}
-# uid, %options->{ columns in users table }
-sub dbUserEdit {
- my($s, $uid, %o) = @_;
-
- my %h;
- defined $o{$_} && ($h{$_.' = ?'} = $o{$_})
- for (qw| username ign_votes email_confirmed |);
-
- return if scalar keys %h <= 0;
- return $s->dbExec(q|
- UPDATE users
- !H
- WHERE id = ?|,
- \%h, $uid);
-}
-
-
-# username, mail, [ip]
-sub dbUserAdd {
- $_[0]->dbRow(q|INSERT INTO users (username, mail, ip) VALUES(?, ?, ?) RETURNING id|, $_[1], $_[2], $_[3]||$_[0]->reqIP)->{id};
-}
-
# uid
sub dbUserDel {
@@ -121,32 +98,6 @@ sub dbUserDel {
}
-# uid, token
-sub dbUserLogout {
- $_[0]->dbExec(q|SELECT user_logout(?, decode(?, 'hex'))|, $_[1], unpack 'H*', $_[2]);
-}
-
-
-sub dbUserEmailExists {
- $_[0]->dbRow(q|SELECT user_emailexists(?) AS r|, $_[1])->{r};
-}
-
-
-sub dbUserGetMail {
- $_[0]->dbRow(q|SELECT user_getmail(?, ?, decode(?, 'hex')) AS r|, $_[1], $_[2], $_[3])->{r};
-}
-
-
-sub dbUserSetMail {
- $_[0]->dbExec(q|SELECT user_setmail(?, ?, decode(?, 'hex'), ?)|, $_[1], $_[2], $_[3], $_[4]);
-}
-
-
-sub dbUserSetPerm {
- $_[0]->dbExec(q|SELECT user_setperm(?, ?, decode(?, 'hex'), ?)|, $_[1], $_[2], $_[3], $_[4]);
-}
-
-
# %options->{ uid id what results page reverse }
# what: titles
sub dbNotifyGet {
diff --git a/lib/VNWeb/User/Edit.pm b/lib/VNWeb/User/Edit.pm
index 4e67bf4e..23302ee6 100644
--- a/lib/VNWeb/User/Edit.pm
+++ b/lib/VNWeb/User/Edit.pm
@@ -102,6 +102,7 @@ json_api qr{/u/edit}, $FORM, sub {
if(auth->permUsermod) {
tuwf->dbExeci(select => sql_func user_admin_setmail => \$data->{id}, \auth->uid, sql_fromhex(auth->token), \$data->{email});
} else {
+ return elm_DoubleEmail if tuwf->dbVali(select => sql_func user_emailexists => \$data->{email}, \$data->{id});
my $token = auth->setmail_token($data->{email});
my $body = sprintf
"Hello %s,"
diff --git a/lib/VNWeb/User/Register.pm b/lib/VNWeb/User/Register.pm
index c093fcee..2f79a93d 100644
--- a/lib/VNWeb/User/Register.pm
+++ b/lib/VNWeb/User/Register.pm
@@ -21,7 +21,7 @@ json_api '/u/register', {
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_DoubleEmail if tuwf->dbVali(select => sql_func user_emailexists => \$data->{email});
+ return elm_DoubleEmail if tuwf->dbVali(select => sql_func user_emailexists => \$data->{email}, \undef);
my $ip = tuwf->reqIP;
return elm_DoubleIP if tuwf->dbVali(
diff --git a/util/sql/func.sql b/util/sql/func.sql
index 0ffe902d..cdf1b605 100644
--- a/util/sql/func.sql
+++ b/util/sql/func.sql
@@ -769,8 +769,8 @@ CREATE OR REPLACE FUNCTION user_isvalidsession(integer, bytea, session_type) RET
$$ LANGUAGE SQL SECURITY DEFINER;
-CREATE OR REPLACE FUNCTION user_emailexists(text) RETURNS boolean AS $$
- SELECT true FROM users WHERE lower(mail) = lower($1) LIMIT 1
+CREATE OR REPLACE FUNCTION user_emailexists(text, integer) RETURNS boolean AS $$
+ SELECT true FROM users WHERE lower(mail) = lower($1) AND ($2 IS NULL OR id <> $2) LIMIT 1
$$ LANGUAGE SQL SECURITY DEFINER;
diff --git a/util/updates/update_20191003b.sql b/util/updates/update_20191003b.sql
index a7342463..d3c41005 100644
--- a/util/updates/update_20191003b.sql
+++ b/util/updates/update_20191003b.sql
@@ -13,6 +13,7 @@ DROP FUNCTION user_isloggedin(integer, bytea);
DROP FUNCTION user_update_lastused(integer, bytea);
DROP FUNCTION user_isvalidtoken(integer, bytea);
DROP FUNCTION user_setmail(integer, integer, bytea, text);
+DROP FUNCTION user_emailexists(text);
-- Convert old password reset tokens to the new session format
INSERT INTO sessions (uid, token, expires, type)