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.

score 21 · Accepted Answer · answered Oct 23 '08 at 03:52

21

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.

answered Oct 23 '08 at 03:52

craigb

16,827
7
51
62

Thanks! Nice to get working code snippet as an answer. I was getting stuck on the {0} in the query text. – MikeJ Oct 23 '08 at 05:01

score 8 · Answer 2 · answered Oct 23 '08 at 03:51

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.

score 0 · Answer 3 · answered Nov 16 '17 at 16:40

In Entity Framework 6 it could be done like this by Native SQL:

List<Person> peopleList = contex.People.SqlQuery(
    @"SELECT * FROM [Person].[Person]
       WHERE [FirstName] LIKE N'%' + @p0 + '%' ", "ab").ToList();

Or

List<Person> peopleList = contex.People.SqlQuery(
    @"SELECT * FROM [Person].[Person]
       WHERE [FirstName] LIKE N'%' + @name + '%' ",
    new SqlParameter("@name", "ab")).ToList();

Also, you can just use LINQ to Entities directly:

List<Person> peopleList1 = contex.People.Where(s => s.FirstName.Contains("ab")).ToList();

score -5 · Answer 4 · edited Oct 23 '08 at 04:10

Short Anwser:

1) name.Replace("'", "''").... Replace any escape characters that your database may have (single quotes being the most common)

2) if you are using a language like .net use Parameterized Queries

sql="Insert into Employees (Firstname, Lastname, City, State, Zip, Phone, Email) Values ('" & frmFirstname.text & "', '" & frmLastName & "', '" & frmCity & "', '" & frmState & "', '" & frmZip & "', '" & frmPhone & "', '" & frmEmail & "')"

The above gets replaced with the below

Dim MySQL as string = "Insert into NewEmp (fname, LName, Address, City, State, Postalcode, Phone, Email) Values (@Firstname, @LastName, @Address, @City, @State, @Postalcode, @Phone, @Email)" 

With cmd.Parameters:
    .Add(New SQLParameter("@Firstname", frmFname.text))
    .Add(New SQLParameter("@LastName", frmLname.text))
    .Add(New SQLParameter("@Address", frmAddress.text))
    .Add(New SQLParameter("@City", frmCity.text))
    .Add(New SQLParameter("@state", frmState.text))
    .Add(New SQLParameter("@Postalcode", frmPostalCode.Text))
    .Add(New SQLParameter("@Phone", frmPhone.text))
    .Add(New SQLParameter("@email", frmemail.text))
end with

3) user Stored procs

4) use Linq to SQL, again if you are using .net

I don't see why all the down votes here... I showed the range of options and what was avaiable. Personally I wouldn't use option 1, but I have said that all characters would need to be escaped not just the single quotes. If done that is perfectly valid. The options only get better from there. People shouldn't down vote without saying why. — vdhant, Mar 29 '10 at 07:10
vdhant, you didn't answer the question. The initial question was focused on how to use parameterized queries with the Like operator, and you don't mention the Like operator at all in your answer. — CodeThug, May 10 '11 at 14:52

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

4 Answers4

Linked