How can I avoid SQL injection attacks in my ASP.NET application?

Question

I need to avoid being vulnerable to SQL injection in my ASP.NET application. How might I accomplish this?

Solution 1

Even though your question is very generic, a few rules always apply:

• Use parameterized queries (SqlCommand with SqlParameter) and put user input into parameters.
• Don't build SQL strings out of unchecked user input.
• Don't assume you can build a sanitizing routine that can check user input for every kind of malformedness. Edge cases are easily forgotten. Checking numeric input may be simple enough to get you on the safe side, but for string input just use parameters.
• Check for second-level vulnerabilites - don't build SQL query strings out of SQL table values if these values consist of user input.
• Use stored procedures to encapsulate database operations.

Solution 2

Use Prepared Statements (link to an ASP.NET tutorial that uses prepared statements in the 'To add nodes for products' section). that's all there is to it.

Well, that or use an ORM, like Linq to SQL or NHibernate, they internally use prepared statements.

Solution 3

Use parameters! It really is that simple :-)

Create your queries like this (for MS Sql server with C#):

SqlCommand getPersons = new SqlCommand("SELECT * FROM Table WHERE Name = @Name", conn); 

Here @Name is the parameter where you want to avoid sql injection and conn is an SqlConnection object. Then to add the parameter value you do the following:

getPersons.Parameters.AddWithValue("@Name", theName);

Here theName is a variable that contains the name you are searching for.

Now it should be impossible to do any sql injections on that query.

Since it is this simple there is no reason not to use parameters.

Solution 4

Never trust user input - Validate all textbox entries using validation controls, regular expressions, code, and so on

Never use dynamic SQL - Use parameterized SQL or stored procedures

Never connect to a database using an admin-level account - Use a limited access account to connect to the database

Don't store secrets in plain text - Encrypt or hash passwords and other sensitive data; you should also encrypt connection strings

Exceptions should divulge minimal information - Don't reveal too much information in error messages; use customErrors to display minimal information in the event of unhandled error; set debug to false

Useful link on MSDN Stop SQL Injection

Solution 5

SQL injection occurs because the query to the database is being constructed in real time, for example:

SELECT * From Table1 WHERE " + UserInput

UserInput may be malicious and contain other statements that you do not intend.

To avoid it, you need to avoid concatenating your query together.

You can accomplish this by using parametrized queries - check out the DBCommand object for your particular DB flavor.

View more solutions

Comments

[Vinko Vrsalovic]

All of those, except maybe the last one, are implied by the first one (if all your input is properly escaped, always, by the use of prepared statements (or parameterized queries)), no? Or you think there are subtle differences?

[Tomalak]

No. But someone who asks these kinds of questions very likely has no firm understanding of the implications. Making them explicit is supporting comprehension. As your experience and abilities to abstract rise, you won't need the explicitness, and you're not likely to ask such questions anymore.

[Robin Day]

Good answer, but, I disagree with "Never use dynamic SQL". Dynamic SQL is a very generic term and can be very powerful and there are many cases where it should be used. Your point should just be pass variable data as parameters.

[glagarto]

Aye Robin, I agree Dynamic SQL can be very useful and there are some good cases where it should be used, by my points where based solely on the interaction with a user in the outside world, to stop them injecting SQL. For example, a SQL statements constructed by the concatenation of SQL with user-entered values.

[Daniel Auger]

This is a good answer, but I feel that "Use stored procedures to encapsulate database operations" is misleading. Parameterized dynamic SQL is just as safe as parameterized stored procedures. Maybe you should make that more implicit in your answer for clarity's sake.

[glagarto]

hmm I just got -1 vote as well as a number of posts below me bumping us all down? (all by the same user maybe??)

[awe]

@Daniel: Parameterizes queries as used with SqlCommand, are to be used if the developer has not much control or expertise in the database technical stuff. Creating stored procedures in the database is not straight forward if you are a plain C# developer and not DBA. Using stored procedures is a good way to do it if the DBA(s) wants to do it in order to encapsulate complexity for the C# developers.

[awe]

@Vinko: I agree. The answer could be made better by splitting up in 2 sections: First the points 2-4 as answer to what you need to consider, and then points 1 and 5 as possible solutions on how to solve the issues pointed out.

[PepitoSh]

Important alright, but only the first bullet addresses the OP's question.

[Csibi Norbert]

What about backend who is using EF ( Entity Framework ) ? How this will be secured? or enhance the security?