Understanding SQL Injection in .Net

This article talk about what SQL injection is, how can this effect the security of our websites and what steps should be taken to create an ASP.NET application SQL injection proof.

SQL Injection :

Many databases in today's world are prone to SQL Injection attack. This attack is often used by attackers to attack the database which means it can gain access to database and manipulate the database.

This attack can be more dangerous if account, through which you are accessing the database, has all privileges to access database then attacker can delete the tables or even database itself.

For Example:

When we want to get the data based on username in Asp.net, then writing like:

  String Query = “select * from User_master where User_name ='"+ txtUsername.Text;

Now in textbox txtUsername you pass following value as "'; drop table User_master - -" Now your Query will be like below

 select * from User_master where User_name = ''; drop table User_master - -'

Now what this above code does it executes two statements in first statement it Executes the statement

  select * from User_master where User_name = ''

After that semicolon (;) is there which tells SQL that it is end of first statement then after that it executes the second statement,Syntactically this will two statements, as result, drop table User_master and drops the table.

Note that:- Even if semicolon is not there it will take two as different statements as SQL it self can not identify SQL statement and Parameter you have to tell him which is query and which is parameter.

Solution :

ASP.NET provides us beautiful mechanism for prevention against the SQL injection. There are some thumb rules that should be followed in order to prevent injection attacks on our websites.

User input should never be trusted. It should always be validated: 

- Dynamic SQL should never be created using string concatenations.

- Always prefer using Stored Procedures.

- If dynamic SQL is needed it should be used with parametrized commands.

- All sensitive and confidential information should be stored in encrypted.

- The application should never use/access the DB with Administrator privileges.

- Dynamic SQL should never be created using string concatenations.

If we have dynamic SQL being created using string concatenations then we are always at the risk of getting some SQL that we are not supposed to use with the application. It is advisable to avoid the string concatenations altogether.

Always prefer using Stored Procedures.

Stored procedures are the best way of performing the DB operations. We can always be sure of that no bad SQL is being generated if we are using stored procedures. Let us create a Stored procedure for the database access required for our login page and see what is the right way of doing the database operation using stored procedure.

CREATE PROCEDURE dbo.CheckUser

 (

 @userID varchar(20),

 @password varchar(16)

 )

AS

 select userID from Users where userID = @userID and password = @password

 RETURN

We have to Validate the user with parameterized commands as below code in Asp.net:

using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["SampleDbConnectionString1"].ConnectionString))

        {

            using (SqlCommand cmd = con.CreateCommand())

            {

                cmd.CommandType = CommandType.StoredProcedure;

                cmd.CommandText = "CheckUser";

                cmd.Parameters.Add(new SqlParameter("@userID", username));

                cmd.Parameters.Add(new SqlParameter("@password", password));

                using (SqlDataAdapter da = new SqlDataAdapter(cmd))

                {

                    result = new DataTable();

                    da.Fill(result);

                    //check if any match is found

                    if (result.Rows.Count == 1)

                    {

                        // return true to indicate that userID and password are matched.

                        return true;

                    }

                }

            }

        }

This is a very basic article on SQL injection. I have specifically focused on ASP.NET applications but same concept will apply for any ADO.NET application. This article is meant for the beginner's who know nothing or too little about SQLinjection and making the applications SQL injection proof. I hope this has been informative.

Note: 

- Do you like this Article, do want to  know more Intresting Concepts on .Net, then Subscribe to this Blog.

- Do you have Intresting Articels on .Net, do you want to publish this blog, then mail to 

dotnetcircle@gmail.com to publish in this blog.