Node MSSQL Dynamic Query

Question

I'm currently building a dynamic query using the below bit of code:

 var conditions = `AND 1 = 1`;
    if (params.AssignedUserId) {
        conditions += `AND x.AssignedUserId = ${params.AssignedUserId}`;
    }
    return dbc.then(pool => {
        return pool.request()
            .input('ClientId', sql.Int, ClientId)
            .query(`SELECT *     
                    FROM  ${db.Enquiry}.Customers x 
                    WHERE x.ClientId = @ClientId
                    ${conditions}`)
    }).then(result => {
        return result.recordset;
    }).catch(err => {
        console.error(err);
    });

This works fine however the major problem is that the params.AssignedUserId is not sanitized, is there a way to also add the value as an input parameter instead?

Something so i could re-write it as this:

    if (params.AssignedUserId) {
        conditions += `AND x.AssignedUserId = @AssignedUserId`;

        // Something to add as input param to query

       .input('AssignedUserId', sql.Int, AssignedUserId)

    }

This would then be protected against SQL injection

The alternative would be if i used a third partly library called node-sanitize to sanitize each parameter, but seems like overkill especially since node-mssql has the capability to do it

Answer 1

How about:

return dbc.then(pool => {
  return pool.request()
    .input('ClientId', sql.Int, ClientId)
    .input('AssignedUserid', sql.Int, params.AssignedUserid)
    .query(`SELECT *     
                FROM  ${db.Enquiry}.Customers x 
                WHERE x.ClientId = @ClientId AND
                      COALESCE(@AssignedUserId, x.AssigneduserId) = x.AssignedUserId`)
}).then(result => {
  return result.recordset;
}).catch(err => {
  console.error(err);
});