sql-server - 在 SQL Server 2012 中使用 Node.js 进行身份验证

Question

我想使用node.js和passport创建一个简单的身份验证，但文档没有提到如何将SQL Server作为数据库包含在内，我已经成功地将mysql与node.js连接起来。

注释的代码是针对 mysql 的，它可以工作。但是当我执行此代码时，我收到此错误:“对象不是函数”

模型.js

        var DB = require('./db').DB;
        var sql = require('sql');
        /*var User = DB.Model.extend({
           tableName: 'tblUsers',
           idAttribute: 'userId'
        });*/
        var User = sql.define({
           name: 'tblusers',
           columns: ['id', 'username', 'password']
        });
        module.exports = {
           User: User
        };

db.js

           /*var Bookshelf = require('bookshelf');
           var config = {
              host: 'localhost',  // your host
              user: 'root', // your database user
              password: '', // your database password
              database: 'dbUsers',
              charset: 'UTF8_GENERAL_CI'
           };

           var DB = Bookshelf.initialize({
              client: 'mysql',
              connection: config
           });*/

           /*--------------------Connection--------------------------------*/

           var sql = require('mssql');


           var config = {
              user: 'test',
              password: '11111',
              server: 'ICEFOX-PC\\SQLSQL',
              database: 'dbusers'
           }

           var DB = sql.connect(config, function(err) {

              if (err){
                 throw err ;
              } else{


                 console.log('connected');
              }

           });


           /*--------------------Connection--------------------------------*/

           module.exports.DB = DB;

app.js

// vendor libraries
                var express = require('express');
               var bodyParser = require('body-parser');
               var cookieParser = require('cookie-parser');
               var session = require('express-session');
               var bcrypt = require('bcrypt-nodejs');
               var ejs = require('ejs');
               var path = require('path');
               var passport = require('passport');
               var LocalStrategy = require('passport-local').Strategy;


           // custom libraries
           // routes
           var route = require('./route');
           // model
           var Model = require('./model');

           var app = express();

           passport.use(new LocalStrategy(function(username, password, done) {

              new Model.User({username: username}).fetch().then(function(data) {
                 var user = data;
                 if(user === null) {
                    return done(null, false, {message: 'Invalid username or password'});
                 } else {
                    user = data.toJSON();
                    if(!bcrypt.compareSync(password, user.password)) {
                       return done(null, false, {message: 'Invalid username or password'});
                    } else {
                       return done(null, user);
                    }
                 }
              });
           }));

           passport.serializeUser(function(user, done) {
             done(null, user.username);
           });

           passport.deserializeUser(function(username, done) {
              new Model.User({username: username}).fetch().then(function(user) {
                 done(null, user);
              });
           });

           app.set('port', process.env.PORT || 3000);
           app.set('views', path.join(__dirname, 'views'));
           app.set('view engine', 'ejs');

           app.use(cookieParser());
           app.use(bodyParser());
           app.use(session({secret: 'secret strategic xxzzz code'}));
           app.use(passport.initialize());
           app.use(passport.session());

           // GET
           app.get('/', route.index);

           // signin
           // GET
           app.get('/signin', route.signIn);
           // POST
           app.post('/signin', route.signInPost);

           // signup
           // GET
           app.get('/signup', route.signUp);
           // POST
           app.post('/signup', route.signUpPost);

           // logout
           // GET
           app.get('/signout', route.signOut);

           /********************************/

           /********************************/
           // 404 not found
           app.use(route.notFound404);

           var server = app.listen(app.get('port'), function(err) {
              if(err) throw err;

              var message = 'Server is running @ http://localhost:' + server.address().port;
              console.log(message);
           });

路由.js

// vendor library
            var passport = require('passport');
            var bcrypt = require('bcrypt-nodejs');

        // custom library
        // model
        var Model = require('./model');

        // index
        var index = function(req, res, next) {
           if(!req.isAuthenticated()) {
              res.redirect('/signin');
           } else {

              var user = req.user;

              if(user !== undefined) {
                 user = user.toJSON();
              }
              res.render('index', {title: 'Home', user: user});
           }
        };

        // sign in
        // GET
        var signIn = function(req, res, next) {
           if(req.isAuthenticated()) res.redirect('/');
           res.render('signin', {title: 'Sign In'});
        };

        // sign in
        // POST
        var signInPost = function(req, res, next) {
           passport.authenticate('local', { successRedirect: '/',
                                  failureRedirect: '/signin'}, function(err, user, info) {
              if(err) {
                 return res.render('signin', {title: 'Sign In', errorMessage: err.message});
              } 

              if(!user) {
                 return res.render('signin', {title: 'Sign In', errorMessage: info.message});
              }
              return req.logIn(user, function(err) {
                 if(err) {
                    return res.render('signin', {title: 'Sign In', errorMessage: err.message});
                 } else {
                    return res.redirect('/');
                 }
              });
           })(req, res, next);
        };

        // sign up
        // GET
        var signUp = function(req, res, next) {
           if(req.isAuthenticated()) {
              res.redirect('/');
           } else {
              res.render('signup', {title: 'Sign Up'});
           }
        };

        // sign up
        // POST
        var signUpPost = function(req, res, next) {
           var user = req.body;
           var usernamePromise = null;
           usernamePromise = new Model.User({username: user.username}).fetch();

           return usernamePromise.then(function(model) {
              if(model) {
                 res.render('signup', {title: 'signup', errorMessage: 'username already exists'});
              } else {
                 //****************************************************//
                 // MORE VALIDATION GOES HERE(E.G. PASSWORD VALIDATION)
                 //****************************************************//
                 var password = user.password;
                 var hash = bcrypt.hashSync(password);

                 var signUpUser = new Model.User({username: user.username, password: hash});

                 signUpUser.save().then(function(model) {
                    // sign in the newly registered user
                    signInPost(req, res, next);
                 });    
              }
           });
        };

        // sign out
        var signOut = function(req, res, next) {
           if(!req.isAuthenticated()) {
              notFound404(req, res, next);
           } else {
              req.logout();
              res.redirect('/signin');
           }
        };

        // 404 not found
        var notFound404 = function(req, res, next) {
           res.status(404);
           res.render('404', {title: '404 Not Found'});
        };

        // export functions
        /**************************************/
        // index
        module.exports.index = index;

        // sigin in
        // GET
        module.exports.signIn = signIn;
        // POST
        module.exports.signInPost = signInPost;

        // sign up
        // GET
        module.exports.signUp = signUp;
        // POST
        module.exports.signUpPost = signUpPost;

        // sign out
        module.exports.signOut = signOut;

        // 404 not found
        module.exports.notFound404 = notFound404;

