diff options
-rw-r--r-- | lib/VNDB/DB/Users.pm | 51 | ||||
-rw-r--r-- | lib/VNWeb/User/Edit.pm | 1 | ||||
-rw-r--r-- | lib/VNWeb/User/Register.pm | 2 | ||||
-rw-r--r-- | util/sql/func.sql | 4 | ||||
-rw-r--r-- | util/updates/update_20191003b.sql | 1 |
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) |