Tag Archives: ADO .NET

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.

Retrieving Table Metadata from SQL Server Catalog Views

Introduction

All database systems that I’ve worked with have some sort of system tables that provide information about the tables, columns, indexes, constraints, etc in a database. SQL Server is no exception; in fact there are different ways of querying its System Catalog. We’re going to look at one of these: Catalog Views, which can be queried easily.

Being able to retrieve this metadata can be very useful in various situations, such as developing tools to work with and visualize data (like SQL Server Management Studio), or automating rote tasks (such as creating Entity classes for each table).

List of Table Names

Querying catalog views is as easy as querying any table. The easiest thing you can do is get a list of table names. Use this query:

select *
from sys.tables;

Here’s the result:

systables

So if all you want is the name of the table, just refine the query to select only that:

select name
from sys.tables;

Retrieving data using ADO .NET

It is easy to run simple queries using the good old ADO .NET technology. Below is a sample you could use to retrieve the table names from a C# application.

        static void Main(string[] args)
        {
            const string connStr = @"server=.\SQLEXPRESS;database=BookStore;Trusted_Connection=True;";

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

                using (var command = new SqlCommand())
                {
                    command.Connection = conn;

                    command.CommandText = "select name from sys.tables;";

                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            string name = reader["name"].ToString();
                            Console.WriteLine(name);
                        }
                    }
                }
            }

            Console.ReadLine();
        }

This code can be similarly adapted to fetch additional data from the queries we will see next.

More Advanced Queries

Retrieving other table metadata such as indexes, foreign keys, etc is not as straightforward as retrieving table names. However, you can get queries for just about any metadata you need from the Querying the SQL Server System Catalog FAQ.

Here’s the query to get column data types:

SELECT c.name AS column_name
    ,c.column_id
    ,SCHEMA_NAME(t.schema_id) AS type_schema
    ,t.name AS type_name
    ,t.is_user_defined
    ,t.is_assembly_type
    ,c.max_length
    ,c.precision
    ,c.scale
FROM sys.columns AS c 
JOIN sys.types AS t ON c.user_type_id=t.user_type_id
WHERE c.object_id = OBJECT_ID('<schema_name.table_name>')
ORDER BY c.column_id;

Here’s the result:

syscolumns

This is how you get the indexes for a table (which may include primary keys):

SELECT i.name AS index_name
    ,i.type_desc
    ,is_unique
    ,ds.type_desc AS filegroup_or_partition_scheme
    ,ds.name AS filegroup_or_partition_scheme_name
    ,ignore_dup_key
    ,is_primary_key
    ,is_unique_constraint
    ,fill_factor
    ,is_padded
    ,is_disabled
    ,allow_row_locks
    ,allow_page_locks
FROM sys.indexes AS i
INNER JOIN sys.data_spaces AS ds ON i.data_space_id = ds.data_space_id
WHERE is_hypothetical = 0 AND i.index_id <> 0 
AND i.object_id = OBJECT_ID('<schema_name.table_name>');

Here’s an example result:

sysindexes

And finally, here’s how you get info on the foreign keys:

SELECT 
    f.name AS foreign_key_name
   ,OBJECT_NAME(f.parent_object_id) AS table_name
   ,COL_NAME(fc.parent_object_id, fc.parent_column_id) AS constraint_column_name
   ,OBJECT_NAME (f.referenced_object_id) AS referenced_object
   ,COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS referenced_column_name
   ,is_disabled
   ,delete_referential_action_desc
   ,update_referential_action_desc
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc 
   ON f.object_id = fc.constraint_object_id 
WHERE f.parent_object_id = OBJECT_ID('<schema_name.table_name>');

Here’s the result of that:

sysforeign_keys

So even though these queries aren’t trivial to cook up, you can find just about anything you need from the Querying the SQL Server System Catalog FAQ, and just adapt it from there.

One Query to Rule Them

If you’re going to do something like code generation, you probably want to build one query that retrieves all the above metadata in one go. You can do that by combining the above queries. Fortunately, I’ve done that for you. Here you go:

SELECT
	-- columns / data types
	c.name AS column_name
    ,c.column_id
    ,SCHEMA_NAME(t.schema_id) AS type_schema
    ,t.name AS type_name
    ,c.max_length
    ,c.precision
    ,c.scale
	-- primary key / indexes
	,i.name AS index_name
    ,is_identity
	,i.is_primary_key
	-- foreign key
    ,f.name AS foreign_key_name
   ,OBJECT_NAME (f.referenced_object_id) AS referenced_object
   ,COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS referenced_column_name
FROM sys.columns AS c 
INNER JOIN sys.types AS t
	ON c.user_type_id=t.user_type_id
LEFT OUTER JOIN sys.index_columns AS ic
	ON ic.object_id = c.object_id
	AND c.column_id = ic.column_id
LEFT OUTER JOIN sys.indexes AS i
	ON i.object_id = ic.object_id
	AND i.index_id = ic.index_id
LEFT OUTER JOIN sys.foreign_key_columns AS fc
	ON fc.parent_object_id = c.object_id
	AND COL_NAME(fc.parent_object_id, fc.parent_column_id) = c.name
LEFT OUTER JOIN sys.foreign_keys AS f
	ON f.parent_object_id = c.object_id
	AND fc.constraint_object_id = f.object_id
WHERE c.object_id = OBJECT_ID('dbo.Book')
ORDER BY c.column_id;

Here’s what you’ll get:

sysquerycombined

That includes column names, column types (along with stuff like decimal precision), indexes, primary keys, auto-increment (that’s is_identity), and foreign key info (constraint name, referenced table, and referenced column).

I’ve only tested this on a very simple scenario, so I’m pretty sure there are improvements to be made. While this can be considered a starting point, feedback is more than welcome.

Setting Connection Strings at Runtime with Entity Framework 5.0, Database First, VS2012

This article was originally posted here at Programmer’s Ranch on Saturday 16th November 2013. The syntax highlighting was added when the article was migrated here.

Hi everyone! 🙂

This article deals with how to solve the problem of building and setting an Entity Framework connection string at runtime, based on a database-first approach (i.e. you have generated an Entity Data Model based on an existing database). You are expected to be familiar with ADO .NET and the Entity Framework. The first part of the article deals with setting up an Entity Data Model and simple interactions with it; this should appeal to all readers. The second part deals with the custom connection string issue, and will be helpful only to those who have actually run into that problem.

We’re going to be using Visual Studio 2012, and Entity Framework 5.0. Start a new Console Application so that you can follow along.

Setting up the database

You can use whatever database you want, but in my case I’m using SQL Server Compact edition (SQLCE). If you’re using something else and already have a database, you can just skip over this section.

Unlike many of the more popular databases such as SQL Server and MySQL, SQLCE is not a server and stores its data in a file with .sdf extension. This file can be queried and updated using regular SQL, but is not designed to handle things like concurrent users – something which isn’t a problem in our case. Such file-based databases are called embedded databases.

If you have Visual Studio, then you most likely already have SQLCE installed. Look for it in “C:\Program Files (x86)\Microsoft SQL Server Compact Edition\v4.0“. Under the Desktop orPrivate folders you’ll find a file called System.Data.SqlServerCe.dll which we need to interact with SQLCE. Add a reference to it from your Console Application.

Now, we’re going to create the database and a simple one-table schema. We’ll use good old ADO.NET for that. We’ll create the database only if it doesn’t exist already. First, add the following usings at the top of Program.cs:

using System.IO;
using System.Data.SqlServerCe;

In Main(), add the following:

String filename = "people.sdf";
String connStr = "Data Source=" + filename;

Since SQLCE works with just a file, we can create a basic connection string using just the name of the file we’re working with.

