Patterns to prevent SQL Injection in C#
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);
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);
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?
Walt Lynton, Mar 19, 2013
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.
- 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
Christopher Ronny, Aug 02, 2013
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
Try to use the class Parameters from the db object.
This class prevent about injection.
Cássio Batista Pereira, Mar 21, 2013