Wednesday, August 17, 2011

Preventing SQL Injection in ASP.NET

Posted by Rahul Kharde at 9:56 PM
What is SQL Injection?

An SQL query comprises one or more SQL commands, such as SELECT, UPDATE or INSERT. For SELECT queries, each query typically has a clause by which it returns data, It’s these types of queries that make the SQL language so popular and flexible… it’s also what makes it open to SQL injection attacks. As the name suggests, an SQL injection attack "injects" or manipulates SQL code. By adding unexpected SQL to a query, it is possible to manipulate a database in many unanticipated ways.

One of the most popular ways to validate a user on a Website is to provide them with an HTML form through which they can enter their username and password. Let’s assume that we have the following simple HTML form:

With the blow example we will understand the SQL Injection You enter the ID of the users, you want to search and click the Search users button. If a match is found in the database, we show the users record in the GridView.

SQL-Server

create table users

(

userId int identity(1,1) not null,

userName varchar(50) not null,

userPass varchar(20) not null

)

insert into users(userName, userPass) values('abc', 'a')

insert into users(userName, userPass) values('pqr', 'p')

insert into users(userName, userPass) values('xyz', 'x')

HTML

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

<title>Untitled Pagetitle>

head>

<body>

<form id="form1" runat="server">

<div>

User ID:<asp:TextBox ID="TextBox1" runat="server">asp:TextBox>

<br>

<br>

<asp:Button ID="Button1" runat="server" Text="Search" OnClick="Button1_Click" />

<br />

<br />

<asp:GridView ID="GridView1" runat="server">

</asp:GridView>

</div>

</form>

</body>

</html>

Code Behind

protected void Button1_Click(object sender, EventArgs e)

{

SqlConnection Objcon = new SqlConnection("Data Source=localhost;Initial Catalog=myDB;integrated security=SSPI");

SqlCommand Objcmd = new SqlCommand();

DataSet Objds = new DataSet();

Objcmd.Connection = Objcon;

Objcmd.CommandType = CommandType.Text;

Objcmd.CommandText = "select * from users where userid='" + TextBox1.Text.ToString() + "'";

SqlDataAdapter Objda = new SqlDataAdapter();

Objda.SelectCommand = Objcmd;

Objda.Fill(Objds);

GridView1.DataSource = Objds.Tables[0];

GridView1.DataBind();

}


The Button1_Click event handler has the required ADO.NET code to get data from the database. This code is highly susceptible to sql injection attack and I will never ever have code like this in production environment. The second line in Button1_Click event handler, dynamically builds the sql query by concatenating the user ID that we typed into the TextBox.

So, for example, if we had typed 3 into the user ID textbox, we will have a SQL query as shown below.

Select * from users where userid=’3’


If a malicious user, types something like ‘3 OR 1=1-- into the TextBox, then we will have a SQL query as shown below.


Select * from users where userid=’’ OR 1=1 –‘

When this query is executed, it shows all the data in the users table. This is SQL Injection Attack, as the user of the application is able to inject SQL and get it executed against the database.

It is very easy to avoid SQL Injection attacks by using either parameterized queries or using stored procedures.


SQL-SERVER

CREATE PROCEDURE sp_getUsers

@Id VARCHAR(255)

AS

BEGIN

SELECT * FROM users WHERE userid=@Id

END

GO

CODE Behind

protected void Button1_Click(object sender, EventArgs e)

{

SqlConnection Objcon = new SqlConnection("Data Source=localhost;Initial Catalog=myDB;integrated security=SSPI");

SqlCommand Objcmd = new SqlCommand();

DataSet Objds = new DataSet();

Objcmd.Connection = Objcon;

Objcmd.CommandType = CommandType.StoredProcedure;

Objcmd.Parameters.Add("@Id", SqlDbType.VarChar, 255, "userid").Value = TextBox1.Text.ToString();

Objcmd.CommandText = "sp_GetUsers";

SqlDataAdapter Objda = new SqlDataAdapter();

Objda.SelectCommand = Objcmd;

Objda.Fill(Objds);

GridView1.DataSource = Objds.Tables[0];

GridView1.DataBind();

}

Now types something like ‘3 OR 1=1-- into the TextBox, then it will thrown an exception





If you enjoyed this post and wish to be informed whenever a new post is published, then make sure you subscribe to my regular Email Updates. Subscribe Now!


Kindly Bookmark and Share it:

YOUR ADSENSE CODE GOES HERE

0 comments:

Have any question? Feel Free To Post Below:

 

Popular Posts

Recent Comments

© 2011. All Rights Reserved | Help to understand .Net | Template by Blogger Widgets

Home | About | Top