How to put a dynamic variable value into double quotes in T-SQL

Question

I have the following line var examNumber = testExamData.examDetails.examNumber; and its value is ABX0916

var examNumber = testExamData.examDetails.examNumber;
con = new SqlConnection(connectionString);
con.Open();
cmd = new SqlCommand("UPDATE [dbo].[Exams_Data] SET [Exam_Date] = DATEADD(DAY, +90, GETDATE())" +
"WHERE [Status_ID] = 2 AND [Exam_Number] = " + @"" + examNumber + @"", con);
dr = cmd.ExecuteReader();

In the above code, var examNumber is ABX0916. Then in cmd, the value for commandText is coming as below:

"UPDATE [dbo].[Exam_Data] SET [Exam_Date] = DATEADD(DAY, +90, GETDATE())WHERE [Status_ID] = 2 AND [Exam_Number] = ABX0916".

Since ABX0916 is a string, i want it to be in double quotes in the SQL to be executed.

Below is the command i want to be execute (with ABX0916 in double quotes)

UPDATE [dbo].[Exam_Data] SET [Exam_Date] = DATEADD(DAY, +90, GETDATE())WHERE [Status_ID] = 2 AND [Exam_Number] = "ABX0916"

How do i correctly escape this @"" + examNumber + @"" to achieve that?

`How to` you don't. That's how SQL injection attacks occur. What if `examNumber` contained `0; drop table Examx_Data;--` ? Use parameters. It's actually a *lot* easier to use a parameterized query, either with ADO.NET alone, or a library like Dapper, eg `con.Execute("update Exams_Data Set Exam_Date=@date where Status_Id=@Id and Exam_Number=@number",new {date=DateTime.Today.AddDays(90),id=2,number=someNumber);` — Panagiotis Kanavos, May 28 '21 at 07:15
As Mitch said you should really use parameters. However if you want to go the way you are you actually want single quotes rather than double quotes for the string in SQL server. @"'" — Bee_Riii, May 28 '21 at 07:16
@Bee_Riii in that case, `'; drop table Exam_Data;--`. There's even [a comic about this](https://xkcd.com/327/) — Panagiotis Kanavos, May 28 '21 at 07:21
cmd = new SqlCommand("UPDATE [dbo].[Exams_Data] SET [Exam_Date] = DATEADD(DAY, +90, GETDATE())" + "WHERE [Status_ID] = 2 AND [Exam_Number] = " + "'" + examNumber + "'", con); — Gudwlk, May 28 '21 at 13:35

How to put a dynamic variable value into double quotes in T-SQL

0 Answers0