server/models/User.js

   1 const db = require("../utils/database");
   2 const genToken = require("../utils/tokenGenerator");
   3 const params = require("../config/parameters");
   4 const sendEmail = require('../utils/mailer');
   5
   6 /*
   7  * Structure:
   8  *   _id: integer
   9  *   name: varchar
  10  *   email: varchar
  11  *   loginToken: token on server only
  12  *   loginTime: datetime (validity)
  13  *   sessionToken: token in cookies for authentication
  14  *   notify: boolean (send email notifications for corr games)
  15  *   created: datetime
  16  *   bio: text
  17  */
  18
  19 const UserModel = {
  20   checkNameEmail: function(o) {
  21     return (
  22       (!o.name || !!(o.name.match(/^[\w-]+$/))) &&
  23       (!o.email || !!(o.email.match(/^[\w.+-]+@[\w.+-]+$/)))
  24     );
  25   },
  26
  27   create: function(name, email, notify, cb) {
  28     db.serialize(function() {
  29       const query =
  30         "INSERT INTO Users " +
  31         "(name, email, notify, created) VALUES " +
  32         "('" + name + "','" + email + "'," + notify + "," + Date.now() + ")";
  33       db.run(query, function(err) {
  34         cb(err, { id: this.lastID });
  35       });
  36     });
  37   },
  38
  39   // Find one user by id, name, email, or token
  40   getOne: function(by, value, fields, cb) {
  41     const delimiter = (typeof value === "string" ? "'" : "");
  42     db.serialize(function() {
  43       const query =
  44         "SELECT " + fields + " " +
  45         "FROM Users " +
  46         "WHERE " + by + " = " + delimiter + value + delimiter;
  47       db.get(query, cb);
  48     });
  49   },
  50
  51   getByIds: function(ids, cb) {
  52     db.serialize(function() {
  53       const query =
  54         "SELECT id, name " +
  55         "FROM Users " +
  56         "WHERE id IN (" + ids + ")";
  57       db.all(query, cb);
  58     });
  59   },
  60
  61   getBio: function(id, cb) {
  62     db.serialize(function() {
  63       const query =
  64         "SELECT bio " +
  65         "FROM Users " +
  66         "WHERE id = " + id;
  67       db.get(query, cb);
  68     });
  69   },
  70
  71   /////////
  72   // MODIFY
  73
  74   setLoginToken: function(token, id) {
  75     db.serialize(function() {
  76       const query =
  77         "UPDATE Users " +
  78         "SET loginToken = '" + token + "',loginTime = " + Date.now() + " " +
  79         "WHERE id = " + id;
  80       db.run(query);
  81     });
  82   },
  83
  84   setBio: function(id, bio) {
  85     db.serialize(function() {
  86       const query =
  87         "UPDATE Users " +
  88         "SET bio = ? " +
  89         "WHERE id = " + id;
  90       db.run(query, bio);
  91     });
  92   },
  93
  94   // Set session token only if empty (first login)
  95   // NOTE: weaker security (but avoid to re-login everywhere after each logout)
  96   // TODO: option would be to reset all tokens periodically (every 3 months?)
  97   trySetSessionToken: function(id, cb) {
  98     db.serialize(function() {
  99       let query =
 100         "SELECT sessionToken " +
 101         "FROM Users " +
 102         "WHERE id = " + id;
 103       db.get(query, (err, ret) => {
 104         const token = ret.sessionToken || genToken(params.token.length);
 105         const setSessionToken =
 106           (!ret.sessionToken ? (", sessionToken = '" + token + "'") : "");
 107         query =
 108           "UPDATE Users " +
 109           // Also empty the login token to invalidate future attempts
 110           "SET loginToken = NULL, loginTime = NULL " +
 111           setSessionToken + " " +
 112           "WHERE id = " + id;
 113         db.run(query);
 114         cb(token);
 115       });
 116     });
 117   },
 118
 119   updateSettings: function(user) {
 120     db.serialize(function() {
 121       const query =
 122         "UPDATE Users " +
 123         "SET name = '" + user.name + "'" +
 124         ", email = '" + user.email + "'" +
 125         ", notify = " + user.notify + " " +
 126         "WHERE id = " + user.id;
 127       db.run(query);
 128     });
 129   },
 130
 131   /////////////////
 132   // NOTIFICATIONS
 133
 134   notify: function(user, message) {
 135     const subject = "vchess.club - notification";
 136     const body = "Hello " + user.name + " !" + `
 137 ` + message;
 138     sendEmail(params.mail.noreply, user.email, subject, body);
 139   },
 140
 141   tryNotify: function(id, message) {
 142     UserModel.getOne("id", id, "name, email, notify", (err, user) => {
 143       if (!err && user.notify) UserModel.notify(user, message);
 144     });
 145   },
 146
 147   ////////////
 148   // CLEANING
 149
 150   cleanUsersDb: function() {
 151     const tsNow = Date.now();
 152     // 86400000 = 24 hours in milliseconds
 153     const day = 86400000;
 154     db.serialize(function() {
 155       const query =
 156         "SELECT id, sessionToken, created, name, email " +
 157         "FROM Users";
 158       db.all(query, (err, users) => {
 159         let toRemove = [];
 160         users.forEach(u => {
 161           // Remove users unlogged for > 24h
 162           if (!u.sessionToken && tsNow - u.created > day)
 163           {
 164             toRemove.push(u.id);
 165             UserModel.notify(
 166               u,
 167               "Your account has been deleted because " +
 168               "you didn't log in for 24h after registration"
 169             );
 170           }
 171         });
 172         if (toRemove.length > 0) {
 173           db.run(
 174             "DELETE FROM Users " +
 175             "WHERE id IN (" + toRemove.join(",") + ")"
 176           );
 177         }
 178       });
 179     });
 180   },
 181 }
 182
 183 module.exports = UserModel;