NodeJs login form with SQL Server user auth

206
January 18, 2020, at 8:00 PM

I am having an issue with authenticating users using a SQL Server database. I have established the connection with the database and can pull user from the database. However when trying to query the database for authentication I get an "unhandledpromise - connection is closed" error.

app.js file:

var sql = require("mssql");
var express = require("express");
var session = require("express-session");
var bodyParser = require("body-parser");
var path = require("path");

var dbconfig = {
  server: "Server",
  database: "Test",
  user: "########",
  password: "####################",
  port: 1433,
  options : {
    encrypt: false
  }
};
var app = express();
app.use(session({
  secret: 'Secret',
  resave: true,
  saveUninitalized: true
}));
app.use(bodyParser.urlencoded({
  extended: true
}));
app.use(bodyParser.json());
app.get('/', function(request, response) {
  response.sendFile(path.join(__dirname + '/login.html'));
});
app.post('/auth', function(request, response) {
      var username = request.body.username;
      var password = request.body.password;
      var conn = new sql.ConnectionPool(dbconfig);
      var req = new sql.Request(conn);
      if (username && password) {
        conn.connect();
        req.query('Select * from Admin where username = ? and password = ?', [username, password], function(error, results, fields) {
            if (results.length > 0) {
              request.session.loggedin = true;
              resquest.session.username = username;
              response.redirect('/home');
            } else {
              response.send('Username and/or Password not found');
            }
            conn.close();
            response.end();
          });
        } else{
          response.send('Please enter Username and Password');
        }
      });
      app.get('/home', function(request, response){
        if(request.session.loggedin){
          response.send('Welcome back,' + request.session.username + '!');
        }else{
          response.send('Please sign');
        }
        response.end();
      });
      app.listen(3000);
      function getEMP() {
        var conn = new sql.ConnectionPool(dbconfig);
        var req = new sql.Request(conn);
        conn.connect(function(err) {
          if (err) {
            console.log(err);
            return;
          }
          req.query("Select * from Admin", function(err, recordset) {
            if (err) {
              console.log(err)
            } else {
              console.log(recordset)
            }
            conn.close();
          });
        });
      }
      getEMP();

The getEMP function returns all of the admins from the database as expected. This is why I am positive the connection is working. This function was used for testing connection.

Error

UnhandledPromiseRejectionWarning: ConnectionError: Connection is closed.
at Request._query (///nodeconSQL/node_modules/mssql/lib/base/request.js:447:37)
at Request._query (///nodeconSQL/node_modules/mssql/lib/tedious/request.js:346:11)
at shared.Promise (///nodeconSQL/node_modules/mssql/lib/base/request.js:413:12)
at new Promise ()
at Request.query (///nodeconSQL/node_modules/mssql/lib/base/request.js:412:12)
at /home/devops-01/nodeconSQL/app.js:43:13
at Layer.handle [as handle_request] (///nodeconSQL/node_modules/express/lib/router/layer.js:95:5)
at next (///nodeconSQL/node_modules/express/lib/router/route.js:137:13)
at Route.dispatch (///nodeconSQL/node_modules/express/lib/router/route.js:112:3)
at Layer.handle [as handle_request] (///nodeconSQL/node_modules/express/lib/router/layer.js:95:5)

Answer 1

Your function getEMP() uses the callback from conn.connect() in order to wait until the connection is established before trying to execute the query.

The function that tries to login executes the query immediately after attempting to open the connection, however since the connection takes some time to be established, this is why you get the error that your connection is not open.

Put your login query inside the conn.connect(function(err){ /* login code */ }) construct like it is in your getEMP() function. You will then need to make sure that you can access the request and response objects in the callback function, for example by using .bind() on your callback function to put the request and response objects into the this object.

Example:

...
// Inside your /auth route
// make an object with the data our callback needs, to use with .bind()
var callbackData = {"conn": conn, "request": request, "response": response};
var myCallback = function(error, results, fields) {
    if (results.length > 0) {
          this.request.session.loggedin = true;
          this.resquest.session.username = username;
          this.response.redirect('/home');
    } else {
          this.response.send('Username and/or Password not found');
    }
    this.conn.close();
    this.response.end();
}.bind(callbackData);
conn.connect();
req.query('Select * from Admin where username = ? and password = ?',
    [username, password], myCallback);
...
Rent Charter Buses Company
READ ALSO
Make bar rounded echarts

Make bar rounded echarts

Is there a way to make bar rounded on top in echarts? For ex in this example

214
Avoid re-rendering in React when switching between tabs

Avoid re-rendering in React when switching between tabs

So I have react application using material-ui to create tabs

170
mock fs.readFile - Unit test

mock fs.readFile - Unit test

I am using Jest framework for unit testing and came across a scenario to mock the fsreadFile

312
How to structure router.js file in VueJs?

How to structure router.js file in VueJs?

I want to structure my routerjs file as currently there is around 500 lines of code in it

182