CODEWITHSUNDEEP
c#asp.net-web-api2sql-injection
Muhammad Faisal
Muhammad Faisal

Reputation: 155

Prevent Sql Injection On Web API 2 inputs

Hi we have old API project which is build on ASP.NET (fxows.asmx ) soap service and now we are converting into WEB API 2.so in first phase we are not changing Business logic layer which have thousands of inline queries. my question is this how can we prevent SQL injection in this case ? because SQL parameter is not an option for us because too much change same goes for ORM integration like entity framework so my only guess is if we validate a string property for each input then may be we can prevent it. is there any other way ? also is there any easy way to validate each string property ?

i cannot copy exact code here because we are not allowed to do this so i am posting a dummy sample

we are executing query like this

SqlCommand command = new SqlCommand();
command.CommandText = "SELECT * FROM Entity where id =" +InputParameter;
command.CommandType = CommandType.Text;
SqlDataReader rdr = command .ExecuteReader();

Upvotes: 2

Views: 3956

Answers (2)

eftpotrm
eftpotrm

Reputation: 2271

Web API isn't the issue here, neither is your reluctance to use an ORM (which I often don't use myself).

The issue is that you're building your query by concatenating an input into the query text. This is always an SQL Injection hazard, and a performance limiter as well because it stops SQL Server from efficiently cacheing a query plan.

Instead of doing it like this, set your CommandText to SELECT * FROM Entity where id = @InputParameter. Define a new SqlParameter with a matching name, assign the value to that and add the parameter to your SqlCommand and you'll have an injection-proof and better performing query.

Upvotes: 1

Marc Gravell
Marc Gravell

Reputation: 1062660

because SQL parameter is not an option for us because too much change

You cannot fix SQL injection vulnerabilities without changing some code. It sounds like you want to do something cheap and cheerful, perhaps something that scans your html form inputs and disallows certain values, but that:

  1. is not reliable (it is an arms race)
  2. it breaks your application (making values that should be valid impossible to enter, which really annoys users)

However; tools exist that can make it painless to switch to parameterized code; let's say we start with with the example from the question:

SqlCommand command = new SqlCommand();
command.CommandText = "SELECT * FROM Entity where id =" +InputParameter;
command.CommandType = CommandType.Text;
SqlDataReader rdr = command.ExecuteReader();

we can tweak that using "Dapper" to be (with using Dapper; at the top of the code file, and consuming the Dapper package from nuget.org):

var rdr = connection.ExecuteReader("SELECT * FROM Entity where id = @id",
    new { id = InputParameter });

and that's it; it is less code, and is now fully safe from SQL injection.

Dapper also has many more advanced things it can do for you, such as processing the results of a query into objects - but: I'm guessing you want to minimize the changes, so : limiting yourself to ExecuteReader is a good place to start.

(as a minor note: because Dapper works against all ADO.NET providers, the reader you get back is an IDataReader, not a SqlDataReader; it should function the same, though)

Upvotes: 2

Related Questions