jluboff / connect-mssql-v2

MS SQL Server session store for Express Session

connect-mssql-v2

SQL Server session store for Connect/Express based on node-mssql and the deprecated/abandoned project connect-mssql.

Installation

npm install connect-mssql-v2

Prerequisites

Before you can use session store, you must create a table. Recommended table name is sessions but you can change it via options. The database user must have db_datareader, db_datawriter, and db_ddladmin permissions.

CREATE TABLE [dbo].[sessions](
    [sid] [nvarchar](255) NOT NULL PRIMARY KEY,
    [session] [nvarchar](max) NOT NULL,
    [expires] [datetime] NOT NULL
)

Usage

Javascript

const MSSQLStore = require('connect-mssql-v2');

const config = {
  user: '...',
  password: '...',
  server: 'localhost', // You can use 'localhost\\instance' to connect to named instance
  database: '...',
  options: {
    encrypt: true, // Use this if you're on Windows Azure
    trustServerCertificate: true, // use this if your MS SQL instance uses a self signed certificate
  },
};

app.use(
  session({
    store: new MSSQLStore(config, options), // options are optional
    secret: 'supersecret',
  }),
);

Typescript

import MSSQLStore from 'connect-mssql-v2';

const config = {
  user: '...',
  password: '...',
  server: 'localhost', // You can use 'localhost\\instance' to connect to named instance
  database: '...',
  options: {
    encrypt: true, // Use this if you're on Windows Azure
    trustServerCertificate: true, // use this if your MS SQL instance uses a self signed certificate
  },
};

app.use(
  session({
    store: new MSSQLStore(config, options), // options are optional
    secret: 'supersecret',
  }),
);

Options

• options.table - Table to use as session store. Default: [sessions]
• options.ttl - (Time To Live) Determines the expiration date. Default: 1000 * 60 * 60 * 24 (24 hours)
• options.autoRemove - Determines if expired sessions should be autoremoved or not. If value is true then a new function, destroyExpired(), will autodelete expired sessions on a set interval. Default: false
• options.autoRemoveInterval - Sets the timer interval for each call to destroyExpired(). Default: 1000 * 60 * 10 (10 min)
• options.autoRemoveCallback - Is the callback function for destroyExpired(). Default: undefined
• options.useUTC - Determines if we are to use the GETUTCDATE instead of GETDATE Default: true

Advanced usage

const store = new MSSQLStore(config, options);

store.on('connect', () => {
	// ... connection established
});

store.on('error', (error) => {
	// ... connection error
});

store.on('sessionError', (error, classMethod) => {
  // ... any error that occurs within a store method
  // classMethod will return the method name (get, set, length, etc)
})
app.use(session({
    store: store
    secret: 'supersecret'
}));

Configuration

To see all options please visit node-mssql docs.

Upgrading from v4.x.x to v5.x.x

Ensure you're running Node >=v15

Upgrading from v3.x.x to v4.x.x

Ensure you're running Node >=v13

Upgrading from v2.x.x to v3.x.x

The key step to upgrading is to include

trustServerCertificate: true;

in your options object for the store config (see either javascript or typescript example) if running a local instance of MS SQL with a self signed certificate. If you do not provide this, you will get a connection error

ConnectionError: Failed to connect to databaseserver:1433 - self signed certificate

Upgrading from v1.x.x to v2.x.x

It is no longer required to pass in the express-session store. Please see the Usage section on the updated import/require method.

Contributions

Contributions are welcome, please submit a PR which will be reviewed.

Reporting Issues

Please report issues/errors to Github's issue tracker: connect-mssql-v2 issue tracker. Include issue, expected behavior, and how to replicate the issue.

License

MIT License

connect-mssql-v2's Issues

Add support for gitpod development?

So one of the slightly annoying aspects of testing out this plugin is the fact you need an mssql installation locally and some "hooking it up" logic. One way to get around this is to use something like gitpod which is free for open source, to get a ready to go dev environment that could also be setup with the mssql installation.

This would allow anyone to try out this plugin without much work. Might be worth looking into so its easier to mess around with the plugin, especially if we are to continue updating it :)

Add in "advanced usage" emitters

Add in event emitters for connect and error events.

[BUG] mssql responed recordset null but rowseffected count is there

I am using "connect-mssql-v2": "^3.1.3" and "mssql": "^7.3.5". While mssql request execute i am getting response with null recordset even though there is rowseffected count.
Below is the code i am using.

const session = require("express-session");
const MSSQLStore = require("connect-mssql-v2");
const sql = require("mssql");

var sqlConfig = {
  user: config.sqlExtDbUserName,
  password: config.sqlExtDbPassword,
  server: config.sqlExtDbServerName,
  database: config.sqlExtDbName,
  port: config.sqlExtDbServerport,
  options: {
    encrypt: true,
    trustServerCertificate: true,
  },
};

var option = {
  ttl: 100060608, //set session expiry as 8 hours
  autoRemove: "interval",
  autoRemoveInterval: 100060 * 60, //delete expired session after 1 hour
};

app.use(
  session({
    name: "session",
    store: new MSSQLStore(sqlConfig, option),
    secret: config.sessionKey,
    resave: true,
    saveUninitialized: false,
    cookie: { secure: true, sameSite: true },
  })
);

request = new sql.Request(connection);
if (parameterList) {
  for (const param of parameterList) {
    request.input(
      param.parameterName,
      param.parameterType,
      param.parameterValue
    );
  }
}

result = await request.execute(procedureName);

Is it possible to use it with msnodesqlv8 and Windows Authentication? (Trusted Connection)

How can i use Windows Authentication. (Trusted Connection)?

Add test for autoremove callback

As stated in the README the option: autoRemoveCallback is not tested.

sessionError listener duplicates

Each time the sessionError listener emits, an additional listener is added. Actual behavior should only ever be one listener.

Add pre-expiration callback for auditing and custom logic in connect-mssql-v2

Is your feature request related to a problem? Please describe.
Yes, the problem is related to the lack of a callback or hook that is invoked immediately before a session expires and is deleted from the database. Currently, the connect-mssql-v2 library provides the autoRemoveCallback option, which is triggered after the expired sessions are deleted. However, there is no way to execute custom logic or perform necessary actions, such as auditing, right before the session expiration process begins.

Describe the solution you'd like
The desired solution is to add support for a callback function that is invoked just before the session expiration process starts. This callback should be configurable through a new option, such as beforeExpireCallback, which accepts a function.

The destroyExpired method should be modified to execute the beforeExpireCallback (if provided) before running the delete query on the sessions table. The callback should receive relevant information, such as the session ID or session data, to allow for custom logic based on the expiring session.

Describe alternatives you've considered
Another alternative is to use a separate cron job or background process to query the sessions table, identify expiring sessions, and perform custom actions before deleting them. However, this approach can be complex to set up and maintain, and it may introduce race conditions if the session is modified or extended after being identified as expired.

Additional context
In my application, I implement auditing functionality to track important user actions, such as sign-ins and sign-outs, by writing relevant information to an audit table. However, when a session expires automatically due to inactivity or reaching the expiration time, this library directly deletes the session data from the database without providing any hooks or callbacks that allow me to perform auditing or other necessary actions before or after the session expiration.

Fails Node audit (HIGH vulnerability)

Describe the bug
This package isn't secure, it depends on an old version of mssql which has a bunch of jsonwebtoken issues:

• jsonwebtoken's insecure implementation of key retrieval function could lead to Forgeable Public/Private Tokens from RSA to HMAC - GHSA-hjrf-2m68-5959
• jsonwebtoken vulnerable to signature validation bypass due to insecure default algorithm in jwt.verify() - GHSA-qwph-4952-7xr6
• jsonwebtoken unrestricted key type could lead to legacy keys usage - GHSA-8cf7-32gw-wr33
• jsonwebtoken has insecure input validation in jwt.verify function - GHSA-27h2-hvpr-p74q

To Reproduce

• Run npm install connect-mssql-v2
• Run npm audit
• Error is there

Expected behavior
Package should be updated to use mssql ^7.2.0

Screenshots
If applicable, add screenshots to help explain your problem.

Desktop (please complete the following information):

• OS: Windows
• Node Version: >=14
• Package Version: 3.1.4

Additional context
This is the only working session manager package for Microsoft SQL Server/TSQL and Node, and the vulnerabilities are significant. This could cause a lot of sites using MSSQL to become insecure as a result.

UnhandledPromiseRejection -- MSSQLStore.ready

While idle, experienced an UnhandledPromiseRejectionWarning: TypeError: callback is not a function.
Calling function - MSSQLStore.ready (private method)

[chore] Clean up repo tooling

I found a few things we can improve on the tooling/development side if we are to put this repo into a more "maintenance" mode moving forward.

• Add .nvmrc and update docs to specify which version of nodejs we should develop on and are tested against.
• Add github-action pipeline to run some sanity checks against the repo
• Update package.json with rimraf rather than windows specific rimdir, will allow for linux based development, even though this library generally is used on windows.
• Add clean and build commands for developer accessibility
• Add contributing docs
• Add typedoc based github page deployment. Will help with documenting the current codebase, rather than the current README based setup
• Add .vscode specific files, primarily for extensions we should install (gitpod might already have some)

Type Definitions

Need to straighten out type definitions. Currently, Store accepts an argument of session with type any and returns with type any. In turn, does not properly supply types.

MSSQLStore is not a constructor error

When implementing the example from the usage section in the Readme. I get the following error in the command line:

