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