Bypassing a Login Form using SQL Injection

This article was originally posted as “C# Security: Bypassing a Login Form using SQL Injection” on 5th January 2014 at Programmer’s Ranch. The article is based on ASP .NET Web Forms and ADO .NET, and the code example was originally written using Visual Studio Express for Web 2013 and SQL Server 2012 Express. This updated version removes references to particular software versions, adds syntax highlighting, and includes other simple edits where necessary.

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

Setting up the database

sqlinj-newdatabase

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.

sqlinj-newquery

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:

sqlinj-createtable

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 never a good idea – see my article “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:

sqlinj-newproject

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:

sqlinj-loginform

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:

sqlinj-injected-sql

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:

sqlinj-injection-failed

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.

14 thoughts on “Bypassing a Login Form using SQL Injection”

  1. O boy. This is beginner code.
    Nobody uses varchar for password in db. It is binary contains password hash somethink like salted SHA1.

    People uses parameter based qry to avoid sql attack instead of plain text sql. Moreover pages are form authenticated.

    1. This article is indeed targeted at beginners. It explains what SQL injection is and how to avoid it, as you’d realise if you bothered to read it.

  2. My second day at first job and my tech lead explained me not do string concatination for SQL command EVER, and then told me what SQl injection was over beers later that day. And that was my second day of coding in C# using SQL.

    Yep, good old days. I think tools like resharper points you at such blunders.

  3. At the risk of sounding impertinent, how does this appear not simply transfer the problem a step?

    What prevents the hacker from sending in the same information for password and receiving the same result?

    1. Using parameterised queries handles the input validation for you, e.g. apostrophes are doubled. Thus if you submit the input it won’t work, as I show towards the end of the article.

  4. Great article on SQL injection.

    But it always frustrates me when I see ‘simple’ tables like this – any beginners out there should be keeping in mind that a password and email should be encrypted so even if the user can get access to the table, they wouldn’t see the passwords in a readable form. When you hear stories of companies having their users, emails, and passwords exposed then you know they didn’t encrypt them.

    1. Passwords should be hashed, not encrypted. I have a couple of articles that explain how it is done (one of them is linked in this article), but it is simply beyond the scope of this article.

  5. this query ”String query = “select count(*) from users where username = ….” and then using ExecuteScalar() … cooomeeee oooon… why so complicating something so simple… just check is record exist or not with specific usn and pwd (gave by condition)… you can done with dataadapter or datareader and check is there any record or not… and, what is purpose of username and password if there is more than one unique username… if there is more than one, say your app good bay.

    1. Surely there are better things you can do with your life than pick on details in a beginner article to show how clever you are. You could almost manage that, if only you knew what you were talking about.

Leave a Reply

Your email address will not be published. Required fields are marked *