Parameterized strings and LIKE operator and wildcards

Question

In my searches, I saw the use of parameterized strings in SQL queries generated as follows:

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

However, it was mentioned in this forum that it falls under SQL injection, despite the fact that it is used in a parameterized string. I can only assume that concatenated strings circumvent all parameterized security and simply insert the value directly as a string. If so, then how to use wildcard operators in a parameterized query, avoiding pasting SQL code?

Answer 1

It is not vulnerable to SQL Injection.

The one who told you this is wrong. '%' +@var +'%' regarded as data not as executable code. It is evaluated as a string, then used as a template on the right side of LIKE .

You will only have a problem if you were then EXEC result of such a concatenation. Just doing string concatenation in the query itself is not a problem.

Answer 2

You must use "SqlParameter" to send values ​​to the stored procedure that performs the search. The purpose of "SqlParameter" is to reject all injection values ​​in the values. Also, if you need to execute text containing sql code or concat parameters, again you must set the "CommandType" property of the command to "Text" and use "SqlParameter" to send your value to this text.

Check out the Microsoft documentation about this here:

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

as well as another stackoverflow question:

How does SQLParameter prevent SQL injection?

Also consider a few specific examples here:

SQL injection examples even when using SQLParameter in .NET?

Update:

As you updated the question, and now the execution method is precisely indicated, in the mentioned code there is no longer a problem with sql injection.

Greetings