NodeJS Passport login script with MySQL database

April 8, 2017 JavaScript, MySQL, NodeJS 32 Comments

Spread the love

Last updated:22nd March, 2018

NodeJS Passport login with MySQL

User authentication is an important feature in today’s dynamic applications. This tutorial explores NodeJS passport l ogin 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.

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.

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.

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

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

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 LocalStrategy a verify 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 the 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');

}

Redirect user to Profile page after login

After the user is successfully logged in will be redirected to /users route. User can view personal data from the database and also a logout link is provided so user can logout.

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 GitHub link. You can also clone this repository