Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Passport.js using MySQL for Authentication with Express https://manjeshpv.github.io/mean-development-guide-2021/
// 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]);
});
}));
};
@haveaguess

This comment has been minimized.

Copy link

@haveaguess haveaguess commented Sep 4, 2014

Thanks for sharing this, it helped me get started with things. One thing I added was I use mysql escaping on the email otherwise you could suffer from a SQL injection attack right ?

@dkran

This comment has been minimized.

Copy link

@dkran dkran commented Sep 27, 2014

I appreciate this as well. There are so many MongoDB examples using models that makes it a bit abstract for us mysql guys

@tfeess

This comment has been minimized.

Copy link

@tfeess tfeess commented Oct 10, 2014

Thank you! Huge help to get an example not linked to MongoDB.

@yosiasz

This comment has been minimized.

Copy link

@yosiasz yosiasz commented Dec 12, 2014

wonderful stuff!!!

@pepziman

This comment has been minimized.

Copy link

@pepziman pepziman commented Mar 19, 2015

is there anymore walktrough or step by step tutorial to help better understanding? thx so much!

@christianboutin

This comment has been minimized.

Copy link

@christianboutin christianboutin commented Apr 15, 2015

Thanks a lot for this. It helped me make sense of the original tutorial and create my dynamodb implementation here: christianboutin/passportjs-dynamodb@b7e64d3

@Nilsbijlsma

This comment has been minimized.

Copy link

@Nilsbijlsma Nilsbijlsma commented Jun 11, 2015

Unfortunately, my LocalStrategy is never called. :(

@sanjay1432

This comment has been minimized.

Copy link

@sanjay1432 sanjay1432 commented Jun 24, 2015

passport.use function is never called don't know why?

@josh-stevens

This comment has been minimized.

Copy link

@josh-stevens josh-stevens commented Aug 25, 2015

Glad to see an example using SQL instead of Mongo. Two comments (though I realize this is pretty old):

Instead of concatenating user input into your SQL queries/inserts, you should really be putting a '?' in the query string so that you can bind parameters to that. Not only does it look cleaner, but it protects you from SQL injection attacks.

Also, I highly recommend using spaces for indentation rather than tabs. The tabs look ugly, and it makes your code hard to read. It's indented way too much, causing the lines to run onto the next line on my display. Either use 2 spaces for indentation, or set your tab width to '2' so that you aren't indenting so far.

@bala9593

This comment has been minimized.

Copy link

@bala9593 bala9593 commented Feb 15, 2016

how to implement in a mysql angularjs nodejs

@SuzanneMcC

This comment has been minimized.

Copy link

@SuzanneMcC SuzanneMcC commented Mar 3, 2016

Thanks for this, spared me a few headaches!

@bernatixer

This comment has been minimized.

Copy link

@bernatixer bernatixer commented Jun 30, 2016

Thanks for sharing, it worked out pretty well!

@technotim

This comment has been minimized.

Copy link

@technotim technotim commented Jan 5, 2017

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...

@anurag-itsolvs

This comment has been minimized.

Copy link

@anurag-itsolvs anurag-itsolvs commented Jan 27, 2017

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.

@leonus96

This comment has been minimized.

Copy link

@leonus96 leonus96 commented Mar 13, 2017

Muchas gracias!

@Rliao1992

This comment has been minimized.

Copy link

@Rliao1992 Rliao1992 commented Apr 6, 2017

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.

@sovietspaceship

This comment has been minimized.

Copy link

@sovietspaceship sovietspaceship commented Apr 9, 2017

@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

@phenric

This comment has been minimized.

Copy link

@phenric phenric commented Apr 28, 2017

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 ?

@mitrabrihas

This comment has been minimized.

Copy link

@mitrabrihas mitrabrihas commented Apr 30, 2017

@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

@phenric

This comment has been minimized.

Copy link

@phenric phenric commented May 1, 2017

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

@hoangtuan051

This comment has been minimized.

Copy link

@hoangtuan051 hoangtuan051 commented Jul 28, 2017

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

@Amouhwilliam

This comment has been minimized.

Copy link

@Amouhwilliam Amouhwilliam commented Dec 6, 2017

where is the route?

@ekabelly

This comment has been minimized.

Copy link

@ekabelly ekabelly commented Jan 28, 2018

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');

@h3ct0rjs

This comment has been minimized.

Copy link

@h3ct0rjs h3ct0rjs commented Apr 27, 2018

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

@pintuam

This comment has been minimized.

Copy link

@pintuam pintuam commented May 21, 2018

Thanks a Lot @manjeshpv
This works fine...

@Sankarbainapalli

This comment has been minimized.

Copy link

@Sankarbainapalli Sankarbainapalli commented May 23, 2018

thank you so much..

@arnab-kundu

This comment has been minimized.

Copy link

@arnab-kundu arnab-kundu commented Sep 6, 2018

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

@sumeet-bansal

This comment has been minimized.

Copy link

@sumeet-bansal sumeet-bansal commented Mar 8, 2019

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

@h3ct0rjs why bcrypt over crypto?

@juliobadilloguzman

This comment has been minimized.

Copy link

@juliobadilloguzman juliobadilloguzman commented May 11, 2019

Magnifico!!!

@smhk

This comment has been minimized.

Copy link

@smhk smhk commented Aug 25, 2019

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

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

@EmanuelGF

This comment has been minimized.

Copy link

@EmanuelGF EmanuelGF commented Sep 6, 2019

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

@rodriguesvinicius

This comment has been minimized.

Copy link

@rodriguesvinicius rodriguesvinicius commented Oct 20, 2019

thanks!!

@justinpeake

This comment has been minimized.

Copy link

@justinpeake justinpeake commented Jan 1, 2020

Can anyone share what the config file should look like?

@L-Andy

This comment has been minimized.

Copy link

@L-Andy L-Andy commented Jan 3, 2020

thanks great mr

@daverave13

This comment has been minimized.

Copy link

@daverave13 daverave13 commented May 6, 2020

perfect!! thank you

@imjvdn

This comment has been minimized.

Copy link

@imjvdn imjvdn commented May 18, 2020

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

how would you go about using bcrypt?

@cyberkuv

This comment has been minimized.

Copy link

@cyberkuv cyberkuv commented Jun 2, 2020

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

@pageneck

This comment has been minimized.

Copy link

@pageneck pageneck commented Sep 7, 2020

Thanks very much for sharing

@AliAzlanAziz

This comment has been minimized.

Copy link

@AliAzlanAziz AliAzlanAziz commented Dec 2, 2020

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment