Using passport.js and plain SQL query to authenticate

Question

I'm working on a project and I have the following issue. I want to implement logic for user login with Passport API but I'm having difficulties of understanding how it works, especially the way I want to implement it (with plain SQL queries). I have gone thru several tutorials which explain how this can be done ,but the problem is that in them it is shown only with ORMs, and I do not want it that way. I've wrote a few thousand lines of code so far ,but without success which were deleted after this of course and this is the reason I haven't provided any code below. I'm using MySQL and Express as frameworks to build the website. If you have any brief or advanced idea of how things can happen I will be happy to hear from you. Thanks in advance !

Answer 1

Passport can be quite confusing at times, I'll give that to you! I'm assuming based on your question that you want to use the "local" login strategy and not offer something like Google or GitHub Single Sign On. I'll also assume you want to use "Sessions" (cookies) rather than something like JWT.

To do this you'll need to first configure passport with your express app up front. This requires you to initialise passport and a session store (you can use MySQL if you like, or something like Redis).

Then you need to configure your "strategy" which in our cases is the local strategy.

I'll run you through an example with some code that shows how this can be done. I'll shove this all into one code snippet but you may wish to break this out into several files.

Snippet you can clone:

https://gist.github.com/BlueHatbRit/5d07d3f98d41d536a776b74fcb843174

Mirrored here for answer longevity:

const express = require('express');
const session = require('express-session');
const bodyParser = require('body-parser');
const passport = require('passport');
const LocalStrategy = require('passport-local').Strategy;

// Create the express app
const app = express();

// Initialise express-session module effectively deals with serilaising some
// form of ID in a cookie which is secured with the given secret. In this case
// express then remembers this ID in memory. When this cookie is handed
// back to your server, express-session takes that ID and matches it up to
// the data it has stored against that ID in memory. Remember, in production
// you will most probably want to hook this up to some sort of data store,
// either Redis, MySQL, etc...
app.use(session({ secret: "cats" }));

// We need some body parser setup to use Passport with express
// you can checkout the body parser and passport docs to find out why
app.use(bodyParser.urlencoded({ extended: false }));

// Now we initialise passport
app.use(passport.initialize());

// Now setup the session strategy, this happens after the express-session
// initialisation as that must run on a request first. Once we have the data
// from express-session (remember, it converted from a session ID given to
// the user via a cookie, back into the data we stored against the ID) we can
// then pull our any additional information.
app.use(passport.session());

passport.serializeUser(function(user, done) {
  // This happens at the end of a request, it recieves the
  // req.user object, and you can then choose what to serialise
  // into the session (returning the user a new cookie with a
  // session ID).
  // In most cases you'll want to store as little data as possible
  // so just a user.id might be fine.
  done(null, user.id);
});

passport.deserializeUser(function(id, done) {
  // Assume we stored the user ID in the session in the above
  // function call, we can now access it.
  // You can now take "id" and pass it into your database and
  // get back whatever you want in regards to the user. This may
  // just be a small representation of the user, or the entire
  // record.
  // You can use either SQL or an ORM here. The important bit is
  // that you call the "done" callback with whatever object you
  // want to represent the user.
  User.findById(id, function(err, user) {
    // In your main request handlers, you will then call `req.user`
    // and get back whatever you passed into the callback.
    done(err, user);
  });
});

// Now we setup the main "login" route, this will do the first round
// of authentication. It will take a username and password, will check
// those credentails and will then decide whether or not to log the user in.
passport.use(new LocalStrategy(function(username, password, done) {
  // Run your SQL here to find the user by their username
  // Then check their password is correct
  // If something fails then call the "done" callback with a descriptive error
  // otherwise call "done" with no error, and pass it the "user" object. This will
  // be assigned to req.user which will then later be put through our serialize
  // function above.
  // In this case I'm using an ORM, but you can use something to execute raw SQL
  // if you like.

  User.findOne({ username: username }, function(err, user) {
    if (err) { return done(err); }
    if (!user) {
      return done(null, false, { message: 'Incorrect username.' });
    }

    // This is a made up function here, you'll need to create this and fill it out
    // if you're using SQL you will probably have a function called "validPassword"
    // (not assigned to a user object) where you will then pass in the hashed password
    // from your database, and the password they provided you (the password string in this
    // case).
    if (!user.validPassword(password)) {
      return done(null, false, { message: 'Incorrect password.' });
    }

    // We have a user and the passwords match so we can return the user object!
    return done(null, user);
  }
});

// Now we need to mount our configured strategy to an endpoint
app.post('/login', function(req, res, next) {
  passport.authenticate('local', {
    successRedirect: '/dashboard', // The user logged in fine, redirect them do the dashboard
    failureRedirect: '/login', // The login failed, send them back to the login page
    // It is possible to use "connect-flash" here to send back the reason but that's outside of the scope of this
  });
});

// Now we'll create some middleware to ensure a user is logged in when trying to access
// a protected endpoint
function protected(req, res, next) {
  // req.user will only exist if they've been authenticated
  if (!req.user) {
    return next(new Error('nice try, but you are not logged in!');
  }

  return next();
}

app.get('/private-things', protected, function(req, res, next) {
  // This code will only be accessible if someone goes to /private-things and
  // has a valid session!
  console.log(the user is logged in!);
  console.log(req.user);

  res.sendStatus(200);
});

A warning, I have not run this code. All the code is there though you might spot a few syntax errors and will need to write the SQL to match up to your database.