diff options
| -rw-r--r-- | public/common/client.js | 3 | ||||
| -rw-r--r-- | schema.sql | 38 | ||||
| -rw-r--r-- | server.js | 27 | ||||
| -rwxr-xr-x | tools/patchgame.js | 4 |
4 files changed, 48 insertions, 24 deletions
diff --git a/public/common/client.js b/public/common/client.js index 7d3ebcf..393eb0a 100644 --- a/public/common/client.js +++ b/public/common/client.js @@ -124,8 +124,7 @@ var game_title = document.title function update_title() { if (is_your_turn || (chat && chat.has_unread)) - document.title = "\u273b " + game_title + " \u273b" - // document.title = "\u2bc8 " + game_title + " \u2bc7" + document.title = "\u2bc8 " + game_title else document.title = game_title } @@ -38,18 +38,32 @@ create table if not exists users ( mail text unique collate nocase, notify integer default 0, is_verified boolean default 0, - is_banned boolean default 0, - ctime datetime default current_timestamp, - password text, - salt text, - about text + is_banned boolean default 0 ); insert or ignore into - users (user_id, name, mail, ctime) - values (0, 'Deleted', 'deleted@nowhere', null) + users (user_id, name, mail) + values (0, 'Deleted', 'deleted@nowhere') ; +create table if not exists user_password ( + user_id integer primary key, + password text, + salt text +); + +create table if not exists user_about ( + user_id integer primary key, + ctime datetime, + about text +); + +create table if not exists user_first_seen ( + user_id integer primary key, + ctime datetime, + ip text +); + create table if not exists user_last_seen ( user_id integer primary key, atime datetime, @@ -84,6 +98,7 @@ create view user_login_view as user_id, name, mail, notify, password, salt from users + left join user_password using(user_id) ; drop view if exists user_profile_view; @@ -92,7 +107,9 @@ create view user_profile_view as user_id, name, mail, notify, ctime, atime, about, is_banned from users + left join user_first_seen using(user_id) left join user_last_seen using(user_id) + left join user_about using(user_id) ; drop view if exists user_dynamic_view; @@ -892,10 +909,13 @@ end; drop trigger if exists trigger_delete_on_users; create trigger trigger_delete_on_users after delete on users begin + delete from user_password where user_id = old.user_id; + delete from user_first_seen where user_id = old.user_id; + delete from user_last_seen where user_id = old.user_id; + delete from user_about where user_id = old.user_id; + delete from webhooks where user_id = old.user_id; delete from logins where user_id = old.user_id; delete from tokens where user_id = old.user_id; - delete from webhooks where user_id = old.user_id; - delete from user_last_seen where user_id = old.user_id; delete from read_threads where user_id = old.user_id; delete from unread_chats where user_id = old.user_id; delete from contacts where me = old.user_id or you = old.user_id; @@ -421,14 +421,15 @@ const SQL_BLACKLIST_NAME = SQL("select exists ( select 1 from blacklist_name whe const SQL_EXISTS_USER_NAME = SQL("SELECT EXISTS ( SELECT 1 FROM users WHERE name=? )").pluck() const SQL_EXISTS_USER_MAIL = SQL("SELECT EXISTS ( SELECT 1 FROM users WHERE mail=? )").pluck() -const SQL_INSERT_USER = SQL("INSERT INTO users (name,mail,password,salt) VALUES (?,?,?,?) RETURNING user_id,name,mail") +const SQL_INSERT_USER = SQL("INSERT INTO users (name,mail) VALUES (?,?) RETURNING user_id,name,mail") const SQL_DELETE_USER = SQL("DELETE FROM users WHERE user_id = ?") const SQL_SELECT_LOGIN = SQL("SELECT * FROM user_login_view WHERE user_id=?") -const SQL_SELECT_USER_VIEW = SQL("SELECT * FROM user_view WHERE user_id=?") -const SQL_SELECT_USER_BY_NAME = SQL("SELECT * FROM user_view WHERE name=?") const SQL_SELECT_LOGIN_BY_MAIL = SQL("SELECT * FROM user_login_view WHERE mail=?") const SQL_SELECT_LOGIN_BY_NAME = SQL("SELECT * FROM user_login_view WHERE name=?") + +const SQL_SELECT_USER_VIEW = SQL("SELECT * FROM user_view WHERE user_id=?") +const SQL_SELECT_USER_BY_NAME = SQL("SELECT * FROM user_view WHERE name=?") const SQL_SELECT_USER_PROFILE = SQL("SELECT * FROM user_profile_view WHERE name=?") const SQL_SELECT_USER_DYNAMIC = SQL("select * from user_dynamic_view where user_id=?") const SQL_SELECT_USER_ID = SQL("SELECT user_id FROM users WHERE name=?").pluck() @@ -440,9 +441,12 @@ const SQL_UPDATE_USER_NOTIFY = SQL("UPDATE users SET notify=? WHERE user_id=?") const SQL_UPDATE_USER_NAME = SQL("UPDATE users SET name=? WHERE user_id=?") const SQL_UPDATE_USER_MAIL = SQL("UPDATE users SET mail=? WHERE user_id=?") const SQL_UPDATE_USER_VERIFIED = SQL("UPDATE users SET is_verified=? WHERE user_id=?") -const SQL_UPDATE_USER_ABOUT = SQL("UPDATE users SET about=? WHERE user_id=?") -const SQL_UPDATE_USER_PASSWORD = SQL("UPDATE users SET password=?, salt=? WHERE user_id=?") -const SQL_UPDATE_USER_LAST_SEEN = SQL("INSERT OR REPLACE INTO user_last_seen (user_id,atime,ip) VALUES (?,datetime(),?)") + +const SQL_SELECT_USER_ABOUT = SQL("SELECT about FROM user_about WHERE user_id=?").pluck() +const SQL_UPDATE_USER_ABOUT = SQL("insert or replace into user_about (user_id,about) values (?,?)") +const SQL_UPDATE_USER_PASSWORD = SQL("insert or replace into user_password (user_id,password,salt) values (?,?,?)") +const SQL_UPDATE_USER_FIRST_SEEN = SQL("insert or replace into user_first_seen (user_id,ctime,ip) values (?,datetime(),?)") +const SQL_UPDATE_USER_LAST_SEEN = SQL("insert or replace into user_last_seen (user_id,atime,ip) values (?,datetime(),?)") const SQL_UPDATE_USER_IS_BANNED = SQL("update users set is_banned=? where name=?") const SQL_SELECT_WEBHOOK = SQL("SELECT * FROM webhooks WHERE user_id=?") @@ -543,6 +547,7 @@ app.post("/signup", must_pass_altcha, function (req, res) { function err(msg) { res.render("signup.pug", { flash: msg }) } + let ip = req.headers["x-real-ip"] || req.ip || req.connection.remoteAddress || "0.0.0.0" let name = req.body.username let mail = req.body.mail let password = req.body.password @@ -561,7 +566,9 @@ app.post("/signup", must_pass_altcha, function (req, res) { return err("Password is too long!") let salt = crypto.randomBytes(32).toString("hex") let hash = hash_password(password, salt) - let user = SQL_INSERT_USER.get(name, mail, hash, salt) + let user = SQL_INSERT_USER.get(name, mail) + SQL_UPDATE_USER_FIRST_SEEN.run(user.user_id, ip) + SQL_UPDATE_USER_PASSWORD.run(user.user_id, hash, salt) login_insert(res, user.user_id) res.redirect("/profile") }) @@ -653,7 +660,7 @@ app.post("/reset-password", function (req, res) { return err("Invalid or expired token!") let salt = crypto.randomBytes(32).toString("hex") let hash = hash_password(password, salt) - SQL_UPDATE_USER_PASSWORD.run(hash, salt, user.user_id) + SQL_UPDATE_USER_PASSWORD.run(user.user_id, hash, salt) SQL_UPDATE_USER_VERIFIED.run(1, user.user_id) login_insert(res, user.user_id) return res.redirect("/profile") @@ -677,7 +684,7 @@ app.post("/change-password", must_be_logged_in, function (req, res) { return res.render("change_password.pug", { user: req.user, flash: "Wrong password!" }) let salt = crypto.randomBytes(32).toString("hex") let hash = hash_password(newpass, salt) - SQL_UPDATE_USER_PASSWORD.run(hash, salt, user.user_id) + SQL_UPDATE_USER_PASSWORD.run(user.user_id, hash, salt) return res.redirect("/profile") }) @@ -805,7 +812,7 @@ app.get("/change-about", must_be_logged_in, function (req, res) { }) app.post("/change-about", must_be_logged_in, function (req, res) { - SQL_UPDATE_USER_ABOUT.run(req.body.about, req.user.user_id) + SQL_UPDATE_USER_ABOUT.run(req.user.user_id, req.body.about) return res.redirect("/profile") }) diff --git a/tools/patchgame.js b/tools/patchgame.js index 1426a75..fef07a0 100755 --- a/tools/patchgame.js +++ b/tools/patchgame.js @@ -79,11 +79,9 @@ function is_valid_action(rules, state, role, action, arg) { let view = rules.view(state, role) let va = view.actions[action] if (va) { - if (Array.isArray(arg)) - arg = arg[0] if (Array.isArray(va) && va.includes(arg)) return true - if (arg === undefined || arg === null || arg === 1) + if (arg === undefined || arg === null || arg === 1 || Array.isArray(arg)) return (va === 1 || va === true || typeof va === "string") } return false |