Gigi Labs

Please follow Gigi Labs for the latest articles. Programmer's Ranch no longer has its domain, so please update your bookmarks and links to programmersranch.blogspot.com.

Sunday, January 5, 2014

C# Security: Bypassing a Login Form using SQL Injection

Greetings! :)

I hope you've had a fantastic holiday season. The Ranch had a bit of a break of its own, and is now back to bring you more goodness that you can hopefully learn something from.

In this article, we're going to learn about SQL injection. We'll use it to bypass a login form on a website, and you'll see just how easy it is. Despite its simplicity, this article is going to be a little bit long - because we'll need to set up a simple login form with a database that we can then use to try out the SQL injection. Naturally, you should never try out these types of attacks on someone else's website; so when you want to learn something in practice, set up a vulnerable system of your own.

To demonstrate SQL injection, we're going to be using ASP .NET (for the web form) and SQL Server 2012 Express (for the database). However, SQL injection is not tied to any technology in particular, so you could, for example, use PHP and MySQL instead. You are expected to know a little something about databases (SQL) and websites, although rest assured that there's nothing complicated in this article.

Prerequisites


For the database, we're going to use Microsoft SQL Server 2012 Express, which is freely available from Microsoft. When you download it, you will be given the option to download several different files. What you need is SQLEXPRWT_x64_ENU.exe (for 64-bit systems) or SQLEXPRWT_x86_ENU.exe (for 32-bit systems). This particular file includes tools such as SQL Server Management Studio, which you need to actually create and work with your database. You should see it included in the installer:


In order to create our ASP .NET login form, we'll use Visual Studio. If you have a paid edition of VS, then you can use that. Otherwise, you can get Visual Studio Express for Web (at the time of writing, the 2013 edition is the latest) for free:


Setting up the database



In order to create and set up our database, we'll need to use SQL Server Management Studio. Launch it, and from the Object Explorer on the left, right click on the Databases node, and click on "New Database...". Enter a name for your database (I'm using "sqlinjection") and click OK.


You should now be able to right click on the newly created database and select "New Query". This brings up a text editor where you can enter and run queries against the database. Enter the following script into this editor:

create table users(
    id int not null primary key identity(1,1),
username varchar(50) not null,
password varchar(50) not null
);

...and press F5 to execute it:


You should now have your users table with an id field as well as the username and password. Now, replace the script with the following:

insert into users(username, password)
values('hankmarvin', 'theshadows');

Press F5 to insert a new row where the username is "hankmarvin" and the password is "theshadows". The id column should be filled automatically since we are using an IDENTITY on that column. Note that in this case we're storing a password as cleartext for simplicity, but this is almost never a good idea - see my article "C# Security: Securing Passwords by Salting and Hashing" if you don't know why.

Creating the login form

In Visual Studio, go on File -> New Website... and create a new project of type ASP .NET Empty Web Site:


Next, right click on the project in Solution Explorer, and select Add -> Add New Item..., and then pick Web Form from the list of templates. Leave the name as Default.aspx.

Set up the markup in Default.aspx so that it looks like this:

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

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            Username: <asp:TextBox ID="usernameField" runat="server" />
        </div>
        <div>
            Password: <asp:TextBox ID="passwordField" runat="server" />
        </div>
        <div>
            <asp:Button ID="loginButton" runat="server" Text="Login" OnClick="loginButton_Click" />
        </div>
        <div>
            <asp:Label ID="resultField" runat="server" />
        </div>
    </form>
</body>
</html>

It's not wonderful HTML, and not exactly pretty, but it's the simple login form that we need. You can see the result by pressing F5 to launch the project in your web browser:


Next, go into your webpage's codebehind file (that would be Default.aspx.cs. Add the following statement near the top:

using System.Data.SqlClient;

