Node-mssql: IN clause array parameter

[ghalleb-pj]

Hi,

Is it possible to give a array as a parameter. for example in a IN clause?

For example:

var query = "SELECT * FROM myTable WHERE code IN (@datas)"

var params = [
    {
        name: 'datas',
        type: sql.VarChar,
        value: ['095578','095602']
    }
];


async.waterfall([
    function (callback) {
        createConnection(callback);
    },
    function (connection, callback) {
        var request = connection.request();

        params.forEach(function (param) {
            request.input(param.name, param.type, param.value);
        });

    request.query(query, function (err, recordset) {
    if (err) {

        callback(err, null);
    }
    else {
        callback(null, recordset);
    }
    });
    }
], function (err, recordset) {
    if (err) {
        next(err, null);
    }
    else {
        next(null, recordset);
    }
});

[patriksimek]

No, unfortunately it is not. You can consider using TVP instead.

[ghalleb-pj]

Thanks for the quick response.

Are you planning to develop that functionnality? It would be very helpfull.

TVP seems a little bit overkill and I don't have read access on the database I'm querying.

[patriksimek]

I'm not planning to implement this, because it is not even possible with SQL Server. If you only need to pass simple values (like numbers), you can concatenate those values to string and then use a server-side split function.

More info: http://stackoverflow.com/questions/697519/split-function-equivalent-in-t-sql

[Aides359]

@patriksimek what exactly is not possible with SQL Server?

According to https://msdn.microsoft.com/en-gb/library/ms177682.aspx an IN clause is absolutely legitimate with constant values.

Also when testing with SQL Server 2014 an IN clause works perfectly fine (even tried to manually concatenate the appropriate SQL on the query string of node-mssql and it worked perfectly fine).

I think it would be absolutely reasonable to have input compile the array into the corresponding SQL parameter like this

```var query = "SELECT * FROM myTable WHERE code IN (@data)";
request.input('data', mssql.VarChar, ['foo', 'bar']);
/// SELECT * FROM myTable WHERE code IN ('foo', 'bar')

var query = "SELECT * FROM myTable WHERE code IN (@data)";
request.input('data', mssql.Int, [22, 23]);
/// SELECT * FROM myTable WHERE code IN (22, 23)
`` to gain all the benefits fromrequest.input()` for IN statements.

[patriksimek]

@Aides359 It is not possible to pass an array as a parameter. What I really like about the current behavior of the library is that it's not modifying the user's command at all. What you propose breakes that and honestly I don't like it. I would have to parse the command and analyze it to find out whether the variable is inside an IN block or not. That would require a huge amount of effort with a small added value.

[goforgold]

For now, I've created a function which adds parameters dynamically like below. It is SQL Injection safe and can work for any type (no need to worry about quotes) as I've taken type as an argument.

Function:

/**
 * @param request sql request object
 * @param {string} columnName sql table column name
 * @param {string} paramNamePrefix prefix for parameter name
 * @param type parameter type
 * @param {Array<string>} values an array of values
 */
function parameteriseQueryForIn(request, columnName, parameterNamePrefix, type, values) {
  var parameterNames = [];
  for (var i = 0; i < values.length; i++) {
    var parameterName = parameterNamePrefix + i;
    request.input(parameterName, type, values[i]);
    parameterNames.push(`@${parameterName}`);
  }
  return `${columnName} IN (${parameterNames.join(',')})`
}

Usage:

var query = 'SELECT * FROM TableName WHERE ' + parameteriseQueryForIn(queryRequest, 'Name', 'nameParam', sql.NVarChar, ['Name1', 'Name2'])

I still wish if something like below should support, so programmers don't have to all this and also don't have to worry about sanitization to prevent injection.

var query = "SELECT * FROM myTable WHERE code IN (@datas)"

var param =  {
    name: 'datas',
    type: sql.VarChar,
    value: ['095578','095602']
};

request.input(param.name, param.type, param.value);

Thanks,

[trademark18]

@goforgold Since it appears that this won't be added to the tediousjs package, have you considered making this function its own NPM package?

[TeamDman]

I've tried a similar approach, but quickly hit the MSSQL limit of 2k variables in a single prepared statement.