Answer 1

成功了!我已成功从 SQL 数据库验证用户和密码

passport.js//配置/passport.js

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

                // load up the user model
                var mysql = require('mysql');
                var bcrypt = require('bcrypt-nodejs');
                var sql = require('mssql');
                var config = {
                    user: 'test',
                    password: '11111',
                    server: 'ICEFOX-PC\\SQLSQL',
                    database: 'dbusers'
                }
                sql.connect(config, function(err) {

                    if (err){
                        throw err ;
                    } else{


                        console.log('connected');
                    }

                });
                var request = new sql.Request([config]);
                // 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(username, done) {
                        done(null, username.userId);
                    });

                    // used to deserialize the user
                    passport.deserializeUser(function(userId, done) {

                        request.query("select * from tblusers where userId='"+userId+"'",function(err,rows){

                                done(err, rows[0]);
                        });
                    });



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

                                request.query("select * from tblusers where username='"+username+"'",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]);
                                console.log('loged');

                            });



                        }));
                };

服务器.js//服务器.js

            // set up ======================================================================
            // get all the tools we need
            var express  = require('express');
            var app      = express();
            var port     = process.env.PORT || 8080;

            var passport = require('passport');
            var flash    = require('connect-flash');

            // configuration ===============================================================
            // connect to our database

            require('./config/passport')(passport); // pass passport for configuration

            app.configure(function() {

                // set up our express application
                app.use(express.logger('dev')); // log every request to the console
                app.use(express.cookieParser()); // read cookies (needed for auth)
                app.use(express.bodyParser()); // get information from html forms

                app.set('view engine', 'ejs'); // set up ejs for templating

                // required for passport
                app.use(express.session({ secret: 'vidyapathaisalwaysrunning' } )); // session secret
                app.use(passport.initialize());
                app.use(passport.session()); // persistent login sessions
                app.use(flash()); // use connect-flash for flash messages stored in session

            });

            // routes ======================================================================
            require('./app/routes.js')(app, passport); // load our routes and pass in our app and fully configured passport

            // launch ======================================================================
            app.listen(port);
            console.log('The magic happens on port ' + port);

routes.js//应用程序/routes.js

        module.exports = function(app, passport) {

        // =====================================
        // HOME PAGE (with login links) ========
        // =====================================
        app.get('/', function(req, res) {
            res.render('index.ejs'); // load the index.ejs file
        });

        // =====================================
        // LOGIN ===============================
        // =====================================
        // show the login form
        app.get('/login', function(req, res) {

            // render the page and pass in any flash data if it exists
            res.render('login.ejs', { message: req.flash('loginMessage') });
        });

        // process the login form
        app.post('/login', passport.authenticate('local-login', {
                successRedirect : '/profile', // redirect to the secure profile section
                failureRedirect : '/login', // redirect back to the signup page if there is an error
                failureFlash : true // allow flash messages
            }),
            function(req, res) {
                console.log("hello");

                if (req.body.remember) {
                  req.session.cookie.maxAge = 1000 * 60 * 3;
                } else {
                  req.session.cookie.expires = false;
                }
            res.redirect('/');
        });

        // =====================================
        // SIGNUP ==============================
        // =====================================
        // show the signup form
        app.get('/signup', function(req, res) {
            // render the page and pass in any flash data if it exists
            res.render('signup.ejs', { message: req.flash('signupMessage') });
        });

        // process the signup form
        app.post('/signup', passport.authenticate('local-signup', {
            successRedirect : '/profile', // redirect to the secure profile section
            failureRedirect : '/signup', // redirect back to the signup page if there is an error
            failureFlash : true // allow flash messages
        }));

        // =====================================
        // PROFILE SECTION =========================
        // =====================================
        // we will want this protected so you have to be logged in to visit
        // we will use route middleware to verify this (the isLoggedIn function)
        app.get('/profile', isLoggedIn, function(req, res) {
            res.render('profile.ejs', {
                user : req.user // get the user out of session and pass to template
            });
        });

        // =====================================
        // LOGOUT ==============================
        // =====================================
        app.get('/logout', function(req, res) {
            req.logout();
            res.redirect('/');
        });
    };

    // route middleware to make sure
    function isLoggedIn(req, res, next) {

        // if user is authenticated in the session, carry on
        if (req.isAuthenticated())
            return next();

        // if they aren't redirect them to the home page
        res.redirect('/');
    }

当然还有添加您的观点