Add the following event handler that actually takes care of the logic for logging in (your actual connection string may vary depending on how you installed SQL Server - see this if you run into issues):

    protected void loginButton_Click(object sender, EventArgs e)
    {
        String connStr = @"Data Source=localhost\SqlExpress;Initial Catalog=sqlinjection;Integrated Security=True;";
        String username = this.usernameField.Text;
        String password = this.passwordField.Text;
        String query = "select count(*) from users where username = '" + username
            + "' and password = '" + password + "'";

        try
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();

                using (SqlCommand command = new SqlCommand(query, conn))
                {
                    int result = (int)command.ExecuteScalar();
                    if (result > 0)
                        resultField.Text = "Login successful.";
                    else
                        resultField.Text = "Login failed! Go away!";
                }
            }
        }
        catch(Exception ex)
        {
            resultField.Text = ex.Message;
        }
    }

SQL Injection

You can now press F5 and test out the login form. If you enter the correct credentials, which are "hankmarvin" for username and "theshadows" as the password, then you should see the message "Login successful." just below the form. For any other input, the login will fail.

It should be pretty evident that the code in loginButton_Click is constructing dynamic SQL based on the credentials provided. So for the correct credentials, this would build the SQL string:

select count(*) from users where username = 'hankmarvin' and password = 'theshadows'

The weakness in this is that we can write whatever we want into the username and password fields, and they'll be included in the SQL query. Let's see what happens when we use the following input in the password field:

' OR 1=1 --

Using this, we are logged in just fine:


Oops! What just happened here? If we take a look at the dynamic SQL that is being constructed, it becomes clear:

select count(*) from users where username = '' and password = '' OR 1=1 --'

The stuff we entered in the password field is closing off the SQL string (with the apostrophe at the beginning) and is adding a condition that will always be true (1=1). A comment (--) at the end gets rid of the remaining SQL, in this case a closing apostrophe. The query's WHERE clause can now be read as follows:

((username = '') AND (password = '')) OR 1=1

Well, it turns out that 1=1 is always true, so the query ends up returning every row in the database. The count is greater than zero, and so the login is successful, even though we didn't actually provide valid credentials.

Prepared Statements

The correct way to fight SQL injection is to use prepared statements. This means that the event handler changes as follows:

    protected void loginButton_Click(object sender, EventArgs e)
    {
        String connStr = @"Data Source=localhost\SqlExpress;Initial Catalog=sqlinjection;Integrated Security=True;";
        String username = this.usernameField.Text;
        String password = this.passwordField.Text;
        String query = "select count(*) from users where username = @username and password = @password";

        try
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();

                using (SqlCommand command = new SqlCommand(query, conn))
                {
                    command.Parameters.Add(new SqlParameter("@username", username));
                    command.Parameters.Add(new SqlParameter("@password", password));
                    int result = (int)command.ExecuteScalar();
                    if (result > 0)
                        resultField.Text = "Login successful.";
                    else
                        resultField.Text = "Login failed! Go away!";
                }
            }
        }
        catch(Exception ex)
        {
            resultField.Text = ex.Message;
        }
    }

Instead of building dynamic SQL, we insert named placeholders, such as @username, to stand in for parameters in the query. We then provide these values via the SqlCommand's Parameters property, where the values are paired up with the corresponding parameter names. Since these parameters are strongly typed, things like escaping apostrophes in strings are handled automatically, and so users can't inject SQL via input fields.

In fact, if you try the same SQL injection attack as above, you'll see that it doesn't work any more:


Summary

As we have seen in this article, SQL injection is a really simple technique that can be used to breach security in vulnerable websites and applications. Bypassing login forms is only one of many things you can do with SQL injection, which is so dangerous that it has topped the OWASP Top 10 Risks for years.

To protect against SQL injection, use prepared statements to provide strongly-typed parameters in your SQL queries, and avoid dynamic SQL built directly by concatenating strings.

1 comment:

  1. Thanks your post was useful for me. I really appreciate.

    ReplyDelete