Introduction to SQL Injection in ASP.NET

Vuyiswamb
Posted by in ASP.NET category on for Beginner level | Views : 8426 red flag
Rating: 5 out of 5  
 3 vote(s)

Every time I get involve in a new project that I happen to inherit, I always come across sql code in the pages and input that is not trapped. Well some of this applications are used internally and some go to clients externaly. One programmer argued about how dangerous it is to write your sql statement in the pages. In this short article am going explain the reasons why is it not a good programming practice or dangerous to your users if you don’t trap the input or if you write your sql on your pages. It is because it leads to sql injection. What is SQl Injection?
Background

Wikipedia says SQL injection is a code injection technique that exploits a security vulnerability occurring in the database layer of an application. The vulnerability is present when user input is either incorrectly filtered for string literal escape characters embedded in SQL statements or user input is not strongly typed and thereby unexpectedly executed. It is an instance of a more general class of vulnerabilities that can occur whenever one programming or scripting language is embedded inside another. SQL injection attacks are also known as SQL insertion attacks.[1]

Using the code

We are going to use multiple comments in our article and we are going to use C# as our language.

Start

Let us take an example of a code that i came across in some online catalogue, that was for online shooping application for a client. The code look like this and the page ware written too close to something like this.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data;

using System.Data.SqlClient;

using System.Web.Configuration;

 

public partial class _Default : System.Web.UI.Page

{

    protected void Page_Load(object sender, EventArgs e)

    {

 

    }

 

    protected void  Show_Data(String strssearch)

    {

        String strcon = WebConfigurationManager.ConnectionStrings["MYSTRING"].ConnectionString;

 

        SqlConnection con = new SqlConnection(strcon);

 

        String tsql = "select * from dbo.memyself where fname = '" + strssearch + "'";

 

        SqlCommand cmd = new SqlCommand(tsql, con);

 

        con.Open();

 

        SqlDataReader reader = cmd.ExecuteReader();

       

        GridView1.DataSource = reader;

       

        GridView1.DataBind();

 

        reader.Close();

 

        con.Close();

 

    }

 

    protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)

    {

 

    }

    protected void Button1_Click(object sender, EventArgs e)

    {

       

        Show_Data(txtsearch.Text);

    }

}

 

And the aspx page like this

 

 

<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %>

 

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

 

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

<head runat="server">

    <title>SQL Injection Example</title>

</head>

<body>

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

    <div>

   

    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

        <asp:TextBox ID="txtsearch" runat="server"></asp:TextBox>

&nbsp;

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

        <br />

   

    </div>

    <asp:GridView ID="GridView1" runat="server" Height="169px"

        onselectedindexchanged="GridView1_SelectedIndexChanged" Width="396px">

    </asp:GridView>

    </form>

</body>

</html>

 

And when you run the application,it should show you something like this

 

Now maybe the programmer was happy when he saw that he can search for me :), but now this was a dangerous way of doing things. Let us inject the above application.

 

SQL Injection Begins

 

In the above example a bad user(Hacker) might try to temper with the Sql statement. Often, the main reason for a bad user is to get an error message that will expose the name of the tables and the database. Now as you saw above code;the error handling has not been done and the low level message will show and that info will be used to execute other malicious statements against your database and if it’s an online shopping site;the clients credits card info might be exposed. Now let us inject the above. Enter the Following text:

 

 “Vuyiswa' OR '1' = '1”

 

