Saturday, 10 September 2016

Sql Injection and prevention in asp.net

13:35    No comments

Sql Injection
It means injecting malicious sql statements that can hamper the functioning of the application.

Let's see with an example:
select * from EmployeeTest

In the aspx file lets take a gridview

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <div>
                Employee Name :
                <asp:TextBox ID="txtEmployeeName" runat="server"></asp:TextBox>
                <asp:Button ID="EmpSearch" runat="server" OnClick="btnClick_Search" Text="Search"/>
                <br /><br />
                <asp:GridView ID="gdvEmlpoyee" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None">
                    <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
                    <EditRowStyle BackColor="#999999" />
                    <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
                    <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
                    <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
                    <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
                    <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
                    <SortedAscendingCellStyle BackColor="#E9E7E2" />
                    <SortedAscendingHeaderStyle BackColor="#506C8C" />
                    <SortedDescendingCellStyle BackColor="#FFFDF8" />
                    <SortedDescendingHeaderStyle BackColor="#6F8DAE" />
                </asp:GridView>
            </div>
        </div>
    </form>
</body>

</html>

In code behind add the following code

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace ADONET
{
    public partial class SqlInjection : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }
        protected void btnClick_Search(object sender, EventArgs e)
        {
            String CS = ConfigurationManager.ConnectionStrings["EmplyoeeDB"].ConnectionString;
            using (SqlConnection sqlcon = new SqlConnection(CS))
            {
                SqlCommand sqlcmd = new SqlCommand("select * from EmployeeTest where empName like '" + txtEmployeeName.Text.Trim() + "%'", sqlcon);
                sqlcon.Open();
                SqlDataReader sqlreader = sqlcmd.ExecuteReader();
                gdvEmlpoyee.DataSource = sqlreader;
                gdvEmlpoyee.DataBind();
            }
        }
    }
}


Lets Execute our application we will get the following output

Now what will happen if User insert the following Query and click Search

A'; delete from EmployeeTest -- 

when above request will be executed, table will be deleted from database.

Preventing Sql Injection
We can prevent Sql injection by using Parametrized query or Stored procedure.

Using Parametrized Query
Changing button click event
        protected void btnClick_Search(object sender, EventArgs e)
        {
            String CS = ConfigurationManager.ConnectionStrings["EmplyoeeDB"].ConnectionString;
            using (SqlConnection sqlcon = new SqlConnection(CS))
            {
                SqlCommand sqlcmd = new SqlCommand("select * from EmployeeTest where empName like @empName", sqlcon);
                sqlcmd.Parameters.AddWithValue("@empName", txtEmployeeName.Text + "%");
                sqlcon.Open();
                SqlDataReader sqlreader = sqlcmd.ExecuteReader();
                gdvEmlpoyee.DataSource = sqlreader;
                gdvEmlpoyee.DataBind();
            }
        }

Here complete text in the textbox will be treated as single value.

No data is returned because no match is found.



Using Stored Procedure
First we have to create a stored procedure

Create proc A_GetEmployeeDetails
@empName varchar(200)
As
select * from EmployeeTest where EmpName like @empName +'%'

Changing button click event

        protected void btnClick_Search(object sender, EventArgs e)
        {
            String CS = ConfigurationManager.ConnectionStrings["EmplyoeeDB"].ConnectionString;
            using (SqlConnection sqlcon = new SqlConnection(CS))
            {
                SqlCommand sqlcmd = new SqlCommand("A_GetEmployeeDetails", sqlcon);
                sqlcmd.CommandType = System.Data.CommandType.StoredProcedure;
                sqlcmd.Parameters.AddWithValue("@empName", txtEmployeeName.Text);
                sqlcon.Open();
                SqlDataReader sqlreader = sqlcmd.ExecuteReader();
                gdvEmlpoyee.DataSource = sqlreader;
                gdvEmlpoyee.DataBind();
            }
        }
Here complete text in the textbox will be treated as single value.
No data is returned because no match is found.

0 comments:

Post a Comment