CODEWITHSUNDEEP
sqlsql-servernode.js
M. Lautaro
M. Lautaro

Reputation: 97

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

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!

Upvotes: 6

Views: 4652

Answers (3)

Campbeln
Campbeln

Reputation: 2990

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 + ';');
    `)
;

Upvotes: 3

JCH2k
JCH2k

Reputation: 3621

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.

Upvotes: 3

ttallierchio
ttallierchio

Reputation: 460

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.

Upvotes: 2

Related Questions