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

Asp.net Preventing SQL Injection Attacks using Parameterized Queries

Aug 3, 2014
Introduction:

Here I will explain how to prevent
SQL injection attacks in asp.net website with example using c#, vb.net. 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 posts I explained
SQL injection example in Asp.net, 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 how to prevent SQL injection attacks in asp.net website with example using c#, vb.net.

To prevent SQL injection attacks we need to use parameterized queries to pass values from code behind to database like as shown below

C# Code


SqlCommand cmd = new SqlCommand("select Name,Total=value from countrydetails where value =@value", con);
cmd.Parameters.AddWithValue("@value", txtSearch.Text);

VB.NET Code


Dim cmd As New SqlCommand("select Name,Total=value from countrydetails where value =@value", con)
cmd.Parameters.AddWithValue("@value", txtSearch.Text)

To know more about how SQL injection occurs check this article SQL injection Attacks in Asp.net.
If you want to check example to prevent SQL injection attacks 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>Prevent 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 =@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


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 =@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
End Sub
End Class
When we run above code we will get output like as shown below

Demo


To know more about how SQL injection occurs check this article SQL injection Attacks in Asp.net.

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

0 comments :

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.