Introduction to SQL Injection

Vimalsoft(Pty) Ltd

Rate me:

3.08/5 (22 votes)

31 Mar 2009CPOL3 min read

68.9K

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:

XML

<%@ 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:

Now, maybe the programmer was happy when he saw that he could 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 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"

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:

SQL

Vuyiswa' ; truncate table dbo.Customers --

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

SQL

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:

SQL

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

And our function should look like this:

SQL

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)

Written By

Vimalsoft(Pty) Ltd

Software Developer (Senior) Vimalsoft

South Africa

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.