Sumedh Meshram

.Net Technical Blog

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

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

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);

 

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading