req.app.locals.pool.input is not a function

[8483]

I'm trying to follow the documentation for node mssql on creating pools.

I get this error...

TypeError: req.app.locals.pool.input is not a function

Expected behaviour:

Create a gobal pool and add inputs to queries.

Actual behaviour:

Here's the code:

server.js

const express = require("express");
const app = express();

const sql = require("mssql");
const { db } = require("./config.js");
const poolConnection = new sql.ConnectionPool(db);

const PORT = process.env.PORT || 9010;

//connect the pool and start the web server when done
poolConnection
    .connect()
    .then((pool) => {
        app.locals.pool = pool; //    <------- HERE

        const server = require("http").createServer(app);

        server.listen(PORT, () => {
            console.log(`listening on port: ${PORT}`);
        });
    })
    .catch((err) => {
        console.error("Error creating connection pool", err);
    });

route.js

router.get("/api/users/:userId", async (req, res, next) => {
    try {
        let userId = req.params.userId;

        req.app.locals.pool.input("userId", sql.Int, userId); //    <------- HERE

        let query = `
            select *
            from bi_user u
            where u.id = @userId    
            ;
        `;

        let result = await req.app.locals.pool.query(query);

        let final = result.recordset[0];

        res.send(final);
    } catch (err) {
        next(err);
    }
});

Configuration:

db: {
        database: process.env.DB_NAME,
        server: process.env.DB_SERVER,
        port: +process.env.DB_PORT,
        user: process.env.DB_USER,
        password: process.env.DB_PASSWORD,
        connectionTimeout: 60000, // default 15 sec
        requestTimeout: 180000,
        trustServerCertificate: true,
        options: {
            encrypt: false, // need to stop ssl checking in case of local db
            enableArithAbort: true,
        },
    },

Software versions

• NodeJS: v18.12.1
• node-mssql: 9.1.1
• SQL Server: Microsoft SQL Server 2017 (RTM-CU16) (KB4508218) - 14.0.3223.3 (X64) Jul 12 2019 17:43:08 Copyright (C) 2017 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Datacenter 10.0 (Build 14393: ) (Hypervisor)

[dhensby]

That's because ConnectionPools don't have .input() method. You need to create a request and use that:

router.get("/api/users/:userId", async (req, res, next) => {
    try {
        let userId = req.params.userId;
+        const dbReq = req.app.locals.pool.request();

-        req.app.locals.pool.input("userId", sql.Int, userId); //    <------- HERE
+        dbReq.input("userId", sql.Int, userId); //    <------- HERE

        let query = `
            select *
            from bi_user u
            where u.id = @userId    
            ;
        `;

+        let result = await dbReq.query(query);

        let final = result.recordset[0];

        res.send(final);
    } catch (err) {
        next(err);
    }
});

But you can do this all in one step using tagged template literals:

router.get("/api/users/:userId", async (req, res, next) => {
    try {
        let userId = req.params.userId;

-        let query = `
-            select *
-            from bi_user u
-            where u.id = @userId    
-            ;
-        `;
-
+        let result = await req.app.locals.pool.query`SELECT * FROM [bi_user] AS [u] WHERE [u].[id] = ${userId};`;

        let final = result.recordset[0];

        res.send(final);
    } catch (err) {
        next(err);
    }
});

[8483]

Ahhhh, I see. Thank you so much for the reply.

A quick question... Isn't the template literal vulnerable to SQL injection?

[dhensby]

A quick question... Isn't the template literal vulnerable to SQL injection?

No. You can read about tagged template literals. But as long as you pass the template to the query method without braces then the library can perform the parameterisation of the query auto-magically.

You can see how it works here.

So this is not vulnerable:

const result = await req.app.locals.pool.query`SELECT * FROM [bi_user] AS [u] WHERE [u].[id] = ${userId};`;

This is vulnerable:

const result = await req.app.locals.pool.query(`SELECT * FROM [bi_user] AS [u] WHERE [u].[id] = ${userId};`);

[8483]

I did not know this also. Thank you again for taking the time to help me out. God bless you!