server/models/User.js

   1 var db = require("../utils/database");
   2 var genToken = require("../utils/tokenGenerator");
   3 var params = require("../config/parameters");
   4 var 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  */
  17
  18 const UserModel =
  19 {
  20     checkNameEmail: function(o)
  21     {
  22         if (typeof o.name === "string")
  23         {
  24             if (o.name.length == 0)
  25                 return "Empty name";
  26             if (!o.name.match(/^[\w]+$/))
  27                 return "Bad characters in name";
  28         }
  29         if (typeof o.email === "string")
  30         {
  31             if (o.email.length == 0)
  32                 return "Empty email";
  33             if (!o.email.match(/^[\w.+-]+@[\w.+-]+$/))
  34                 return "Bad characters in email";
  35         }
  36     },
  37
  38     // NOTE: parameters are already cleaned (in controller), thus no sanitization here
  39     create: function(name, email, notify, callback)
  40     {
  41         db.serialize(function() {
  42             const insertQuery =
  43                 "INSERT INTO Users " +
  44                 "(name, email, notify, created) VALUES " +
  45                 "('" + name + "', '" + email + "', " + notify + "," + Date.now() + ")";
  46             db.run(insertQuery, err => {
  47                 if (!!err)
  48                     return callback(err);
  49                 db.get("SELECT last_insert_rowid() AS rowid", callback);
  50             });
  51         });
  52     },
  53
  54     // Find one user (by id, name, email, or token)
  55     getOne: function(by, value, cb)
  56     {
  57         const delimiter = (typeof value === "string" ? "'" : "");
  58         db.serialize(function() {
  59             const query =
  60                 "SELECT * " +
  61                 "FROM Users " +
  62                 "WHERE " + by + " = " + delimiter + value + delimiter;
  63             db.get(query, cb);
  64         });
  65     },
  66
  67   getByIds: function(ids, cb) {
  68     db.serialize(function() {
  69       const query =
  70         "SELECT id, name " +
  71         "FROM Users " +
  72         "WHERE id IN (" + ids + ")";
  73       db.all(query, cb);
  74     });
  75   },
  76
  77     /////////
  78     // MODIFY
  79
  80     setLoginToken: function(token, uid, cb)
  81     {
  82         db.serialize(function() {
  83             const query =
  84                 "UPDATE Users " +
  85                 "SET loginToken = '" + token + "', loginTime = " + Date.now() + " " +
  86                 "WHERE id = " + uid;
  87             db.run(query, cb);
  88         });
  89     },
  90
  91     // Set session token only if empty (first login)
  92     // TODO: weaker security (but avoid to re-login everywhere after each logout)
  93     trySetSessionToken: function(uid, cb)
  94     {
  95         // Also empty the login token to invalidate future attempts
  96         db.serialize(function() {
  97             const querySessionToken =
  98                 "SELECT sessionToken " +
  99                 "FROM Users " +
 100                 "WHERE id = " + uid;
 101             db.get(querySessionToken, (err,ret) => {
 102                 if (!!err)
 103                     return cb(err);
 104                 const token = ret.sessionToken || genToken(params.token.length);
 105                 const queryUpdate =
 106                     "UPDATE Users " +
 107                     "SET loginToken = NULL" +
 108                     (!ret.sessionToken ? (", sessionToken = '" + token + "'") : "") + " " +
 109                     "WHERE id = " + uid;
 110                 db.run(queryUpdate);
 111                 cb(null, token);
 112             });
 113         });
 114     },
 115
 116     updateSettings: function(user, cb)
 117     {
 118         db.serialize(function() {
 119             const query =
 120                 "UPDATE Users " +
 121                 "SET name = '" + user.name + "'" +
 122                 ", email = '" + user.email + "'" +
 123                 ", notify = " + user.notify + " " +
 124                 "WHERE id = " + user.id;
 125             db.run(query, cb);
 126         });
 127     },
 128
 129   /////////////////
 130   // NOTIFICATIONS
 131
 132   tryNotify: function(oppId, message)
 133   {
 134         UserModel.getOne("id", oppId, (err,opp) => {
 135       if (!err || !opp.notify)
 136         return; //error is ignored here (TODO: should be logged)
 137       const subject = "vchess.club - notification";
 138       const body = "Hello " + opp.name + "!\n" + message;
 139       sendEmail(params.mail.noreply, opp.email, subject, body, err => {
 140         res.json(err || {});
 141       });
 142     });
 143   },
 144
 145   ////////////
 146   // CLEANING
 147
 148   cleanUsersDb: function()
 149   {
 150     const tsNow = Date.now();
 151     // 86400000 = 24 hours in milliseconds
 152     const day = 86400000;
 153     db.serialize(function() {
 154       const query =
 155         "SELECT id, sessionToken, created " +
 156         "FROM Users";
 157       db.all(query, (err, users) => {
 158         users.forEach(u => {
 159           // Remove unlogged users for >1 day
 160           if (!u.sessionToken && tsNow - u.created > day)
 161             db.run("DELETE FROM Users WHERE id = " + u.id);
 162         });
 163       });
 164     });
 165   },
 166 }
 167
 168 module.exports = UserModel;