What is pros and cons for these approaches SQL Server tedious queries

Question

What are the pros in cons between these two queries one without input parameters and the other one with input parameters.

Method #1:

 const pool = await poolPromise;
 const request = await pool.request()
 let CustomQuery = `INSERT INTO TableName (LastName, FirstName) 
                    VALUES ('${body.LastName}', '${body.FirstName}'`;

 const result = await request.query(CustomQuery);

Method #2: using input parameters

 const pool = await poolPromise;
 const request = await pool.request()
      .input('LastName', TYPES.VarChar, body.LastName)
      .input('FirstName', TYPES.VarChar, body.FirstName)

 let CustomQuery = `INSERT INTO TableName (LastName, FirstName) 
                    VALUES (@LastName, @FirstName)`
 const result = await request.query(CustomQuery);

Which is safer?

The parameterized version is probably the way to go, as it might avoid SQL iniection. — Tim Biegeleisen, Oct 27 '19 at 03:13
Yes but in documentations they say when using template string its safe and the .query escapes sql injections. — MJ X, Oct 27 '19 at 03:15
*Which is more safer?* The one that is not vulnerable to SQL injection. That would be the one that does not use string concatenation. This has been discussed here dozens of times before. A useful search phrase would be *parameterized queries*, *SQL injection*, or *Little Bobby Tables*. Try any of those in your favorite search engine. — Ken White, Oct 27 '19 at 03:17
If you're going to argue that *according to documentation both of them are safe*, then you've clearly made up your mind that both of them are safe. Two people have already told you that one of them is safer than the other, and you're arguing with them about it. If you don't want to hear the answer, why are you asking the question? — Ken White, Oct 27 '19 at 03:32
Possible duplicate of [What is SQL injection?](https://stackoverflow.com/questions/601300/what-is-sql-injection) — Michał Turczyn, Oct 27 '19 at 09:18
This is **not** opinion based at all. There are probably other reasons to close this question (most likely a duplicate) but it is absolutely not a matter of opinion here. — Zohar Peled, Oct 27 '19 at 12:22

Edin Omeragic · Answer 1 · 2019-10-27T12:15:26.057

The second version is safer and query string is constant so SQL server needs to parse and verify query fewer times, also parameter types are explicitly set.

In following test:

const TDS = require("tedious")

const body = {
    LastName: "A",
    FirstName: "B"
}

var request = new TDS.Request(
    `INSERT INTO TableName (LastName, FirstName) 
    VALUES ('${body.LastName}', '${body.FirstName}')`);

let parmRequest = new TDS.Request(
    `INSERT INTO TableName (LastName, FirstName) 
    VALUES (@LastName, @FirstName)`);

parmRequest.addParameter('LastName', TDS.TYPES.VarChar, body.LastName)
parmRequest.addParameter('FirstName', TDS.TYPES.VarChar, body.FirstName)

console.log(request);
console.log(parmRequest);

First query is just interpolated string and does not contain any parameters, so SQL server will see this query as new query every time it needs to be executed and it needs to be parsed and verified.

score 2 · Accepted Answer · answered Oct 27 '19 at 09:16

2

Second is dfinietely safer, as in first approach you just use string interpolation, which can be thought of as simple string concatenation.

Second won't allow malicious input, therefore prevent SQL injection.

See SQL injection.

answered Oct 27 '19 at 09:16

Michał Turczyn

28,428
14
36
58

What is pros and cons for these approaches SQL Server tedious queries

2 Answers2