4

I have seen in my searches the use of parameterized strings in SQL queries formed as below:

SqlCommand comm = new SqlCommand();
comm.CommandText="SELECT * FROM table WHERE field LIKE '%'+@var+'%'";
comm.Parameters.AddWithValue("var","variabletext");
SqlDataReader reader = comm.ExecuteReader();

However, in this forum it was mentioned that is subject to sql injection despite it's being used in a parameterized string. I can only assume that concatenated strings bypass all parameterized security and just insert the value directly as a string. If this is the case, how does one use the wildcard operators in a parameterized query while avoiding sql code injection?

MPelletier
  • 16,256
  • 15
  • 86
  • 137
steventnorris
  • 5,656
  • 23
  • 93
  • 174

2 Answers2

3

This is not vulnerable to SQL Injection.

Whoever told you that is wrong. '%'+@var+'%' is treated as data not as executable code. It is evaluated as a string then used as the pattern on the right hand side of the LIKE.

You would only have an issue if you were then to EXEC the result of such a concatenation. Simply performing a string concatenation in the query itself is not a problem.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • -1 You should check all the exceptions about the sql injection issue. Take a look at here: http://stackoverflow.com/questions/1429866/examples-of-sql-injection-even-when-using-sqlparameter-in-net – Rikki Oct 30 '12 at 19:07
  • 1
    @MohammadGoudarzi - You're wrong. There is no SQL injection issue here. The OP is already using parameterised queries. They just happen to be concatenating something to the parameter. They are not creating an executable string through concatenation. – Martin Smith Oct 30 '12 at 19:07
  • @MohammadGoudarzi I believe Martin is right. I'm adding an edit to make my use more clear. – steventnorris Oct 30 '12 at 19:11
  • You don't know the way of executing the mentioned code in the question because it's not specified. It can be anything such as sp_executesql or exec or might be SqlCommand. So I think the best suggestion would be the safe way to execute the code. Cheers – Rikki Oct 30 '12 at 19:12
  • @KenWhite - Parameterised with either a variable or parameter. It makes no difference. Neither of them would be a problem. The original question specifically mentions `parameterized string.` though. – Martin Smith Oct 30 '12 at 19:12
  • @MohammadGoudarzi - I specifically mentioned `EXEC` in my answer. – Martin Smith Oct 30 '12 at 19:14
  • @MohammadGoudarzi I've added the execution method as well. (I did read in a link of your response the issues with sp_executesql, but I will not be using that). – steventnorris Oct 30 '12 at 19:15
  • Also check the link I just mentioned in the post and the comment. There IS a difference. – Rikki Oct 30 '12 at 19:15
  • So it was the questioner issue that @steventnorris solved it! :D Cheers all – Rikki Oct 30 '12 at 19:18
  • @steventnorris - Yes. You would only get a problem if you were doing that concatenation to generate a string that you then execute. Doing `LIKE '%'+@var+'%'"` in the query itself is fine. – Martin Smith Oct 30 '12 at 19:21
2

You should use "SqlParameter" to send the values to the stored procedure does searching. The purpose of "SqlParameter" is to reject all the injection things in the values. Also if you have to execute a text containing sql code or concat the parameters, again you should set the "CommandType" property of the command to "Text" and use a "SqlParameter" to send your value to that text.

Check the Microsoft documentations about this here:

http://msdn.microsoft.com/en-us/library/ff648339.aspx

and also another question on stackoverflow here:

How does SQLParameter prevent SQL Injection?

Also take a look at here to see some specific examples:

Examples of SQL injection even when using SQLParameter in .NET?

Update:

As you have updated the question and now the way of execution is exactly specified there is no sql injection problem anymore in the code you mentioned.

Cheers

Community
  • 1
  • 1
Rikki
  • 3,338
  • 1
  • 22
  • 34