Avoiding SQL Injection in SQL query with Like Operator using parameters?

Question

Taking over some code from my predecessor and I found a query that uses the Like operator:

SELECT * FROM suppliers
WHERE supplier_name like '%'+name+%';

Trying to avoid SQL Injection problem and parameterize this but I am not quite sure how this would be accomplished. Any suggestions ?

note, I need a solution for classic ADO.NET - I don't really have the go-ahead to switch this code over to something like LINQ.

Answer 1

try this:

var query = "select * from foo where name like @searchterm";
using (var command = new SqlCommand(query, connection))
{
  command.Parameters.AddWithValue("@searchterm", String.Format("%{0}%", searchTerm));
  var result = command.ExecuteReader();
}

the framework will automatically deal with the quoting issues.

Answer 2

Simply parameterize your query:

SELECT * FROM suppliers WHERE supplier_name like '%' + @name + '%'

Now you can pass your "name" variable into the @name parameter and the query will execute without any danger of injection attacks. Even if you pass in something like "'' OR true --" it'll still work fine.