Patterns to prevent SQL Injection in C#

Hi:

Here is the example of SQL injection:

Suppose a user searches for a product by entering a product name:
The product name is directly added to construct a sql query string.


// this is what the user entered

string name = "Monitor";      

// build sql 

string sql = "SELECT * FROM Products WHERE ProductName = '" + name + "'";
var products = db.Query(sql);

All is fine.
But now suppose a malicious user enters the following:


// this is what the user entered

string name = "x';DROP TABLE Products;--";      

// build sql 

string sql = "SELECT * FROM Products WHERE ProductName = '" + name + "'";
var products = db.Query(sql);

See what happens? Some very scary SQL gets executed.

Now my question is as follows: What is the best way to prevent SQL Injection?
One could parse the incoming entries and check for SQL keywords.
However, are there some good patterns to do this? Or is there perhaps a better way?

Thanks,
Walt

Walt Lynton, Mar 19, 2013

Reply 1

Based on the above answers, I think we have to be a bit more careful when presenting solutions that prevent Sql Injection because the damage done can be large.

For example:

- Stored procedures are NOT a solution.
A procedure that performs string concatenation is highly vulnerable to Sql injection.

- Re-validate data in Stored Procedure is NOT a solution.
I am not even sure what kind of validation to do. Disallow certain keywords? How and which ones? It is almost impossible to catch all possible attacks.

- Use parameterized queries.
Indeed, using SQL Parameters is a very good way to prevent Sql injection.

- Use ORM tools (LINQ, NHibernate, LINQ to Entities)
First of all, LINQ is not an ORM tool - I am not sure how this helps in Sql injection.
With LINQ to Entities, I guess you mean Entity Framework.
Yes, using a proven ORM tool such as NHibernate and Entity Framework are good solutions.
But you should not pass any user input concatenated into Sql strings directly to these tools.

- Use regular expression to discard input string
Yes, you can create a black-list of keywords that are not allowed, or a white-list of expressions that are allowed.
However, this should be in combination with SQL Parameters or a proven ORM.

The link you provided is also giving bad advice: http://cybarlab.blogspot.com/2013/04/how-to-prevent-sql-injection.html

Best
CR

Christopher Ronny, Aug 02, 2013

Reply 2

SQLInjection is a one kind of attack where the attacker generates malicious codeand send into SQL query to access database or system. If we follow sometechniques we can prevent it. Some techniques:

Use stored procedure (SP)

Re-validate data in stored procedures.

Use parameterized query

Use ORM tools (LINQ, NHybernet, LINQ to Entities)

Use regular expression to discard input string

For more Pleasevisit: http://cybarlab.blogspot.com/2013/04/how-to-prevent-sql-injection.html

Thanks & regard

World Travelar, Apr 08, 2013

Reply 3

Try to use the class Parameters from the db object.
This class prevent about injection.

Cássio Batista Pereira, Mar 21, 2013