Oops it gave more than it should have. This brought Dave’s info and it should have brought only vuyiswa. Now my point is that the user can add sql statements on your textbox and return more that what was intended to be seen by clients and that might be someone else’s credit card info. It does not end here, the malicious user might even insert comments and run extra code. In Oracle database comments are done with a (;) and MySql with a (#) code. Or he can use a Batch command to execute an sql command.

Let look at this one. Enter the following

Vuyiswa' ; truncate table dbo.Customers --


 

What happened here? The data in the Table is deleted , I mean all of it. Let us look at it in detail.

 

Vuyiswa' ; truncate table dbo.memyself --

 

With the “;” you start an other line. All this will be executed. Now if you check your table, you will not find any data.

Solution

 

To overcome this problem a Parametrized stored procedure and more input validation like this. A Stored Procedure can be created like this


CREATE PROC prc_search
(
@FNAME varchar(20)
)
AS
SELECT * FROM DBO.MEMYSELF WHERE FNAME = @FNAME

And our function should look like this

protected void Show_Data(String strssearch)

{

String strcon = WebConfigurationManager.ConnectionStrings["MYSTRING"].ConnectionString;

SqlConnection con = new SqlConnection(strcon);

SqlCommand cmd = new SqlCommand();

cmd.CommandText = "dbo.prc_search";

cmd.Parameters.Add("@FNAME", SqlDbType.VarChar, 20).Value = strssearch;

cmd.CommandTimeout = 0;

cmd.CommandType = CommandType.StoredProcedure;

cmd.Connection = con;

SqlDataReader reader = null;

 

try

{

con.Open();

reader = cmd.ExecuteReader();

}

catch (SqlException ex)

{

lblMessage.Text = ex.Message.ToString();

}

if (reader.HasRows)

{

GridView1.DataSource = reader;

GridView1.DataBind();

reader.Close();

con.Close();

}

else

{

lblMessage.Text = "There are no Records Available";

}

}



Now if you go and test the injection again , you will see that the user cannot run sql commands again. i have added a label to show trapped exceptions and named it lblMessage. 

Conclusion
I only truncated the table. I don’t know what the malicious user would do with the data. If it is an online shopping site I don’t know. There are thousands of sites build with this vulnerability and believe me there are malicious users hunt this sites every day.

Thank you

Ngiyabonga

Page copy protected against web site content infringement by Copyscape

About the Author

Vuyiswamb
Full Name: Vuyiswa Maseko
Member Level: NotApplicable
Member Status: Member,MVP,Administrator
Member Since: 7/6/2008 11:50:44 PM
Country: South Africa
Thank you for posting at Dotnetfunda [Administrator]
http://www.Dotnetfunda.com
Vuyiswa Junius Maseko is a Founder of Vimalsoft (Pty) Ltd (http://www.vimalsoft.com/) and a forum moderator at www.DotnetFunda. Vuyiswa has been developing for 16 years now. his major strength are C# 1.1,2.0,3.0,3.5,4.0,4.5 and vb.net and sql and his interest were in asp.net, c#, Silverlight,wpf,wcf, wwf and now his interests are in Kinect for Windows,Unity 3D. He has been using .net since the beta version of it. Vuyiswa believes that Kinect and Hololen is the next generation of computing.Thanks to people like Chris Maunder (codeproject), Colin Angus Mackay (codeproject), Dave Kreskowiak (Codeproject), Sheo Narayan (.Netfunda),Rajesh Kumar(Microsoft) They have made vuyiswa what he is today.

Login to vote for this post.

Comments or Responses

Posted by: Thulanekhz on: 3/25/2009
even though we have diverted from what we do. i am glad that there are people like you who keep on enlighting us and passing your knowledge to fundi's(learner) like us.

this is a very vital topic that we tend to overlook.

thank you Vuyiswa!

Kubonga thina (Zulu, we thank you)
Posted by: Vuyiswamb on: 1/24/2011 | Points: 25
Thanks
Posted by: Akiii on: 3/23/2011 | Points: 25
I am a beginner and i found this article very valuable.
Thank you very much for sharing the knowledge.

Akiii
Posted by: Mcadeepuraj on: 11/21/2011 | Points: 25
nice article

Posted by: Vuyiswamb on: 11/22/2011 | Points: 25
Thank you :)

Login to post response

Comment using Facebook(Author doesn't get notification)