SQL Injection Exercise

Source code for this exercise can be dowloaded below:

First let’s create a database with two tables. In order to do that, simply run the "src/CreateDatabase.txt" in SQL Server Object Explorer to create the required items, and then open the project in Visual Studio.

​

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.

You might need to modify the appsettings.json file in the OWASPTop10.SqlInjection.Web solution in order to point to the database. Here is the suggested updated connection string:

"ConnectionString": "Server=(localdb)\MSSQLLocalDB;Database=OWASP_TOP10_SQLInjection;Integrated Security=true;",

The Web solution is essentially an API endpoint that takes a parameter of an “Id”, and use it to request data from the database. The Home Controller source code is shown below:

[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;
 }
 }
 }
}

So if you access the endpoint to get data using URL and parameters below, you get the output displayed.

However, looking at the source code there is something suspicious.

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

The Id parameter is passed directly into the SQL Statement. This is really bad and will make the Solution vulnerable to SQL Injection. Let's try accessing the endpoint again but with "malcrafted" input:

So we have managed to modify the SQL statement by simply changing a query parameter and getting a different output from the "NonsensitiveDataTable". If we want to take this further, let’s modify the URL to access data in the "SensitiveDataTable".

The SQL query that ended up running looked like the following:

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

Essentially is searching for data from the NonSensitiveDataTable where the ID is 999, and then perform an UNION with the data from the SensitiveDataTable. Because there was no record with the ID of 999, the query performed the UNION with the SensitiveDataTable, in this case leaking out credit card information.

You might be wondering whether you need to know that the “SensitiveDataTable” exists before crafting and running this query. It depends. Quite often you will find guesswork can do a tonne for you. If this is an eCommerce site, probably there are going to be “Customers”, “Orders” tables and so on.

We could also take a different approach and craft a query that will tell us the various tables available in the database. Have a look at the following SQL statement:

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

As you have seen, armed with some SQL skills and enough time you might be able to perform quite sophisticated SQL injection attacks. Now let's look at how we could tackle this issue.