Building a kitchen sink query. Error passing @ parameters to SQL Server-CodePudding

Im building a kitchen sink query on NODE, where several parameters, for about 20 tables are being passed. They all form a single SQL Query:

if (data.basics.memberId) { sqlString  = ` memberid=@memberId and` };

etc...

Im passing several parameters (about 40), but Im not able to pass the parameters when sending the query to the database:

const pool = await utils.poolPromise
const recordset = await pool.request()
//basics
if (data.basics.memberId) {
    .input('memberId', utils.sql.Int, data.basics.memberId)
};        
.query(sqlString)
if (recordset.rowsAffected[0] > 0) {
...
...
...
}

and Im getting the error:

Declaration or statement expected.

.input('memberId', utils.sql.Int, data.basics.memberId)

and

.query(sqlString)

I've read parameters are not the right way to build dynamic queries, so I thought of using ES6, as

if (data.basics.memberId) { sqlString  = ` memberid=${data.basics.memberId} and` };

But I've read that ES6 does not prevent SQL injection on SQL Server.

The question is how do I approach this scenario where the SQL string is variable and changes based on the user selection?

Thanks.

CodePudding user response：

Without a query builder library (e.g. Knex), you'll need to

form the SQL query (as a string)
put the parameters into place

e.g. something like this:

const whereClauses = [];
const inputs = {}; // map input name -> [type, value]

// (1) Process data into WHERE clauses and inputs

if (data.basics.memberId) {
  whereClauses.push(`memberid=@memberId`);
  inputs.memberId = [utils.sql.Int, data.basics.memberId];
}

if (data.basics.somethingElse) {
  whereClauses.push(`somethingElse=@somethingElse`);
  inputs.somethingElse = [utils.sql.Int, data.basics.somethingElse];
}

// (etc..., you could use a loop or something for the above)

// (2) Form the final SQL query

const sqlStringBits = ["SELECT * FROM ... WHERE "];

for (let whereClause of whereClauses) {
  sqlStringBits.push(whereClause);
  sqlStringBits.push("AND");
}
if (whereClauses.length) {
  sqlStringBits.pop(); // Remove final AND if we had added one
}

const sqlString = sqlStringBits.join(" ");

// (3) Form the `request` and put the inputs into place

const pool = await utils.poolPromise;
let request = pool.request();
for (let inputName in inputs) {
  request = request.input(inputName, ...inputs[inputName]);
}

// (4) Use the request (endowed with inputs) with the query
const recordSet = await request.query(sqlString);

// (5) Do something with the record set!