search - 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.