HttpUtility.UrlEncode to prevent SQL inject

Question

was using HttpUtility.UrlEncode() to prevent SQL injection on where clauses. However some of the text being input has spaces and replacing them with %20 will stop the query. Is there a better alternative?

Yes - don't handcraft SQL statements based on user input! – RB. Aug 31 '16 at 14:57 — RB., Aug 31 '16 at 14:57
See [this](http://stackoverflow.com/q/601300/60188). – Anton Gogolev Aug 31 '16 at 15:02 — Anton Gogolev, Aug 31 '16 at 15:02

score 1 · Answer 1 · answered Aug 31 '16 at 15:01

Use parameters in your database queries rather than concatenating input. Job done. If that sounds like a lot of work - consider tools like dapper that make it easy:

string name = ...
int regionId = ...
var customers = connection.Query<Customer>(
    "select * from Customers where Name = @name and RegionId = @regionId",
    new { name, regionId }).AsList();

score 1 · Answer 2 · answered Aug 31 '16 at 15:03

1

To prevent SQL Injection it is preferred to use SQL Parameters. When working with parameters SQL ensures that the parameters in the query are never executed.

Never construct queries by concatenating strings. Especially when it is input that is untrusted (Received from a user)!

In your case using c# you can take a look here: http://csharp-station.com/Tutorial/AdoDotNet/Lesson06

answered Aug 31 '16 at 15:03

Nitzo

79
6

Hi, how would you do that in a linq/API based system? IE Results.WhereLike("missionCountry", "%" + prefixText + "%"); ??? – CerIs Aug 31 '16 at 15:24
`ObjectQuery contactQuery = context.Contacts .Where("it.LastName = @ln AND it.FirstName = @fn", new ObjectParameter("ln", lastName), new ObjectParameter("fn", firstName));` (In this case ln and fn are the parameters) – Nitzo Aug 31 '16 at 15:38

HttpUtility.UrlEncode to prevent SQL inject

2 Answers2