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