How to create login endpoint using express-session and express-mysql-session

Question

I want to create a secure login. I'd like to add session but I can't figure out how they should be used together.

I have 2 codes, one code came from express-mysql-session and another code which I wrote and has the login (/api/login) endpoint.

Below is the code which I copied from the readme.md of express-mysql-session and it works.

var express = require('express');
var app = module.exports = express();
var session = require('express-session');
var MySQLStore = require('express-mysql-session')(session);

var options = {
    host: 'localhost',
    port: 3306,
    user: 'root',
    password: 'password',
    database: 'session_test'
};

var sessionStore = new MySQLStore(options);

app.use(session({
    key: 'session_cookie_name',
    secret: 'session_cookie_secret',
    store: sessionStore,
    resave: true,
    saveUninitialized: true
}));

Here is the output on the terminal. The code above ran well but not really sure what it did. I see it has established connection to the locally running mysql using netstat command

tcp4       0      0  127.0.0.1.3306         127.0.0.1.52470        ESTABLISHED
tcp4       0      0  127.0.0.1.52470        127.0.0.1.3306         ESTABLISHED

then the output

$ DEBUG=express-mysql-session* node index.js
express-mysql-session:log Creating session store +0ms
express-mysql-session:log Setting default options +2ms
express-mysql-session:log Creating sessions database table +46ms
express-mysql-session:log Setting expiration interval: 900000ms +42ms
express-mysql-session:log Clearing expiration interval +0ms

Then below is the basic login auth endpoint I created using Express. This works but I want to add express-session, express-mysql-session as well as use crypt, bcrypt or scrypt-for-humans but not sure how to integrate it.

const express = require('express');
const bodyParser = require('body-parser');
const mysql      = require('mysql');
const app = express();
app.use(bodyParser.json());       // to support JSON-encoded bodies
app.use(bodyParser.urlencoded({     // to support URL-encoded bodies
  extended: true
}));

app.set('port', (process.env.API_PORT || 8000));

const connection = mysql.createConnection({
  host     : 'localhost',
  user     : 'root',
  password : 'password',
  database : 'authdb'
});

connection.connect(function(err) {
  if (err) {
    console.error('error connecting: ' + err.stack);
    return;
  }

  console.log('connected as id ' + connection.threadId);
});

app.post('/api/login', function(req, res) {
  const user_id = req.body.user_id;
  const password = req.body.password;
  let response = {};
  res.setHeader('Content-Type', 'application/json');

  connection.query('SELECT password from user WHERE `username` = "' + user_id + '"' , function(err, rows) {
    if (err) throw err;

    if (rows.length > 0) {
      if (password === rows[0].password) {
        response.status = 200;
        response.message = "authenticated";
        response.authenticated = true;
        response.user_id = user_id;
      } else {
        response.status = 403;
        response.message = "Login failed!";
        response.authenticated = false;
        response.user_id = user_id;
      }
    } else {
      response.status = 403;
      response.message = "Login failed!";
      response.authenticated = false;
      response.user_id = user_id;
    }
    res.status(response.status).send(JSON.stringify(response));

  });

});

app.listen(app.get('port'), () => {
  console.log(`Find the server at: http://localhost:${app.get('port')}/`);
});

Answer 1

I got it working and quite happy with the results. My login endpoint is working great! I now have more ideas on how to make it better as well. Here is the screenshot of the REST client - https://i.stack.imgur.com/y6GAu.png and below is the endpoint

app.post('/api/login', function(req, res) {
  const user_id = req.body.user_id;
  const password = req.body.password;
  let response = {};
  res.setHeader('Content-Type', 'application/json');
  connection.query('SELECT * FROM authdb.users as authusers inner join authdb.passwords as hashed on authusers.email = hashed.email WHERE authusers.email = "' + user_id + '"' , function(err, rows) {
    if (err) throw err;

    Promise.try(function(){
      return scrypt.verifyHash(password, rows[0].password);
    }).then(function(){
        var sess = req.session;
        if (sess.views) {
          sess.views++;
        } else {
          sess.views = 1
        }
        response = { status: 200, message: "Login successful!", authenticated: true, user_id: user_id, views: sess.views }
        res.status(response.status).send(JSON.stringify(response));
    }).catch(scrypt.PasswordError, function(err){
        response = { status: 403, message: "Login failed!", authenticated: false, user_id: user_id }
        res.status(response.status).send(JSON.stringify(response));
    });
  });
});

To make it secure, I'll setup an EC2 behind an ELB which terminates all SSL connections and sends all traffic in clear to the NodeJS running my Express auth API spawned by PM2 or other better balancers. The AWS secgroup will only accept traffic whose source is the ELB.