Understanding SQL Injection and Creating SQL Injection Proof ASP.NET Applications

• Download demo - 411.7 KB

Introduction

This article talk about what SQL injection is, how can that effect the security of our websites and what steps should be taken to create an ASP.NET application SQL injection proof.

Background

As ASP.NET developers, we often write dynamic SQL to perform some database operations. These dynamic SQL is some cases might be created by concatenating strings with user input. If we are not validating the user input and taking every input as is, then this kind of scenario poses a very serious problem of SQL injection.

SQL injection is the attack in which the user of the website will input some SQL code as input which would result in creating a SQL statement that developers didn't intend to write. These SQL statements could result in unauthorized access, revealing secret user information and sometimes it could even wipe out the entire data lying on the server.

Using the code

Getting to know SQL Injection

Let us take this discussion a little further by looking into the bad coding practices that will make the application prone to the SQL injection attacks. Let us create a simple table that contains username and password of the user for authentication.

Now I will create a small page that lets the user to enter his login credentials and get them validated against the Users table.

Note: Password should never be stored in plain text. This table contains password in plain text just for the sake of simplicity of this article.

The actual code that I will use to authenticate the user contains dynamic SQL that is being created by concatenating strings. This code will return true if the userid and password are found in the database otherwise false.  

public bool IsUserAuthenticated_Bad(string username, string password)
{
    DataTable result = null;
    try
    {
        using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["SampleDbConnectionString1"].ConnectionString))
        {
            using (SqlCommand cmd = con.CreateCommand())
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "select userID from Users where userID = '" + username + "' and password = '" + password + "'";
                
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    result = new DataTable();
                    da.Fill(result);

                    //check if any match is found
                    if (result.Rows.Count == 1)
                    {
                        // return true to indicate that userID and password are matched.
                        return true;
                    }
                }
            }
        }
    }
    catch (Exception ex)
    {
        //Pokemon exception handling
    }

    //user id not found, lets treat him as a guest        
    return false;
}

For all the normal users this code will work fine. I can even test it using userid as sampleuser and password as samplepwd and this will work fine. For any other data except this it should say that authentication failed(since this is the only record in the table). The query that will get generated to test this input will be:

select userID from Users where userID = 'sampleuser' and password = 'samplepwd'

Now let us try to inject some SQL into this page. Let me give hacker' or 1=1-- as username and anything in the password(even leave it empty). Now the resultant SQL for this will become:

select userID from Users where userID = 'hacker' or 1=1--' and password = ''

Now when we execute this query the 1=1 clause will always return true(and the password check is commented out. Now irrespective of whatever data user has entered this will SQL return a row making this function return true and in turn authenticating the user. So What I have done now is that I gained access to the website even when I didn't knew the valid user credentials.

How can I curb this problem is something we will look into details in some time. But before that let us also look at one more example of SQL injection just to get little more understanding.

In this second example we will assume that the malicious user somehow got hold of the database schema and then he is trying to manipulate the application to find some confidential information. Lets say we have a page that is supposed to show all the products that are assigned to a user in the organization.

Let us start by looking at the Product table.

Let us now look at the code that is retrieving this data:

public DataTable GetProductsAssigner_Bad(string userID)
{
    DataTable result = null;
    try
    {
        using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["SampleDbConnectionString1"].ConnectionString))
        {
            using (SqlCommand cmd = con.CreateCommand())
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "select * from Products where AssignedTo = '" + userID + "'";

                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    result = new DataTable();
                    da.Fill(result);
                }
            }
        }
    }
    catch (Exception ex)
    {
        //Pokemon exception handling
    }

    //user id not found, lets treat him as a guest        
    return result;
}

Now if I call this function with the proper data(as normal users would do) then this will show me the results. i.e. If I call this page for sampleuser the resulting query would be:

select * from Products where AssignedTo = 'sampleuser'

Now let me use this query string with this page: userID=' UNION SELECT 0 AS Expr1, password, userID FROM Users -- . Once this data is used with the current code this will show me all the username and passwords from the database. The reason will be quiet clear once we look into the resulting query of this input.

select * from Products where AssignedTo = '' UNION SELECT 0 AS Expr1, password, userID FROM Users --

Now we saw that how string concatenated dynamic SQL is prone to SQL injection. There are many other problems that could be created by injecting SQL. Imagine a scenario where the injected SQL is dropping tables or truncating all the tables. The problem in such cases would be catastrophic.

