.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Articles >> C# >> Post New Resource Bookmark and Share   

 Subscribe to Articles

Countermeasures for SQL Injection Attacks in ASP.Net

Posted By:Sunil Yadav       Posted Date: June 04, 2010    Points: 25    Category: C#    URL: http://www.dotnetspark.com  

This article explains the countermeasures for SQL injections in asp.net.
 

SQL injection is a technique to exploit application using malicious code passed to sql server for execution.
A successful sql injection attack results in unauthorized access to database.

For example if login page accepts a input from user i.e. UserName and Password from the TextBox and creates a sql query by concatenating the values entered in Text boxes which is then passed to SQL Server for execution as follows:

  
SqlDataAdapter objSqlDataAdapter = new SqlDataAdapter("SELECT username, password FROM usermaster WHERE username ='" + txtUserName.Text + "' AND password='" + txtPassword.Text + "'", constr);

Imagine if i have added something like this .

txtUserName.Text="sunil";
txtPassword.Text="or 1=1-";

The above query will get executed and it will always evaluated it to true because of "or 1=1?.

Countermeasures:

Avoid Dynamic SQL Queries.

Never build SQL statements directly from user input as in the above case.
If you are constructing sql query directly from user input make sure that you are using SQLparameters with dynamic sql.

Following example illustrates using of SqlParameters while building dynamic queries.

SqlConnection objSqlConnection;
string sConn = ConfigurationManager.ConnectionStrings["TestConnectionString"].ToString();
objSqlConnection = new SqlConnection(sConn);
SqlCommand objSqlCommand = new SqlCommand();
objSqlConnection.Open();
objSqlCommand.Connection = objSqlConnection;

objSqlCommand.CommandType = CommandType.StoredProcedure;
objSqlCommand.CommandText = "sp_validateuser";
objSqlCommand.Parameters.Add("@UID", SqlDbType.VarChar).Value = sUid;
objSqlCommand.Parameters.Add("@Pass", SqlDbType.VarChar).Value = sPass;
objSqlCommand.Parameters.Add("@Action", SqlDbType.Int).Value = iAction;
SqlDataAdapter objSqlDataAdapter = new SqlDataAdapter(objSqlCommand);
DataSet objDataSet = new DataSet();
objSqlDataAdapter.Fill(objDataSet);
 
Follow the principle of least privilege for retrieving data

Always make sure that which identity to be used to access database.It is advisable to create a minimum previlage account for accessing data from the database.
Grant execute permission to specific stored procedure and based on the user type grant permission on table in the database.


    


Also make sure that while building connection string always set Persist Security Info value to false which causes the sensitive inforamtion to be availabe when connection is open.

SqlConnection objSqlConnection;
string sConn = ConfigurationManager.ConnectionStrings["TestConnectionString"].ToString();
objSqlConnection = new SqlConnection(sConn);
SqlCommand objSqlCommand = new SqlCommand();
objSqlConnection.Open();
objSqlCommand.Connection = objSqlConnection;

objSqlCommand.CommandType = CommandType.StoredProcedure;
objSqlCommand.CommandText = "sp_validateuser"; 
objSqlCommand.Parameters.Add("@UID", SqlDbType.VarChar).Value = sUid;
objSqlCommand.Parameters.Add("@Pass", SqlDbType.VarChar).Value = sPass;          
objSqlCommand.Parameters.Add("@Action", SqlDbType.Int).Value = iAction;

SqlDataAdapter objSqlDataAdapter = new SqlDataAdapter(objSqlCommand);
DataSet objDataSet = new DataSet();
objSqlDataAdapter.Fill(objDataSet);


Validate input field, encrypt query string etc..

If a user validates the data for length,format,range,application can be protected againt sql injections.
By eliminating or filtering certain characters like "'".So avoid using characters which has special meaninig in sql.
    string sSql=txtPassword.Text.Replace("'", """);

Avoid displaying database related errors

Database related exceptions includes sensitive details like database server,database name,table,stored procedure etc. that an attacker can use.It is advisable to log the exceptions and display a generic error message.

Use Stored Procedures

Use stored procedure wherever possible because stored procedures uses parameters to receive user input values so helps to prevent SQL injection.But there is a possibility of SQL injection in stored procedure as mainly SQL Injection attacks are found in dynamic sql queries, for instance if you are using sp_executesql to execute a Transact-SQL statement or batch.
Microsoft also says that use of sp_executesql can lead to malicious attack like sql injection.
So you can use parameters with stored procedures.

Use Linq,Ado.net data-entity.

If you are using Linq or Ado.net DataEntity Framework it generated SQL Queries along with parameters to protect against SQL injection.

References:

http://msdn.microsoft.com/en-us/library/ff648339.aspx

http://www.owasp.org/index.php/SQL_Injection

 Subscribe to Articles

     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend