SQL Injection attack by Example and how to prevent it in asp.net:

Jan 4, 2012 By Anil Gaikwad

Today we will be learning about the SQL injection attack and ways to prevent this attack using simple coding practices. Using simple practices you can keep your database away from SQL injection attack. 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.

What is SQL Injection attack?

Example 1:-

Consider a simple functionality which you have on your website where you use username in the SQL query to get details of user and based on result you proceed.

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. 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


Example 2:-

Consider login functionality of a website where you pass username and password for logging into the website

String Query = SELECT User_name FROM User_master WHERE User_name ='"+ txtUsername.Text +"' AND Password ='"+ txtPassword.Text +"

Now if you pass “' OR 1 = 1; --“ to Username textbox and any value to password textbox then SQL query will be like.

String Query = SELECT User_name FROM User_master WHERE User_name '' OR 1 = 1; --' AND Password = 'aaa'

The SQL will execute following query only

SELECT User_name FROM User_master WHERE User_name '' OR 1 = 1

As here user will be successfully logged with first users credentials as due to comment sign (- -) the password part will be commented and will not be executed.

Ways to prevent SQL injection attack

Below are some of the common coding practices which can be used to prevent the SQL Injection attack and make your application database secure.

  • Use validation for input values: - You can check for User input inside the textboxes and validate them according to the expected value so no other value will be inserted into the database. In below example database expects only Numeric value so we have use a RegularExpressionValidator which will allow only numeric value to be entered inside the textBox
<asp:TextBox ID="txtid" runat="server"></asp:TextBox></span> <asp:RegularExpressionValidator ID="regExp" runat="server" ErrorMessage="*" ValidationExpression="^(-)?\d+(\.\d\d)?$" ControlToValidate="txtid"></asp:RegularExpressionValidator>
  • Use of Parameterized Query: Another way of preventing SQL Injection is using Parameterized Query, where you pass the required. As we pass parameter differently to Query it is not vulnerable to SQL Injection attack. AS SQL distinguishes between Parameter and Query.
SqlConnection conn = new SqlConnection(connectionString) DataSet ds = new DataSet(); SqlDataAdapter da= new SqlDataAdapter("SELECT * FROM User_master WHERE au_id = @u_id", conn); da.SelectCommand.Parameters.Add("@au_id", SqlDbType.VarChar, 11); da.SelectCommand.Parameters["@au_id"].Value = txtUsername.Text; da.Fill(ds);
  • Use of Stored Procedure: Another way of preventing SQL Injection is using Stored Procedure, where you pass the required parameters with values to the stored procedure which is defined for the stored procedure. As to stored procedure you pass value stored procedure takes it as Parameter so there is no risk of Database attack.
SqlConnection conn = new SqlConnection(connectionString) DataSet ds = new DataSet(); SqlDataAdapter da= new SqlDataAdapter("p_get_user_details", conn); da.SelectCommand.CommandType = CommandType.StoredProcedure; da.SelectCommand.Parameters.Add("@au_id", SqlDbType.VarChar, 11); da.SelectCommand.Parameters["@au_id"].Value = txtUsername.Text; da.Fill(ds);