OWASP Top 10 for ASP.net Core – SQL Injection

This article is part of a series on the OWASP Top 10 for ASP.net Core. See below for links to other articles in the series.

OWASP, or the Open Web Application Security Project, is a non profit organization whose purpose is to promote secure web application development and design. While they run different workshops and events all over the world, you have probably heard of them because of the “OWASP Top Ten” project. Every few years, OWASP publishes a top 10 list of the most critical web security risks. While by no means does it mean if you can check off these 10 items, your website is now secure, but it definitely covers your bases for the most common attack vectors on the web.

When I first started programming and heard about OWASP, the hardest thing for me was trying to put that into practical terms. When someone started talking about “CSRF” for example, I wanted to know what does that actually look like in .NET terms, What are the basic principles to protect myself from it, and what (if any) built in systems in .NET are there? This 10 part article series will attempt to answer these questions within the realm of ASP.net Core. By no means will you reach the end and suddenly be immune to all web attacks, but it should hopefully help you understand the OWASP Top 10 from an ASP.net Core standpoint.

Another point to note, I’m going to base these top 10 off the OWASP 2017 Release Candidate. These have not yet been accepted as the official top 10 for 2017, and if they change I will be sure to add additional articles to cover everything off.

So without further ado, let’s get started! Our first item on the list is SQL Injection.

How Does SQL Injection Work?

SQL Injection works by modifying an input parameter that is known to be passed into a raw SQL statement, in a way that the SQL statement executed is very different to what is intended. That might sound like a whole lot of mumbo jumbo, so let’s take a working example.

If you would like the entire working code project on your machine for this post, you can grab everything from Github here. You can still follow along without the code as I’ll post code samples and screenshots the entire way through.

First let’s create a database with two tables. The first table titled “NonsensitiveDataTable” contains some data that we don’t mind sharing out to the user. The second table, aptly titled “SensitiveDataTable” contains users credit cards and social security numbers. Admittedly, a little extreme but bare with me for a bit. Here is how the tables look in SQL Server.

Now let’s say we have an API endpoint. All it does is take a parameter of an “Id”, and we use this to request data from our NonSensitiveDataTable.

[HttpGet]
[Route("nonsensitive")]
public string GetNonSensitiveDataById()
{
	using (SqlConnection connection = new SqlConnection(_configuration.GetValue<string>("ConnectionString")))
	{
		connection.Open();
		SqlCommand command = new SqlCommand($"SELECT * FROM NonSensitiveDataTable WHERE Id = {Request.Query["id"]}", connection);
		using (var reader = command.ExecuteReader())
		{
			if (reader.Read())
			{
				string returnString = string.Empty;
				returnString += $"Name : {reader["Name"]}. ";
				returnString += $"Description : {reader["Description"]}";
				return returnString;
			}
			else
			{
				return string.Empty;
			}
		}
	}
}

This is a rather extreme example and coded rather poorly, but it illustrates the point. Hitting this endpoint to get data, we seem to have no issue.

But just looking at the code we can see something suspect. Notably this line here :

SqlCommand command = new SqlCommand($"SELECT * FROM NonSensitiveDataTable WHERE Id = {Request.Query["id"]}", connection);

We are passing the Id parameter directly into our SQL Statement. People could type anything in there right? Well, we know that the record of “Mark Twain” is returned when we request the ID of 2. But let’s try this :

Wow…. So why does adding the test “OR id = 1” suddenly change everything. The answer lies in our code above. When we simply pass the id of “2” to our code, it ends up executing this statement.

SELECT * FROM NonSensitiveDataTable WHERE Id = 2

But when we pass in our OR statement, it actually ends up reading like so :

SELECT * FROM NonSensitiveDataTable WHERE Id = 2 OR Id = 1

So here we have managed to modify what SQL statements are getting executed simply by changing a query parameter. But no big deal right? The user will get the wrong data and that’s the end of the that. Well, since we know we can modify the SQL statement. Let’s try something funky. Experience the future of gaming with the convenience of casino Bitcoin payment . Our website offers a comprehensive guide to the top online casinos that accept Bitcoin, combining security, speed, and ease of transactions. Embrace the digital revolution in gaming and discover the benefits of using Bitcoin for your casino payments. Visit us now to explore a world of hassle-free, secure gaming experiences where Bitcoin enhances your play! Let’s try this URL :

Uh oh… That doesn’t look good. The query that ran ended up looking like this :

SELECT * FROM NonSensitiveDataTable WHERE Id = 999 UNION SELECT * FROM SensitiveDataTable