How to Prevent SQL Injection

ASP.NET provides us beautiful mechanism for prevention against the SQL injection. There are some thumb rules that should be followed in order to prevent injection attacks on our websites.

1. User input should never be trusted. It should always be validated
2. Dynamic SQL should never be created using string concatenations.
3. Always prefer using Stored Procedures. 
4. If dynamic SQL is needed it should be used with parametrized commands.
5. All sensitive and confidential information should be stored in encrypted.
6. The application should never use/access the DB with Administrator privileges. 

User input should never be trusted. It should always be validated

The basic thumb rule here is that the user input should never be trusted. First of all we should apply filters on all the input fields. If any field is supposed to take numbers then we should never accept alphabets in that. Secondly, All the inputs should be validated against a regular expression so that no SQL characters and SQL command keywords are passed to the database.

Both this filtration and validation should be done at client side using JavaScript. It would suffice for the normal user. Malicious users cans till bypass the client side validations. So to curb that all the validations should be done at server side too.

Dynamic SQL should never be created using string concatenations.

If we have dynamic SQL being created using string concatenations then we are always at the risk of getting some SQL that we are not supposed to use with the application. It is advisable to avoid the string concatenations altogether.

Always prefer using Stored Procedures.

Stored procedures are the best way of performing the DB operations. We can always be sure of that no bad SQL is being generated if we are using stored procedures. Let us create a Stored procedure for the database access required for our login page and see what is the right way of doing the database operation using stored procedure.

CREATE PROCEDURE dbo.CheckUser 	
	(
	@userID varchar(20),
	@password varchar(16)
	)
AS
	select userID from Users where userID = @userID and password = @password
	RETURN

And now lets have a good version in our code using this stored procedure.

public bool IsUserAuthenticated_Good(string username, string password)
{
    DataTable result = null;
    try
    {
        using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["SampleDbConnectionString1"].ConnectionString))
        {
            using (SqlCommand cmd = con.CreateCommand())
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "CheckUser";
                cmd.Parameters.Add(new SqlParameter("@userID", username));
                cmd.Parameters.Add(new SqlParameter("@password", password));

                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    result = new DataTable();
                    da.Fill(result);

                    //check if any match is found
                    if (result.Rows.Count == 1)
                    {
                        // return true to indicate that userID and password are matched.
                        return true;
                    }
                }
            }
        }
    }
    catch (Exception ex)
    {
        //Pokemon exception handling
    }

    //user id not found, lets treat him as a guest        
    return false;
}

If dynamic SQL is needed it should be used with parametrized commands.

If we still find our self needing the dynamic SQL in code then parametrized commands are the best way of performing such dynamic SQL business. This way we can always be sure of that no bad SQL is being generated. Let us create a parametrized command for the database access required for our Product page and see what is the right way of doing the database operation.

public DataTable GetProductsAssigner_Good(string userID)
{
    DataTable result = null;
    try
    {
        using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["SampleDbConnectionString1"].ConnectionString))
        {
            using (SqlCommand cmd = con.CreateCommand())
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "select * from Products where AssignedTo = @userID";
                cmd.Parameters.Add(new SqlParameter("@userID", userID));

                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    result = new DataTable();
                    da.Fill(result);
                }
            }
        }
    }
    catch (Exception ex)
    {
        //Pokemon exception handling
    }

    //user id not found, lets treat him as a guest        
    return result;
}

All sensitive and confidential information should be stored in encrypted.

All the sensitive information should be stored encrypted in the database. The benefit of having this is that even if somehow the user get hold of the data he will only be able to see the encrypted values which are not easy to use for someone who doesn't know the encryption technique used by the application.

The application should never use/access the DB with Administrator privileges.

This will make sure that even if the bad SQL is being passed to the Database by some injections, the database will not allow any catastrophic actions like dropping table.

Note: Refer the sample application attached to see the working examples SQL injection and how to curb them using parametrized commands and stored procedures.

Point of interest 

This is a very basic article on SQL injection. I have specifically focused on ASP.NET applications but same concept will apply for any ADO.NET application. This article is meant for the beginner's who know nothing or too little about SQL injection and making the applications SQL injection proof. I hope this has been informative.

History

• 14 September 2012: First version
• 09 January 2013: Bug fix in sample application.