Passport.js using MySQL for Authentication with Express

passport.js

// config/passport.js
				
// load all the things we need
var LocalStrategy   = require('passport-local').Strategy;

var mysql = require('mysql');

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

connection.query('USE vidyawxx_build2');	

// expose this function to our app using module.exports
module.exports = function(passport) {

	// =========================================================================
    // passport session setup ==================================================
    // =========================================================================
    // required for persistent login sessions
    // passport needs ability to serialize and unserialize users out of session

    // used to serialize the user for the session
    passport.serializeUser(function(user, done) {
		done(null, user.id);
    });

    // used to deserialize the user
    passport.deserializeUser(function(id, done) {
		connection.query("select * from users where id = "+id,function(err,rows){	
			done(err, rows[0]);
		});
    });
	

 	// =========================================================================
    // LOCAL SIGNUP ============================================================
    // =========================================================================
    // we are using named strategies since we have one for login and one for signup
	// by default, if there was no name, it would just be called 'local'

    passport.use('local-signup', new LocalStrategy({
        // by default, local strategy uses username and password, we will override with email
        usernameField : 'email',
        passwordField : 'password',
        passReqToCallback : true // allows us to pass back the entire request to the callback
    },
    function(req, email, password, done) {

		// find a user whose email is the same as the forms email
		// we are checking to see if the user trying to login already exists
        connection.query("select * from users where email = '"+email+"'",function(err,rows){
			console.log(rows);
			console.log("above row object");
			if (err)
                return done(err);
			 if (rows.length) {
                return done(null, false, req.flash('signupMessage', 'That email is already taken.'));
            } else {

				// if there is no user with that email
                // create the user
                var newUserMysql = new Object();
				
				newUserMysql.email    = email;
                newUserMysql.password = password; // use the generateHash function in our user model
			
				var insertQuery = "INSERT INTO users ( email, password ) values ('" + email +"','"+ password +"')";
					console.log(insertQuery);
				connection.query(insertQuery,function(err,rows){
				newUserMysql.id = rows.insertId;
				
				return done(null, newUserMysql);
				});	
            }	
		});
    }));

    // =========================================================================
    // LOCAL LOGIN =============================================================
    // =========================================================================
    // we are using named strategies since we have one for login and one for signup
    // by default, if there was no name, it would just be called 'local'

    passport.use('local-login', new LocalStrategy({
        // by default, local strategy uses username and password, we will override with email
        usernameField : 'email',
        passwordField : 'password',
        passReqToCallback : true // allows us to pass back the entire request to the callback
    },
    function(req, email, password, done) { // callback with email and password from our form

         connection.query("SELECT * FROM `users` WHERE `email` = '" + email + "'",function(err,rows){
			if (err)
                return done(err);
			 if (!rows.length) {
                return done(null, false, req.flash('loginMessage', 'No user found.')); // req.flash is the way to set flashdata using connect-flash
            } 
			
			// if the user is found but the password is wrong
            if (!( rows[0].password == password))
                return done(null, false, req.flash('loginMessage', 'Oops! Wrong password.')); // create the loginMessage and save it to session as flashdata
			
            // all is well, return successful user
            return done(null, rows[0]);			
		
		});
		


    }));

};

Thanks for sharing, it worked out pretty well!

I'm new to NodeJS and found this very helpful getting mysql to work properly with passport! Thank you so much! I had made some changes by moving all the mysql queries to the user.js model so that there are minimal changes needed to passport.js. I'm new to github as well and realize I should have forked this script instead of creating new documents on my page. I'll have to sort that out later...

Good work @manjesh V. I have implemented passport-strategy for google authentication with mongoDB. Eventually I had to use Mysql. I had been looking this kind of example. You made exactly what I was looking for. You guys post this kind example and makes others life easier.
Keep It up.

Muchas gracias!

thanks for your example, is that possible signup user allow more fields?
ex: INSERT INTO users (email, username, password) VALUES (?,?,?)
for the existing sample only email and password can be insert.

@Rliao1992 by setting passReqToCallback to true, the callback is passed the request object as its first argument, so you can access the other parameters directly, e.g. req.query.email

Firstly, thank you for this helpful post !
But at line 54, I have "Error: Cannot enqueue Query after invoking quit" and I don't really understand why.
Anyone can help me ?

@phenric, somewhere in the code, if you are closing the connection(ex: connection.end()), it causes this error. Because you have invoked quit meaning the end of connection and there are enqued items still. hope it helps

@mitrabrihas, effectively, it was the problem. Thank you !

How can I design the database in mysql if I want also to save facebook account ?

where is the route?

this is how ur code should look like in app/server.js using this code (as passportConfig.js):

const session = require('express-session');
const app = express();
const BodyParser = require('body-parser');
const CookieParser = require('cookie-parser');
const {userResponse, validateUser, secret} = require('./config/config');
const passport = require('passport');
const passportConfig = require('./congif/passportCongig');

app.use(BodyParser.json());
app.use(CookieParser());
app.use(BodyParser.urlencoded({extended:true}));

passportConfig(passport);

app.use(session({
secret,
name:'cookie',
resave: false,
saveUninitialized:false,
cookie:{
httponly, //put here some values
maxAge,
secure
}
}));
app.use(passport.initialize());
app.use(passport.session());

app.post('/signup', passport.authenticate('local-signup'), userResponse);

app.post('/login', passport.authenticate('local-login'), userResponse);

app.get('/logout', (req, res)=>{
req.logout();
return res.json({status:'success'});
});

also i reccomand using crypto to hash ur passwords:
const passToCrypto = pass =>crypto.createHmac('sha256', secret).update(pass).digest('hex');

@ekabelly you should avoid using crypto, you should use bcrypt instead.

Thanks a Lot @manjeshpv
This works fine...

thank you so much..

const {userResponse, validateUser, secret} = require('./config/config');
Can you please share the code for config file inside config folder.

@ekabelly you should avoid using crypto, you should use bcrypt instead.

@h3ct0rjs why bcrypt over crypto?

Magnifico!!!

Here's a lovely long explanation with FULL SQL, cheers

https://stackoverflow.com/a/57642380/294884

This is great example. Thank you.
Just as a reminder, always protect against sql injection. Check this out link

thanks!!

Can anyone share what the config file should look like?

thanks great mr

perfect!! thank you

@ekabelly you should avoid using crypto, you should use bcrypt instead.

how would you go about using bcrypt?

@manjeshpv
Wow Thank you so much, you just helped me with my assignment!

Thanks very much for sharing

I am getting "Error: Failed to serialize user to session". I implemented above just changing very slightest code and using bcrypt. Plz help me here is the code https://stackoverflow.com/questions/65108451/error-failed-to-serialize-user-into-session

Thanks for sharing, I found it useful.

bro thank you i have been looking for solution for 2 days