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]);			
		
		});
		


    }));

};

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

It's a pretty nice code. I would suggest creating a local database connection for each method instead of declaring a global variable. Then, use connection.end() after you finished executing queries for that connection.

Here are some reasons why this approach is preferred:

Resource management: Opening and closing the database connection within each method ensures that the connection is only active when needed. This helps manage server resources more efficiently, especially in scenarios where multiple requests are being processed concurrently.

Connection isolation: Each method can have its own dedicated connection, which provides better isolation and prevents potential issues when multiple requests are accessing the database simultaneously. It avoids interference between queries executed by different Passport.js methods.

Error handling: With a dedicated connection in each method, error handling becomes more straightforward. If an error occurs during a query, it is easier to handle and propagate the error within that specific method, without affecting other parts of the application that may be using the database connection.

Flexibility: By creating connections within each method, you have the flexibility to configure the connection parameters specifically for that method's requirements. This allows you to customize options such as host, user, password, and database on a per-method basis if needed.