How to avoid SQL injection in SQL query using Operator using parameters?

Question

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

SELECT * FROM providers WHERE sale_name, for example, '%' + name +% ';

Trying to avoid the SQL injection problem and parameterize it, but I'm not quite sure how this will be achieved. Any suggestions?

Note that I need a solution for the classic ADO.NET - I don’t have to switch this code to something like LINQ.

Answer 1

try the following:

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 structure will automatically handle citation issues.

Answer 2

Just parameterize your query:

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

Now you can pass your variable "name" to the @name parameter, and the request will be executed without any danger of injection attacks. Even if you pass something like “OR true,” it will work fine.

Answer 3

In Entity Framework 6, this can be done using 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(); 

Alternatively, you can directly use LINQ to Entities:

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

Answer 4

Short Anwser:

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

2) if you use 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 is replaced 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 saved procedures

4) use Linq for SQL, again if you use .net