prevent escape string c#

Question

I have my sql query which displays fields using "where" from a variable. I have my variable passed from a function

string empCode which value is "!\\("

Here's my code:

public List<int> GetSuccessAndFailedCountForTodayForAgent(string empCode)
{
     var result = new List<int>();
     string query = "SELECT (SELECT COUNT(*) FROM BusinessTransactions WHERE STATUS='Failed' AND ENTEREDDATE='" + DateTime.Now.Date.ToShortDateString() + "' AND AgentEmployeeCode='" + empCode + "') AS FAILED_COUNT, (SELECT COUNT(*) FROM BusinessTransactions WHERE STATUS='Completed' AND ENTEREDDATE='" + DateTime.Now.Date.ToShortDateString() + "' AND AgentEmployeeCode='" + empCode  + "') AS SUCCESS_COUNT";
     using (SqlConnection conn = new SqlConnection(asynchPaymentDBConnectionString))
     {
          conn.Open();
          using (SqlCommand s = new SqlCommand(query, conn))
          {
               using (SqlDataReader reader = s.ExecuteReader())
               {
                    try
                    {
                         if (reader.HasRows)
                         {
                               while (reader.Read())
                               {
                                    result.Add(reader.GetInt32(0));
                                    result.Add(reader.GetInt32(1));
                               }
                         }
                   }
                   catch (Exception ex)
                   {
                         //do something
                   }
               }
          }
     }
     return result;
}

In my C# the result becomes 0 - 0 which when i try to sql server directly it display a result of 2 - 0

The string !\\( is being treated as !\(

How can I use my string !\\( to my where clause?

EDIT:

I tried using parameters adding:

s.Parameters.Add("@EmployeeCode", SqlDbType.NVarChar, 16);
s.Parameters["@EmployeeCode"].Value = empCode;

Still doesn't work

Your code is vulnerable to [SQL Injection](http://www.w3schools.com/sql/sql_injection.asp). Like @FelixPamittan suggested, you should use [parameters](https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters%28v=vs.110%29.aspx) to build your query instead of just concatenating strings. — Manfred Radlwimmer, Jun 01 '16 at 06:43
Linked as duplicate http://stackoverflow.com/questions/5468425/how-do-parameterized-queries-help-against-sql-injection question explains what is SQL injection and shows how to fix it (which will also solve your issue you are trying to hack around) — Alexei Levenkov, Jun 01 '16 at 06:45
Aside from parameterizing your code, you can add `@` in front strings to tell compiler to ignore escape chars. — raidensan, Jun 01 '16 at 06:51

score 0 · Answer 1 · edited Apr 23 '22 at 13:40

0

To escape a backslash in C# string you can either use a double backslash or prefix the entire string with @ which will treat the string literally and escape all characters.

I.e.

@"foo!\(bar"

will produce the string

foo!\(bar

You can only do this when you are assigning the string to the variable, you can't prefix a variable with @.

edited Apr 23 '22 at 13:40

Dharman

30,962
25
85
135

answered Jun 01 '16 at 06:51

Notts90

254
2
20

How can I do it when it is from a variable? I tried string id = @empCode and use id instead but doesn't work also – natsu1627 Jun 01 '16 at 06:59
You would need to use this to assign the string to the variable but you don't need to do it again when using the variable. – Notts90 Jun 01 '16 at 07:06
Also isn't empCode the string being passed in? In which case you need to put the @ in front of the string when it's assigned to empCode, wherever that is done. You can't retrospectively escape characters. – Notts90 Jun 01 '16 at 08:00

prevent escape string c#

1 Answers1