It says, find me the NonSensitiveDataTable where the ID is 999, and while you are at it, link it up to the data from the SensitiveDataTable. Because there was no record with the ID of 999, we jumped immediately to blurting out the SensitiveDataTable, in this case leaking out credit card info.

Now the first thing you are going to ask is… Do I not need to know that the “SensitiveDataTable” exists before I go and run this query. The answer is yes and no. Quite often you will find guesswork can do a tonne for you. If this is an eCommerce site the changes are they will have a “Customers” table and a “Orders” table etc. SQL Injection isn’t as always as easy as copy and pasting in a URL and suddenly you have the keys to the kingdom.

However, let’s try one more thing. Let’s try to get the following query going. It will tell us table names that are in the database.

SELECT * FROM NonSensitiveDataTable WHERE Id = 999 UNION SELECT 1 as ID, Name as NAME, Name as Description FROM sys.Tables WHERE name <> 'NonSensitiveDataTable'

There you have it, a little less guesswork. We could actually sit there for days throwing commands in here. You will often find SQL Injection “cheatsheets” out on the web with a whole heap of SQL commands to try out. Often with SQL Injections it’s a case of throwing things against the wall and seeing what sticks.

I’ll leave you with one final query you “could” try and run. Let’s say you get frustrated trying to grab data, and you just want to ruin someone’s day. Imagine running the following command :

SELECT * FROM NonSensitiveDataTable WHERE Id = 999; DROP TABLE SensitiveDataTable

Here we have just given up, and we are just going to drop a table. Maybe we even grabbed all the data we could, and we just want to make life miserable for the resident DBA. This would be one way to do it for sure!

Now we’ve seen what an SQL Injection looks like, let’s get on to protecting ourselves!

Sanitize Your Inputs

You will quite often find that the first comment on any article describing a recent SQL Injection attack is to “Always sanitize your inputs” (Quickly followed by “Using parameters in your queries is better!” – but more on that later).  Sanitizing your inputs could mean a couple of things, so let’s run through them.

Casting To A Non String Type

In our example code, Because we know our Id is supposed to be an int. Let’s just case it to one.

int id = int.Parse(Request.Query["id"]);
SqlCommand command = new SqlCommand($"SELECT * FROM NonSensitiveDataTable WHERE Id = {id}", connection);

Perfect! Now if someone tries to add further info to our query string, it’s not going to be parsed to an int and we will be saved!

An even easier example in ASP.net Core would of course just allowing our routing do the work for us.

[HttpGet]
[Route("nonsensitiveroute/{id}")]
public string GetNonSensitiveDataByIdWithRoute(int id)

Now we don’t even need to do any manual parsing. ASP.net Core routing will take care of it for us!

But of course this only works if your query actually is against an integer column. If we really do need to pass strings, what then?

Whitelist/Blacklist/Character Replacement

Honestly, I don’t want to go too deep into this one because I think it’s a pretty error prone way to deal with SQL Injection. But if you must, you can run strings against a whitelist or blacklist of known good values before passing to SQL. Or you can replace all instances of certain characters (Quotes, semicolons etc). But all of these rely on you being one step ahead of an attack. And you pitting your knowledge of the intricacies of the SQL language against someone else.

While languages like PHP have inbuilt functions for “escaping” SQL strings, .NET core does not. Further, simply escaping quotes does not protect you from the root cause of SQL Injection anyway.

Sanitizing your inputs requires you to “remember” to do it. It’s not something that becomes a pattern to follow. It works. But it’s not the ideal solution.

Parameterized Queries

Using Parameters in your SQL queries is going to be your go-to when defending against SQL Injection attacks. Even if you are using an ORM of some sort (Talked about a bit below), behind the scenes chances are they will be using parameterized queries.

So how can we use them in our example project? We are going to change things up a bit and instead query on the “name” field of our NonSensitiveDataTable. Just because it affords us a little bit more flexibility.

[HttpGet]
[Route("nonsensitivewithparam")]
public string GetNonSensitiveDataByNameWithParam()
{
	using (SqlConnection connection = new SqlConnection(_configuration.GetValue<string>("ConnectionString")))
	{
		connection.Open();
		SqlCommand command = new SqlCommand($"SELECT * FROM NonSensitiveDataTable WHERE Name = @name", connection);
		command.Parameters.AddWithValue("@name", Request.Query["name"].ToString());
		using (var reader = command.ExecuteReader())
		{
			if (reader.Read())
			{
				string returnString = string.Empty;
				returnString += $"Name : {reader["Name"]}. ";
				returnString += $"Description : {reader["Description"]}";
				return returnString;
			}
			else
			{
				return string.Empty;
			}
		}
	}
}

Can you see where we have added parameters to our query? So how does this work? Well, with the aid of the SQL Profiler, the actual SQL that is being sent looks like this :