const store = new MSSQLStore(config, options)
TypeError: MSSQLStore is not a constructor

require(...) is not a function

I am getting this error after declaring like so:
const express = require('express'); const session = require('express-session'); const MSSQLStore = require('connect-mssql-v2')(session); // here

And using like so:
app.use(session({ store: new MSSQLStore(config), secret: 'XXXXX', resave: false, saveUninitialized: true }));

And am getting the error: TypeError: require(...) is not a function on the line marked "here."
Am I doing something wrong?

Update README with badges

We could make the README more flashy with some badges. The following badges would be nice to have:

• license
• download information

also open to suggestions of any other ones too

TypeError: MSSQLStore is not a constructor

Following the usage verbatim and I am receiving this error.

Move dependencies to peer dependencies?

I was recently looking at the package.json and realized this package has 2 dependencies:

"@types/mssql": "^6.0.0",
"mssql": "^6.1.0"

I looked into this to make sure this is the right way to do things, and ran into this SO post where it seems like using peerDependencies would be better (at least for the type def).

Looking further I looked into one of the more popular express-session stores, connect-session-sequelize's package.json and found the "sequalize dependency" under peerDependencies.

As such it might make more sense to move these under peerDependencies instead of dependencies, as this package falls under the "plugins" type I keep hearing mentioned for use cases for peerDependencies.

Further reading:

• differences between the different dependencies in package.json from a SO post
• nodejs docs on peer dependencies

Update README with information on reporting issues and errors

The README lacks any mention of where to report errors (this repo). I think its kinda obvious but it shouldn't hurt to mention it.

Update eslint so it runs/works?

I know there is only 1 file to lint, but this project also includes some of what is needed to lint it (eslint+some plugins). We can setup linting to lint that single file (and potentially any other future files if need be)

As of right now the eslint configuration makes a command like npx eslint src not see any files due to the files being .ts. This can be fixed (along with any other lint errors) and will help us later if we ever add ci/cd support

Add documentation/comments for all configurable properties

Most of the configuration options in the main store file can be documented in the code, in-line with the README documentation.

TypeError: require(...) is not a function

Hi, first at all, i'm super new in express (also in javascript).

I'm trying to use this functionality, but I try everything and cant do it work. (everything I know :) )

I started from scratch to avoid problem with other components.

I just load dotenv, mssql, express, express-session and connect-mssql-v2

const express = require("express");
require("dotenv").config();
const session = require("express-session");
const MSSQLStore = require("connect-mssql-v2")(session); // in "session" word node says "TypeError: require(...) is not a function"
const sql = require("mssql");

const app = express();

var db_enableArithAbort;

if (process.env.db_enableArithAbort == "true") {
  db_enableArithAbort = true;
} else {
  db_enableArithAbort = false;
}

const config = {
  user: process.env.db_user,
  password: encodeURIComponent(process.env.db_password),
  server: process.env.db_host,
  port: Number(process.env.db_port),
  database: process.env.db_database,
  encrypt: process.env.db_encrypt,
  options: {
    appName: process.env.db_appname,
    enableArithAbort: db_enableArithAbort,
  },
};
/*
const poolPromise = new sql.ConnectionPool(config)
  .connect()
  .then(pool => {
    console.log('Connected to MSSQL -> '+config.server+':'+config.port+'/'+config.database)
    return pool
  })
  .catch(err => console.log('Database Connection Failed! Bad Config: ', err))
*/

app.use(
  session({
    store: new MSSQLStore(config), // options are optional
    secret: "supersecret",
  })
);

/* START SERVER */
app.set("port", process.env.WEB_PORT || 8001);
var server = app.listen(app.get("port"), function () {
  console.log("Sever Running. Port " + server.address().port);
});

If I comment the lines associated to connect-mssql I was able to run a simple query from my poolpromise with a request and query... that part works fine. That is I know SQL config is OK.

Can you help me?

I also have some doubt about using... on a real project, I want to store some value per user/session and be able to recover this value in different situations or when I using some routes... can you add some example "for dummies"?

Sorry my english is bad.

Throws initial ConnectionError --- Connects but does not persist

When using in an actual project, initially get a Connection is closed error, but looking at my own logs, can see it is connected, however, the connection is not staying persistent. Or connection is persistent but the on("connect") emitter is firing when it shouldn't (should only emit on initial connection).

Add issue templates

We can add issue templates by following these docs:
https://docs.github.com/en/communities/using-templates-to-encourage-useful-issues-and-pull-requests/configuring-issue-templates-for-your-repository

Having the following templates would be useful to cover the most common cases:

1. Report Bug
2. Request Enhancement/Feature

Add ts/es-lint, prettier on-commit hooks

This will automatically enforce styling and linting guidelines on commit. See here for an example project: https://github.com/cmccormack/minesweeper

Update LICENSE, as it still points to the wrong person