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

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.

Answer 2

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

Answer 3

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 4

Nice it's working fine thanx bro