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

By: Suresh Dasari 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

<head>

<title>SQL Injection Attacks in Asp.net Website</title>

</head>

<body>

<div>

Enter Count:<asp:TextBox ID="txtSearch" runat="server" />

<asp:Button ID="btnsearch" Text="Search" runat="server" onclick="btnsearch_Click" />

<asp:GridView ID="gvDetails" CellPadding="5" runat="server">

</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 Email

1 comments :

Anonymous said...: You have to set the parmaeter type, then only it will prevent the injection data.; April 16, 2015 at 11:11 PM

Give your Valuable Comments

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

Subscribe to: Post Comments ( Atom )

ASP.NET,C#.NET,VB.NET,JQuery,JavaScript,Gridview