NodeJS Passport login script with MySQL database

NodeJS Passport login with MySQL

User authentication is an important feature in today’s dynamic applications. This tutorial explores NodeJS passport login with MySQL. In a previous Nodejs mongodb tutorial you learned about performing Insert, Update, Delete and View records, but these tasks need to be performed by an authenticated user. This tutorial explores this important feature.

To do nodejs passport login with mysql, following tasks are performed.

1. Create a database db_users.

2. Create a database table for tbl_users and insert sample data into the table.

3. Generate a NodeJS, Express Application using Express Generator.

4. Install required modules like using NPM.

5. Create a NodeJS passport login script with MySQL.

nodejs passport login

Create database and a table for users

Open phpmyadmin, click on SQL tab and execute SQL statements below.

Username is john and password is 12345
create database db_users;

use db_users;


--
-- Database: `db_users`
--

-- --------------------------------------------------------

--
-- Table structure for table `tbl_users`
--

CREATE TABLE `tbl_users` (

  `id` int(11) NOT NULL,


  `username` varchar(16) NOT NULL,

  `password` varchar(60) NOT NULL,

  `full_name` varchar(100) NOT NULL

) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_users`
--

INSERT INTO `tbl_users` (`id`, `username`, `password`, `full_name`) VALUES
(1, 'john', '6607a999607711cd339dce1de6d64425a0985cfd', 'John Doe');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `tbl_users`
--
ALTER TABLE `tbl_users`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `tbl_users`
--
ALTER TABLE `tbl_users`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=2;

After creating database and table, sample data is inserted into table. Password is encrypted with a SALT and SHA1 algorithm.

nodejs passport login table structure

If you want to generate password of your choice then you need to do following steps:

1. Password is encrypted with sha1 algorithm.

2. With password we are concatenating a SALT. 7fa73b47df808d36c5fe328546ddef8b9011b2c6

3. Suppose you want to create a and store a password in database as: P@55w0rd

Password should be 7fa73b47df808d36c5fe328546ddef8b9011b2c6P@55w0rd

4. Then Open PHPMyAdmin, Go to database table tbl_users.

5. Click on Insert

6. In password box add the SALT and password and from SHA1 in the drop down, in front of password. As you can see in the image below.

nodejs-passport-login-generate a new password in mysql

Generate a NodeJS application using Express

After creating database let us generate NodeJS, express application using express generator.

Install Express Generator

npm install express-generator -g

After express generator installation, type command below to generate application.

Generate nodejs-mysql-login express app

Command below generates an express application. Pug is used as view engine.

express --view=pug nodejs-mysql-login

nodejs passport login application generated app

Install dependencies and run application

After generation of app skeleton, change directory and install dependencies or required modules. These modules are mentioned in package.json

{
  "name": "nodejs-mysql-login",
  "version": "0.0.0",
  "private": true,
  "scripts": {
    "start": "node ./bin/www"
  },

  "dependencies": {
    "body-parser": "~1.16.0",
    "cookie-parser": "~1.4.3",
    "debug": "~2.6.0",
    "express": "~4.14.1",
    "morgan": "~1.7.0",
    "pug": "~2.0.0-beta10",
    "serve-favicon": "~2.3.2"
 
 }

}

Please type cd nodejs-mysql-login  and then npm install  To run the app, type command below.

DEBUG=nodejs-mysql-login:* npm start

Open browser and type following URL http://localhost:3000 to view running app.

Create NodeJS passport login script with MySQL

Open the the project folder in an IDE such as sublime text.

Create login view and add login form

Inside Views folder open Layout.pug, add bootstrap CSS and JS files. Add a new folder login and create a file named index.pug in it. Add following pug code.

.wrapper

        form.form-signin(method='post', action='/signin')

          h3.form-signin-heading Nodejs MySQL Login Script
          |
          - if(message){
          p(style='color:red') #{message}
          - }
          input.form-control(type='text', name='username', placeholder='Username')
          |
          input.form-control(type='password', name='password', placeholder='Password')
          label.checkbox
          |
          button.btn.btn-lg.btn-primary.btn-block(type='submit') Login

Login form

nodejs passport login login form

After creating form, we have to install appropriate modules

MySQL database connection file

Add a folder called lib and create a file dbconn.js.

var mysql        = require('mysql');

var connection   = mysql.createConnection({

  supportBigNumbers: true,

  bigNumberStrings: true,

  host     : "localhost",

  user     : "root",

  password : "",

  database : "db_users"

});

module.exports = connection;

Change host name, username, password and database name according to your system settings.

Install passport and other modules

In order to perform nodejs passport login with mysql, you have to install Passport and other modules. Open command prompt, navigate to nodejs mysql login project directory and run commands given below.

npm install connect-flash

npm install passport

npm install passport-local

npm install express-session

npm install memory

npm install crypto

npm install mysql

Connect-flash module

Connect-flash module is a session area to store messages that are written to flash and are cleared after user has seen the messages.

Passport module

Passport module is a NodeJS module used as authentication middleware.

Passport-local module

This NodeJS module used to authenticate using a username and password.

Express-session and memory modules

This module is used to store session data.

Crypto module

Crypto module is JavaScript implementation of cryptographic algorithms.

MySQL module

This NodeJS module is driver for MySQL.

One of our reader pointed out that:

npm install memory

and

var BetterMemoryStore = require(__dirname + ‘/memory’);

Did not work for him, Gave the error “store.on is not a function”.  The problem was fixed by instead doing:

npm install session-memory-store

var BetterMemoryStore = require(‘session-memory-store’)(sess);

So if you find the same error please install the memory store module described above

Add modules in app.js

Open app.js file and include these modules.

var flash             = require('connect-flash');

var crypto            = require('crypto');

var passport          = require('passport');

var LocalStrategy     = require('passport-local').Strategy;

var connection        = require('./lib/dbconn');

var sess              = require('express-session');

var Store             = require('express-session').Store;
 
var BetterMemoryStore = require(__dirname + '/memory');

Express session store and expiration

 var store = new BetterMemoryStore({ expires: 60 * 60 * 1000, debug: true });

 app.use(sess({

    name: 'JSESSION',

    secret: 'MYSECRETISVERYSECRET',

    store:  store,

    resave: true,

    saveUninitialized: true

}));

Code above sets expiration time for session in store variable. Then session name and session secret is set.

Passport module initialization

Flash module is mounted using app.use. After flash module passport and passport session module is initialized.

app.use(flash());

app.use(passport.initialize());

app.use(passport.session());

 PassportJS local strategy for Signin

Passport module LocalStrategy is used to authenticate user locally. Other then local strategy user can be authenticated using Twitter or Facebook etc. In LocalStrategyverify callback is required and credentials are passed to it. done method is called. If username and password are not entered an error message is displayed to client.

PassportJS login process

Passport LocalStrategy is called local, and username and password fields are specified. Request object req is passed to callback function. A SALT is defined and is concatenated with password. A query is passed to connection.query() method of mysql driver to fetch user information based on username entered by user.

If provided username is not correct then an error message is displayed to user, if username is correct then password concatenated with SALT and is hashed with sha1 algorithm using crypto module. If password is correct user information is returned.

passport.use('local', new LocalStrategy({

  usernameField: 'username',

  passwordField: 'password',

  passReqToCallback: true //passback entire req to call back
} , function (req, username, password, done){


      if(!username || !password ) { return done(null, false, req.flash('message','All fields are required.')); }

      var salt = '7fa73b47df808d36c5fe328546ddef8b9011b2c6';

      connection.query("select * from tbl_users where username = ?", [username], function(err, rows){

          console.log(err); console.log(rows);

        if (err) return done(req.flash('message',err));

        if(!rows.length){ return done(null, false, req.flash('message','Invalid username or password.')); }

        salt = salt+''+password;

        var encPassword = crypto.createHash('sha1').update(salt).digest('hex');


        var dbPassword  = rows[0].password;

        if(!(dbPassword == encPassword)){

            return done(null, false, req.flash('message','Invalid username or password.'));

         }

        return done(null, rows[0]);

      });

    }

));

Serialize and deserialize user information

Passport serializes user information to store in session, deserialize function is used to deserialize the data.

passport.serializeUser(function(user, done){

    done(null, user.id);

});

passport.deserializeUser(function(id, done){

    connection.query("select * from tbl_users where id = "+ id, function (err, rows){

        done(err, rows[0]);

    });

});

Signin route for GET method in app.js

Create a signin route inside app.js and render login/index.pug file.

app.get('/signin', function(req, res){

  res.render('login/index',{'message' :req.flash('message')});

});

Signin route for POST method to authenticate requests

When user fills in username and password in login form, data is posted to signin route in app.js.

app.post("/signin", passport.authenticate('local', {

    successRedirect: '/profile',

    failureRedirect: '/signin',

    failureFlash: true

}), function(req, res, info){

    res.render('login/index',{'message' :req.flash('message')});

});

To authenticate requests using passport we have to use passport.authenticate and specify local strategy.

If user is signed in successfully, is redirected to profile page, if signin is failed, user is redirected back to signin page and appropriate message is displayed.

To view running signin page, open browser and type following URL

http://localhost:3000/signin

Protect routes with isAutehticated

Request object’s isAuthenticated method is used to validate whether user is logged in or not. So if  there are routes like profile that needs to be protected from public access and should only be visible to authorized user, isAuthenticated is added in route.

router.get('/', isAuthenticated, function(req, res, next) { ... }

The code below checks if user is authenticated then redirected to next page otherwise redirected back to login page.

function isAuthenticated(req, res, next) {

  if (req.isAuthenticated())

    return next();

  res.redirect('/signin');

}

Summary

In this tutorial you have learned about nodejs passport login with MySQL. We created a database and table. Created a form with bootstrap. Installed passport and other related modules, added a local strategy to perform signin and authenticated a route.

Stay tuned to for more great tutorials. To download the tutorial code visit following link. Follow us on twitter  to get informed about upcoming tutorials. Leave your comments and feedback below. thanks for reading.

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

  • The Master

    “Create database and a table for users” MySQL code is full of errors

    • jason

      Hi,

      Thanks for your comment. Yes there was an error in SQL, in create table statement, AUTO_INCREMENT was missing for the “id” field.

      This is fixed now and SQL code is working in tutorial and example code as well.

      Thanks again for your feedback on this.

  • rfritzemeier

    npm install memory
    and
    var BetterMemoryStore = require(__dirname + ‘/memory’);

    Did not work for me, it could not find the module, changing it to just ‘memory’ gave me the error “store.on is not a function”.

    I fixed the problem by instead doing:
    npm install session-memory-store
    var BetterMemoryStore = require(‘session-memory-store’)(sess);

    That seemed to work per what you were asking it to do in the tutorial (there’s also an npm memory-store module, but that doesn’t work)

    • Jason William

      Thanks for your comment. I will check the code again, and will update. Appreciate your feedback.

  • Mahesh

    this is very nice article, thanks.

    how to generate new password? and in sample data what is password?

    • Hi

      Thanks for your appreciation and comment. Article is updated on What is current password and how to generate a new password. But is explained below as well.

      Sample data password is: 12345

      1. Password is encrypted with sha1 algorithm.

      2. With password we are concatenating a SALT. 7fa73b47df808d36c5fe328546ddef8b9011b2c6

      3. Suppose you want to create a and store a password in database as: P@55w0rd

      Password should be 7fa73b47df808d36c5fe328546ddef8b9011b2c6P@55w0rd

      Please see the image below.

      https://uploads.disquscdn.com/images/217cb59c0264e04c427a5f5e46be668b97a58241876e30b68971ced6dea8edf4.png

  • Alex Thomson

    i am wondering when will passport.serializeUser() and passport.deserializeUser() will be executed? there is no function calls them.

    • Hi,
      Thanks for your comments.

      Passport serialize and deserialize user instance from a session store in order to support login. It is done automatically by passport.

      Regards,

  • Manuel Hernandez

    is there a route to register new users?