How to use parameters in SQL Server?-CodePudding

Beginner here! I just started working with SQL Server in Azure functions (in Typescript). I am trying to update a row in the database using declared variables but I am failing to do so with VARCHAR types. Interesting is that in the database tool it is working fine but once I try to run the query inside the Azure function, I get an http me 500 error

RequestError: Incorrect syntax near 'kg'

(working with weight as a string).

At the moment, my code looks like this:

const trainingId: number = parseInt(req.params.training_id);
const exerciseId: number = parseInt(req.params.exercise_id);
const weight: string = req.body.weight;
await mssql.query(
`DECLARE @sql NVARCHAR(4000);
DECLARE @training_id INT;
DECLARE @exercise_id INT;
DECLARE @weight VARCHAR(255);
set @sql = N'
UPDATE Exercises SET weight = @weight WHERE training_id = @training_id AND exercise_id = @exercise_id'
SET @training_id = ${trainingId};
SET @exercise_id = ${exerciseId};
SET @weight = ${weight};
exec sp_executesql @sql, N'@training_id INT, @exercise_id INT, @weight VARCHAR(255)',
 @training_id, @exercise_id, @weight`

I have also tried the syntax where I insert the weight variable into the query like this:

 SET weight '   @weight   '

and did not work either.

I have also tried to use this approach here: How to pass parameter to mssql query in node js But it did not work for me either. So how can I actually pass the parameters correctly? I am trying to avoid SQL injection.

Thank you so much for any advice!

CodePudding user response：

With SET @weight = ${weight}; you are injecting values into a query that you use to execute another query. Why not use that UPDATE query directly after you use mssql.input(...) to set the parameter values (as per your link)?

As I understand it, it should be something like:

const trainingId: number = parseInt(req.params.training_id);
const exerciseId: number = parseInt(req.params.exercise_id);
const weight: string = req.body.weight;

// set up parameters
mssql.input('training_id', Sql.Int, trainingId);
mssql.input('exercise_id', Sql.Int, exerciseId);
mssql.input('weight ', Sql.VarChar, weight);

// execute just the UPDATE query, using the parameters
await mssql.query('UPDATE Exercises SET weight = @weight WHERE training_id = @training_id AND exercise_id = @exercise_id');

CodePudding user response：

Perfect! I was doing it wrong the whole time as I was not linking the new Request to the mssql. What works for me now is:

const request: mssql.Request = new mssql.Request();
request.input('training_id', mssql.Int, trainingId);
request.input('exercise_id', mssql.Int, exerciseId);
request.input('weight ', mssql.VarChar, weight);
const result = await request.query('UPDATE Exercises SET weight = @weight WHERE training_id = @training_id AND exercise_id = @exercise_id');

Thanks a lot for the help!