The following code actually creates the database and a single table called person.

            try
            {
                // create database if it doesn't exist already

                if (!File.Exists(filename))
                {
                    // create the actual database file

                    using (SqlCeEngine engine = new SqlCeEngine(connStr))
                    {
                        engine.CreateDatabase();
                    }

                    // create the table schema

                    using (SqlCeConnection conn = new SqlCeConnection(connStr))
                    {
                        conn.Open();

                        String sql = @"create table person(
                                       id int identity not null primary key,
                                       name nvarchar(20),
                                       surname nvarchar(30)
                                   );";

                        using (SqlCeCommand command = new SqlCeCommand())
                        {
                            command.CommandText = sql;
                            command.Connection = conn;
                            int result = command.ExecuteNonQuery();
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
            }

We first use SqlCeEngine to create the database file. Then we use ADO .NET to create the person table. Each row will have an auto-incrementing id (primary key), as well as a name and surname. Note that SQLCE does not support the varchar type, so we have to use nvarchar (Unicode) instead.

If you now build and run the application, you should find a people.sdf file in the bin\Debug folder. We’ll use that to create an Entity Data Model for the Entity Framework.

Creating the Data Model

Right click on the project and select Add -> New Item…:

cs-efconnstr-addnewitem

From the Data category, select ADO.NET Entity Data Model. You can choose a name for it, or just leave it as the default Model1.edmx; it doesn’t really matter.

cs-efconnstr-addedm

Click The Add button. This brings up the Entity Data Model Wizard.

cs-efconnstr-edmwiz1

The “Generate from database” option is the default selection, so just click Next.

cs-efconnstr-edmwiz2

Hit the New Connection… button to bring up the Connection Properties window.

cs-efconnstr-connprop

If SQL Server Compact 4.0 is not already selected as the Data source, click the Change… button and select it from the Change Data Source window:

cs-efconnstr-changeds

Back in the Connection Properties window, click the Browse… button and locate the people.sdf file in your bin\Debug folder that we generated in the previous section. Leave the Password field empty, and click Test Connection. If all is well, you’ll get a message box saying that the test succeeded.

Once you click OK, the Entity Data Model Wizard should become populated with a connection string and a default name for the model:

cs-efconnstr-edmwiz3

When you click Next, you’ll get the following message:

cs-efconnstr-localdata

Just click Yes and get on with it. In the next step, import the person table into your model by ticking the checkbox next to it:

cs-efconnstr-edmwiz4

Click Finish. The files for your model are added to the project. You may also get the following warning:

cs-efconnstr-security-warning

You don’t have to worry about it. Just click OK. If you click Cancel instead, you won’t have the necessary autogenerated code that you need for this project.

Interacting with the database using the Entity Framework

After the database-creation code from the first section, and before the end of the try scope, add the following code:

// interact with the database

using (peopleEntities db = new peopleEntities())
{
    db.people.Add(new person() { name = "John", surname = "Smith" });
    db.SaveChanges();

    foreach (person p in db.people)
    {
        Console.WriteLine("{0} {1} {2}", p.id, p.name, p.surname);
    }
}

Here, we create an instance of our entity context (peopleEntities) and then use it to interact with the database. We add a new row to the person table, and then commit the change via db.SaveChanges(). Finally, We retrieve all rows from the table and display them.

Also, add the following at the end of the Main() method so that we can see the output:

Console.ReadLine();

Run the program by pressing F5. The output shows that a row was indeed added:

cs-efconnstr-output1

The Entity Framework knows where to find the database because it has a connection string in the App.config file:

  <connectionStrings>
    <add name="peopleEntities" connectionString="metadata=res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl;provider=System.Data.SqlServerCe.4.0;provider connection string=&quot;data source=|DataDirectory|\people.sdf&quot;" providerName="System.Data.EntityClient" />
  </connectionStrings>

This might be good enough in some situations, but other times, we might want to build such connection string in code and ask the Entity Framework to work with it. A reason for this might be because the connection string contains a password, and you want to obtain it from an encrypted source. The following two sections illustrate how this is done.

Building a raw Entity Framework connection string

Let’s start out by commenting out the connection string in the App.config file:

  <connectionStrings>
    <!--
    <add name="peopleEntities" connectionString="metadata=res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl;provider=System.Data.SqlServerCe.4.0;provider connection string=&quot;data source=|DataDirectory|\people.sdf&quot;" providerName="System.Data.EntityClient" />
    -->
  </connectionStrings>

If you try running the program now, you’ll get a nice exception.

Now, what we want to do is add the connection string into our code and pass it to the entity context (the peopleEntities). So before our Entity Framework code (which starts with using (peopleEntities…), add the following:

String entityConnStr = @"metadata=res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl;provider=System.Data.SqlServerCe.4.0;provider connection string=&amp;quot;data source=|DataDirectory|\people.sdf&quot;";

If you now try to pass this connection string to the peopleEntities constructor, you’ll realise that you can’t. You can see why if you expand Model1.edmx and then Model1.Context.tt in Solution Explorer, and finally open the Model1.Context.cs file:

cs-efconnstr-dbcontext

The peopleEntities class has only a parameterless constructor, and it calls the constructor of DbContext with the connection string name defined in App.config. The DbContext constructor may accept a connection string instead, but we have no way of passing it through peopleEntities directly.

While you could add another constructor to peopleEntities, it is never a good idea to modify autogenerated code. If you regenerate the model, any code you add would be lost. Fortunately, however, peopleEntities is a partial class, which means we can add implementation to it in a separate file (see this question and this other question on Stack Overflow).

Add a new class and name it peopleEntities. Add the following at the top:

using System.Data.Entity;

Implement the class as follows:

    public partial class peopleEntities : DbContext
    {
        public peopleEntities(String connectionString)
            : base(connectionString)
        {

        }
    }

We can now modify our instantiation of peopleEntities to use our connection string as defined in code:

using (peopleEntities db = new peopleEntities(entityConnStr))

Since we are using a partial class defined in a separate file, any changes to the model will cause the autogenerated peopleEntities to be recreated, but will not touch the code we added in peopleEntities.cs.

When we run the program, we now get a very nice exception (though different from what we got right after commenting out the connection string in App.config):

cs-efconnstr-output2

Apparently this happens because of the &quot; values, which are necessary in XML files but cause problems when supplied in a String literal in code. We can replace them with single quotes instead, as follows:

String entityConnStr = @"metadata=res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl;provider=System.Data.SqlServerCe.4.0;provider connection string='data source=|DataDirectory|\people.sdf'";

If we run the program now, it works fine, and a new row is added and retrieved:

cs-efconnstr-output3

Using EntityConnectionStringBuilder

You’ll notice that the connection string we’ve been using is made up of three parts: metadata, provider, and the provider connection string that we normally use with ADO.NET.

We can use a class called EntityConnectionStringBuilder to provide these values separately and build a connection string. Using this approach avoids the problem with quotes illustrated at the end of the previous section.

First, remove or comment out the entityConnStr variable we have been using so far.

Then add the following near the top of Program.cs:

using System.Data.EntityClient;

Finally, add the following code instead of the connection string code we just removed:

EntityConnectionStringBuilder csb = new EntityConnectionStringBuilder();
csb.Metadata = "res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl";
csb.Provider = "System.Data.SqlServerCe.4.0";
csb.ProviderConnectionString = "data source=people.sdf";
String entityConnStr = csb.ToString();

When you run the program, it should work just as well:

cs-efconnstr-output4

Summary

This article covered quite a bit of ground:

  • We first used the SqlceEngine and ADO .NET to create a SQL Server Compact database.
  • We then created an Entity Data Model for this database.
  • We added some code to add rows and retrieve data using the Entity Framework.
  • We provided the Entity Framework with a raw connection string in code. To do this, we used a partial class to add a new constructor to the entity context class that can pass the connection string to the parent DbContext. We also observed a problem with using &quot; in the connection string, and solved it by using single quotes instead.
  • We used EntityConnectionStringBuilder to build a connection string from its constituent parts, and in doing so completely avoided the &quot; problem.

I hope you found this useful. Feel free to leave any feedback in the comments below. Check back for more articles! 🙂