How do I execute a query with variable table name in node.js MSSQL?

Question

I currently have the following function that works perfectly fine:

const sql = require('mssql');
const config = require('../../config/credentials');
const Hardware = function () { };

Hardware.prototype.create = function (body) {
    return new sql.ConnectionPool(config).connect().then(function (pool) {
        return pool.query
            `SELECT *
             FROM my_table
             WHERE hardware_guid = ${id}
    });
};

But I want "my_table" in the query to be a variable, like ${my_table}. If I do it in the same way, I get the following error:

Must declare the table variable "@param1"

Looking at the documentation of the mssql package (https://www.npmjs.com/package/mssql), specifically at the section of ConnectionPool, I can see that they declare this parameters in the following way:

const sql = require('mssql')

sql.connect(config).then(pool => {
    // Query 

    return pool.request()
    .input('input_parameter', sql.Int, value)
    .query('select * from mytable where id = @input_parameter')
}).then(result => {
    console.dir(result)
}).catch(err => {
    // ... error checks 
})

sql.on('error', err => {
    // ... error handler 
})

So I tried to do something similar, this is what I did:

var sql = require("mssql")
var config = require("./test");

var id='1'
const pool1 = new sql.ConnectionPool(config);

pool1.connect().then(pool => {

    return pool1.request() // or: new sql.Request(pool1) 
    .input('mytable', sql.NVarChar, 'nvs_central.software_governance')
    .query(`SELECT *
             FROM @mytable
             WHERE software_guid = ${id}`)
}).then(result => {
    console.dir(result)
}).catch(err => {
    console.dir(err)
});

pool1.on('error', err => {
    console.dir(err)
});

But I still get the "Must declare the table variable "@mytable" error. Note that if I replace "@mytable" in that last piece of code for "my_table_name" (so I put the actual name of the table instead of a variable/parameter) it retrieves the expected data perfectly fine.

So what do I need to do?

Thanks!

you can't use parameters to specify a table name in SQL Server (or most other DBMSs). This isn't specific to nodeJS. The only way to vary it is through string concatenation of the variable into your SQL. But be very careful about SQL injection when you do so - if the table names are not hard-coded into your application code, it's advisable to check them against a whitelist of some sort in order to verify that the variable does not contain malicious SQL. — ADyson
– ADyson, Commented Sep 6, 2017 at 21:36
Thanks ADyson, doing the string concatenation instead of using parameters or string templates worked perfectly fine! If you make an answer I'll accept it as solution. — M. Lautaro
– M. Lautaro, Commented Sep 7, 2017 at 12:27

JCH2k · Accepted Answer · 2018-02-27 17:33:51Z

3

I inserted the table name the 'oldschool' way:

const queryString = "SELECT * FROM DocuManager." + tableName + " WHERE id = @id";

const result = await pool.request()
    .input('id', sql.Int, id)
    .query(queryString);

That works fine.

answered Feb 27, 2018 at 17:33

JCH2k

3,6901 gold badge36 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Campbeln · Accepted Answer · 2019-07-09 08:59:26Z

3

You can still use the .inputs to protect you against SQL injections; you just need to pump them through EXEC() in a stringified form:

const result = await pool.request()
    .input("TableName", $ddi.app.services.sql.lib.VarChar(50), oData.TableName)
    .input("TableColumn", $ddi.app.services.sql.lib.VarChar(50), oData.TableColumn)
    .input("ID", $ddi.app.services.sql.lib.VarChar(50), oData.ID)
    .query(`
        EXEC('SELECT ' + @TableColumn + ' FROM ' + @TableName + ' WHERE ' + @TableColumn + ' = ' + @ID + ';');
    `)
;

answered Jul 9, 2019 at 8:59

Campbeln

3,0103 gold badges35 silver badges34 bronze badges

Comments

ttallierchio · Accepted Answer · 2017-09-06 21:39:14Z

2

in sql server @table_name means it is a temporary table variable. you are essentially telling it that you have declared a variable temporary table and you want to select from that. so it attempts to look for it in tempdb and when it doesn't find it, it throws a syntax error.

you will need to switch it back to the "working" example way if you want it to work as using @table in the from clause makes sql server assume you are using a variable temp table.

answered Sep 6, 2017 at 21:39

ttallierchio

4607 silver badges17 bronze badges

Collectives™ on Stack Overflow

How do I execute a query with variable table name in node.js MSSQL?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related