Blog: Feb 24 2016
Node with a persistent connection to mySql, passport.js and bcrypt for User Authentication
The following code demonstartes using Sql, Node.js and Passport.JS for user authentication. In addition to using Passport with SQL, I will also show how to pool SQL for multiple connections, and how to pass this connection to multiple routes. I will not be walking through every step of setting up a CRUD app like this, but the entire source code is available on Github. Here I am focusing on two of the more complex parts, setting up the user authentication and the pooled MySql connection.
The app that I am using in this tutorial is NodeHire.com (under development). It is a site for both Developers and Business owners to post and apply for Node.js Development jobs. NodeHire is 100% free and eliminates the hassle of finding Node.js work for both parties.
To see the working app you can create a log in or use the demo log in Username: JSmith Password: 1234 at www.NodeHire.com.
First I will show how to use the local strategy with Passport.js using MySql. To verify the username and password, they need to be compared against the username and password stored in your MySQL table. The first step is to pass the username from the login form into a SQL query to your database. This query then returns the users previously hashed and saved password. Once the hashed password is retrieved, it can then be passed to the bcrypt password compare function to compare versus the unhashed input from the user. If there is a match, the user is logged into the application and able to access their data.
The code below shows how I connect with MySql, set up my passport local strategy, query the database, and validate the user.
passport.serializeUser(function(user, done) {
done(null, user);
});
passport.deserializeUser(function(user, done) {
done(null, user);
});
router.post('/login',passport.authenticate('local',{failureRedirect:'/', failureFlash:'Wrong Username or Password'}), function(req, res){
req.flash('success','You are now logged in');
res.redirect('/' /*, { "results": user } */ );
});
passport.use(new LocalStrategy(
function(username, password, done, req, flash ) {
console.log('in users the username is ' + username)
main.getConnection(function ( err, connection) {
if(err) { console.log(err); callback(true); return; }
connection.query('SELECT password FROM nodeusers WHERE username = ?', username, function(err, user) {
console.log(user +'got user')
parseResults(user, done);
});
});
function parseResults(user, done) {
Object.keys(user)[0];
var key = Object.keys(user)[0];
user[key];
var storedPw = user[key];
for(var i in storedPw){
returnedPw = storedPw[i];
}
console.log('returnedPw is defined here ' +returnedPw);
if(returnedPw === undefined){return done(null, false, {message: 'Invalid Password'})}
movePw(returnedPw, done);
}
function movePw (returnedPw, done) {
User.comparePassword(password, returnedPw, function(err, isMatch) {
if (err) return done(err);
if(isMatch) {
user = username;
return done(null, user);
} else {
console.log('Invalid Password');
req.flash('failureFlash','User Access Denied. False Password');
return done(null, false, { message: 'Invalid password' });
}
});
}
}
));
router.get('/logout', function(req, res){
req.logout();
// Success Message
req.flash('success', "You have logged out");
res.redirect('/');
});
function ensureAuthenticated(req, res, next) {
if (req.isAuthenticated()) {
return next();
}
res.redirect('/')
}
In the code above main.getConnection() is the variable which contains the MySql connection stored in an external file, main = require('..main.js'). This variable allows us to connect to SQL and perform querys in an efficient way.
In Main.Js (below) there is a Pooled connection to SQL, and an exported function that will make this connection accessible to all of our routes. Pooling the connections and making the connection in one file, and then passing this connection to all of the applications routes is more effective than creating a new sql connection and connecting independently from each route. The pooled connection is made available to other routes through an exported function. Once a route sends a query to the connection and it is completed, the connection is no longer needed and is released back into the pool.
var mysql = require('mysql');
var main;
function connectDatabase() {
// if the pool doesn't exist, create it
if (!main) {
// enter your db config here
var main = mysql.createPool({
host : '',
user : '',
password : '',
database : ''
});
main.getConnection(function(err){
if(!err) {
console.log('Database is connected!');
} else {
console.log('Error connecting database!');
}
});
return main;
}
//export the function above so that all of our routes can include this script, and call main.GetConnection whenever they need a connection.
module.exports = connectDatabase();
Below is another example of how this connection is accessed in a route. The jobs route is what allows users to submit Jobs, each job is tied to the user by their username. Jobs can then be accepted by other users. This code demonstrates how the job is accepted or registered to another user. The job object has a field for the founder of the job (the business owner or job poster) and another field for the person who registers or accepts the job. To avoid SQL injection, we are passing the values of the user and the bid id into the SQL query using ?. Since we are using multiple ? we need to pass these variables in as an array. Once again we are accessing our main variable to get the connection.
router.post('/register', function(req, res){
req.flash('success', 'You are now registered');
var BidId = req.body.bidId;
console.log('the bid id is ' + BidId );
main.getConnection(function ( err, connection) {
if(err) { console.log(err); callback(true); return; }
connection.query('UPDATE nodejobs SET users = ? WHERE bid_id = ?', [user, BidId] , function(err, results) {
console.log('all results query' + results);
if(err){console.log(err)};
if(!err) {
console.log(results)
res.render('bids/index' , { "results": results });
};
});
});
});
I hope this is helpful to anyone using SQL with node and user authentication. Follow me on twitter at @seanquinn781
Download the full source code on Github