exec sp_executesql N'SELECT * FROM NonSensitiveDataTable WHERE Name = @name'
,N'@name nvarchar(12)'
,@name=N'Bart Simpson'

Wow… That’s a heck of a lot different. So what’s going on here? Essentially we are sending the query, but saying “Later on, I will tell you what data to query against”. We pass the exact value we want to query against outside the actual SELECT statement. In this way, our original query is kept intact and it doesn’t matter what a user types into the query string.

The best thing about using Parameters is that they become an easy pattern to follow. You don’t have to “remember” to escape certain strings, or “remember” to use a whitelist. It just works.

Stored Procedures

SQL Stored Procedures are another good way of avoiding SQL Injection attacks. Though stored procedures seem to be out of favour with developers these days, they work similarly to parameterized queries in that you are passing your SELECT statement and your query data in two different “lots” if you will. Let’s quickly create a stored procedure to try out (If you are using the example project from GIT, you already have this SP in your database and you don’t need to run the following).

CREATE PROCEDURE SP_GetNonSensitiveDataByName
	@Name nvarchar(MAX)
AS
BEGIN
	SET NOCOUNT ON;
	SELECT * FROM NonSensitiveDataTable WHERE Name = @Name
END

Let’s create an API endpoint that will run this.

[HttpGet]
[Route("nonsensitivewithsp")]
public string GetNonSensitiveDataByNameWithSP()
{
	using (SqlConnection connection = new SqlConnection(_configuration.GetValue<string>("ConnectionString")))
	{
		connection.Open();
		SqlCommand command = new SqlCommand("SP_GetNonSensitiveDataByName", connection);
		command.CommandType = System.Data.CommandType.StoredProcedure;
		command.Parameters.AddWithValue("@name", Request.Query["name"].ToString());
		using (var reader = command.ExecuteReader())
		{
			if (reader.Read())
			{
				string returnString = string.Empty;
				returnString += $"Name : {reader["Name"]}. ";
				returnString += $"Description : {reader["Description"]}";
				return returnString;
			}
			else
			{
				return string.Empty;
			}
		}
	}
}

When we hit this endpoint with the SQL Profiler running, we can see the following getting run.

exec SP_GetNonSensitiveDataByName @name=N'bart simpson'

As you can see, it’s very similar to our parameterized query above, where the actual details of our query are sent separately. While it’s rare to see an entire project built around stored procedures these days, they do protect you against SQL Injection attacks.

Use Of An ORM

Now this next part is an interesting part of mitigating SQL Injection attacks. Because in one sense, it becomes a one stop shop for protection. But the reason I’ve put it last is because I think it’s better to understand what’s going on under the hood of an ORM that protects you, and that’s typically parameterized queries.

If you use something like Entity Framework. When you run a Linq query to fetch your data, any linq “Where” statement will be packaged as a parameter query and sent to SQL server. This means you really need to go out of your way to open yourself up to SQL Injection, however it’s not impossible! Almost all ORM’s are able to send raw SQL queries if you really want to. Take a look at this article from Microsoft on sending Raw SQL through Entity Framework Core here. At the very least, using an ORM makes SQL Injection the “default” if you will, rather than something extra added on top.

Lowest Possible Permissions

Remember our drop table command? Just incase you forgot, here it is below :

SELECT * FROM NonSensitiveDataTable WHERE Id = 999; DROP TABLE SensitiveDataTable

Will our website ever actually need to drop a table? Unlikely. And yet in our scenario we’ve given it the keys to the kingdom to do whatever it likes. Giving fine grain permissions or the lowest possible permission level is our final step to “stopping” SQL Injection attacks. I’ve put “stopping” in quotes because in reality we are still vulnerable to sensitive data breaches, but our attacker atleast cannot drop tables (They can however still run delete commands!).

Most SQL systems (MYSQL, Postgres, MSSQL) have inbuilt SQL Roles that allow just simple reads and writes to get through, but not to modify the actual table schema. These roles should be used as much as possible to limit any possible attack.

Summary

As we’ve seen, SQL Injections can be pretty brutal about leaking sensitive data, and even destroying it. But we’ve also seen that ASP.net Core already has ways we can protect ourselves. While relying on input parsing/type conversion will get us part of the way there, parameterized queries are really our answer. And best of all, there are very few projects these days where you won’t be using an ORM which gives us protection right out of the box.

In our next article in the series, we will be tackling the pretty broad topic of “Broken Authentication and Session Management”. It’s a pretty open ended topic and is more about “practices” related to authentication and sessions rather than a straight forward “Here’s the issue”. I’ll see you then.