Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

What is SQL Injection and How do you Prevent it in Asp.net using C#, VB.NET

Apr 1, 2015
Introduction:

Here I will explain what is sql injection attacks and how to prevent it in
asp.net website with example and how to prevent SQL injection attacks in asp.net using c#, vb.net with example. SQL injection means injecting some SQL commands in SQL statements to hack your data or delete data or change your data in tables via web page input.

Description:

In previous post I explained jQuery scroll to particular position of div when click on link, SQL Server remove duplicate records from table, SQL Server insert multiple rows with single insert statement,
how to send mail with attachment in asp.net and many more articles related to asp.net, SQL, c#, vb.net. Now I will explain what is sql injection attack in asp.net website with example.

First design one table countrydetails in your database like as shown below

Column Name
Data Type
Allow Nulls
ID
Int(set identity property=true)
No
name
Varchar(50)
no
value
Int
no
Once we create table we need to enter some dummy data for our application purpose
Now in your Default.aspx page write the following code


<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>SQL Injection Attacks in Asp.net Website</title>
</head>
<body>
<form id="form1" runat="server">
<div>
Enter Count:<asp:TextBox ID="txtSearch" runat="server" />
<asp:Button ID="btnsearch" Text="Search" runat="server" onclick="btnsearch_Click" />
<br /><br />
<asp:GridView ID="gvDetails" CellPadding="5" runat="server">
<HeaderStyle BackColor="#df5015" Font-Bold="true" ForeColor="White" />
</asp:GridView>
</div>
</form>
</body>
</html>
After completion of aspx page write the following code in codebehind

C# Code


using System;
using System.Data;
using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnsearch_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable();
using (SqlConnection con = new SqlConnection("Data Source=SureshDasari;Integrated Security=true;Initial Catalog=MySampleDB"))
{
con.Open();
SqlCommand cmd = new SqlCommand("select Name,Total=value from countrydetails where value = "+txtSearch.Text+"", con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
con.Close();
gvDetails.DataSource = dt;
gvDetails.DataBind();
}
}
}
VB.NET Code


Imports System.Data
Imports System.Data.SqlClient
Partial Class VBcode
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
End Sub
Protected Sub btnsearch_Click(ByVal sender As Object, ByVal e As EventArgs)
Dim dt As New DataTable()
Using con As New SqlConnection("Data Source=SureshDasari;Integrated Security=true;Initial Catalog=MySampleDB")
con.Open()
Dim cmd As New SqlCommand("select Name,Total=value from countrydetails where value= = " + txtSearch.Text + "", con)
Dim da As New SqlDataAdapter(cmd)
da.Fill(dt)
con.Close()
gvDetails.DataSource = dt
gvDetails.DataBind()
End Using
End Sub
End Class
When we run above code we will get output like as shown below

Demo


SQL Injection Example

Now I will explain how to inject SQL in our queries from our webpage input.
First in our textbox enter text like “10 or 1=1” as shown below and check the below output

It returns all the rows from table because our textbox input value converts query as like as shown below


select Name,Total=value from countrydetails where value =10 or 1=1

In above query it will check for value =10 as well as it will check for 1=1 means always true that’s the reason it will returns all the values from table this way they can inject values to change our queries and access all the values from table.

In another case if user enters value like “10; Drop TABLE countrydetails” in it will drop table from our database because our query will changed like this


select Name,Total=value from countrydetails where value =10; Drop TABLE countrydetails
This way they can inject SQL and get all the details or delete data or drop tables.

To avoid these SQL injection attacks always we need to use parameterized queries like as shown below


select Name,Total=value from countrydetails where value =@value
Example of C# Code


DataTable dt = new DataTable();
using (SqlConnection con = new SqlConnection("Data Source=SureshDasari;Integrated Security=true;Initial Catalog=MySampleDB"))
{
con.Open();
SqlCommand cmd = new SqlCommand("select Name,Total=value from countrydetails where value =@value", con);
cmd.Parameters.AddWithValue("@value", txtSearch.Text);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
con.Close();
gvDetails.DataSource = dt;
gvDetails.DataBind();
}
VB.NET Code


Dim dt As New DataTable()
Using con As New SqlConnection("Data Source=SureshDasari;Integrated Security=true;Initial Catalog=MySampleDB")
con.Open()
Dim cmd As New SqlCommand("select Name,Total=value from countrydetails where value =@value", con)
cmd.Parameters.AddWithValue("@value", txtSearch.Text)
Dim da As New SqlDataAdapter(cmd)
da.Fill(dt)
con.Close()
gvDetails.DataSource = dt
gvDetails.DataBind()
End Using


If you enjoyed this post, please support the blog below. It's FREE!

Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

subscribe by rss Subscribe by RSS subscribe by email Subscribe by Email

1 comments :

Anonymous said...

You have to set the parmaeter type, then only it will prevent the injection data.

Give your Valuable Comments

Note: Only a member of this blog may post a comment.

© 2015 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.