Click here to Skip to main content
Click here to Skip to main content

Introduction to SQL Injection

, 31 Mar 2009
Rate this:
Please Sign up or sign in to vote.
A reason why we have to write Stored Procedures and remove SQL code from web pages and forms.

Introduction

Every time I get involved in a new project that I happen to inherit, I always come across SQL code in web pages and input that is not trapped. Well, some of these applications are used internally and some go to clients externally. A programmer argued about how dangerous it is to write your SQL statement in web pages. In this short article, I am going explain the reasons why it is 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 web pages. It is because it leads to SQL injection attacks. 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 the 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 a 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 for an online shopping application for a client. The code looks like this, and the code was written 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 was similar to:

<%@ 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" 
           CellPadding="4" ForeColor="#333333" 
           GridLines="None" Height="169px" 
           onselectedindexchanged="GridView1_SelectedIndexChanged" 
           Width="396px">
        <RowStyle BackColor="#FFFBD6" ForeColor="#333333" />
        <FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
        <PagerStyle BackColor="#FFCC66" ForeColor="#333333" 
                       HorizontalAlign="Center" />
        <SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />
        <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
        <AlternatingRowStyle BackColor="White" />
    </asp:GridView>
    <asp:Label ID="lblMessage" runat="server"></asp:Label>
    </form>
</body>
</html>

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

PIC1.JPG

Now, maybe the programmer was happy when he saw that he could search for me Smile | :) , 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 to try this is because they get an error message that will expose the name of the tables and the database. As you saw in the 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”

pic2.JPG

Oops, it gave more than it should have. This brought Dave’s info and it should have brought only vuyiswa's. My point is that the user can add SQL statements on your textbox and return more than 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 in MySQL, with a (#) code.) He could even use a Batch command to execute a SQL command.

Let's look at this one. Enter the following:

Vuyiswa' ; truncate table dbo.Customers --

pic3.JPG

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

Vuyiswa' ; truncate table dbo.memyself --

With the “;” ,you start another line. And all this will be executed. Now if you check your table, you will not find any data.

Solution

To overcome this problem, a parameterized Stored Procedure and more input validation are required. 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.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 the 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 built with this vulnerability, and believe me, there are malicious users hunting these sites every day.

Thank you for reading!

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Vuyiswa Maseko
Software Developer (Senior) Dotnetfunda
South Africa South Africa
Vuyiswa Junius Maseko is a programmer and a moderator in ".NetFunda. Vuyiswa has been developing for 15 years now. his major strength are C# 1.1,2.0,3.0,3.5 and sql and his interest are in Silverlight,WPF,C#. He has been doing a lot of Silverlight development. He has been using .net since the beta version of it. He is also an online Trainer at www.Itfunda.com. Thanks to people like Sheo Narayan (.Netfunda) , Chris Maunder (codeproject), Colin Angus Mackay (codeproject), Dave Kreskowiak (Codeproject),.They have made vuyiswa what he is today.
Follow on   Twitter

Comments and Discussions

 
QuestionAnother article on SQL Injection PinmemberGaikwad_anil19875-May-12 4:16 
SuggestionAnother way! PinmemberShahbaz Hussain12-Nov-11 1:35 
GeneralRe: Another way! PinmemberVuyiswa Maseko12-Nov-11 1:57 
GeneralMy vote of 5 Pinmemberhitech_ssc12-Oct-11 0:28 
GeneralMy vote of 1 Pinmemberbabakzawari25-Dec-09 4:35 
GeneralRe: My vote of 1 PinmemberVuyiswa Maseko20-Sep-11 1:06 
GeneralGreat Job!!! PinmemberJohn Vournelis23-Sep-09 17:18 
GeneralRe: Great Job!!! PinmemberVuyiswa Maseko23-Sep-09 22:06 
GeneralThe Reason i wrote this article PinmemberVuyiswa Maseko29-Jul-09 2:35 
Generalgood article PinmemberDonsw20-Apr-09 10:07 
GeneralRe: good article PinmemberVuyiswa Maseko20-Apr-09 20:15 
GeneralMy vote of 1 Pinmembermbielski31-Mar-09 4:19 
GeneralRe: My vote of 1 PinmemberVuyiswa Maseko20-Apr-09 20:23 
GeneralMy vote of 1 PinmemberPstry30-Mar-09 20:27 
GeneralRe: My vote of 1 PinmemberVuyiswa Maseko30-Mar-09 21:28 
GeneralMy vote of 1 PinmemberNigel-Findlater30-Mar-09 19:45 
GeneralRe: My vote of 1 PinmemberVuyiswa Maseko31-Mar-09 0:56 
GeneralA simple alternative PinmemberNigel-Findlater30-Mar-09 19:43 
GeneralRe: A simple alternative PinmemberKramII30-Mar-09 22:39 
GeneralRe: A simple alternative PinmemberVuyiswa Maseko30-Mar-09 22:49 
GeneralRe: A simple alternative [modified] PinmemberKramII31-Mar-09 0:25 
GeneralRe: A simple alternative PinmemberVuyiswa Maseko31-Mar-09 0:31 
GeneralRe: A simple alternative PinmemberKramII31-Mar-09 0:46 
GeneralRe: A simple alternative PinmemberVuyiswa Maseko31-Mar-09 1:00 
GeneralNeeds some work. PinmemberAndy *M*26-Mar-09 21:40 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140721.1 | Last Updated 31 Mar 2009
Article Copyright 2009 by Vuyiswa Maseko
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid