This book was written and designed to use with Visual Studio Code, Git, and .NET 5.0. Code examples in recipes are presented in ASP.NET Core Razor pages. The code exercises have been tested in a Windows environment but should work in Linux-based operating systems with some minor differences. The sample solution also uses SQLite as the database engine for a more simplified setup. MongoDB is required to be able to test the recipes for NoSQL injection. A tool that can open and browse SQLite databases such as the DB Browser for SQLite is also required. You can find the code for this chapter at https://github.com/PacktPublishing/ASP.NET-Core-Secure-Coding-Cookbook/tree/main/Chapter02.

ASP.NET Core web applications interact with databases to store data and records. We use Standard Query Language (SQL) to communicate with a Database Management System (DBMS) to access and manage data. These queries are composed utilizing the programming language, platform, or library of choice, but the code to generate these queries can be written insecurely.

A developer can write code that produces a dynamic SQL by concatenating strings along with untrusted user input. Without proper countermeasures, a malicious actor can inject suspicious commands into the input string, thereby changing the query's intent, or execute an arbitrary SQL. Identified as SQL Injection, this vulnerability in code still prevails in web applications today.

Entity Framework Core (EF Core) is a popular Object-Relational Mapping (ORM) framework of choice for ASP.NET Core developers. This framework is cross-platform, and its ease of use allows developers to instantly model and query data into objects. Nevertheless, ORM frameworks such as EF Core can still be misused.

In this recipe, we will execute a simple SQL injection to exploit the vulnerability, locate the security bug, and remediate the risk by rewriting a more secure version of the code.

Getting ready

Using Visual Studio Code, open the sample Online Banking app folder at \Chapter02\sql-injection\razor\ef\before\OnlineBankingApp\.

Testing a SQL injection

Here are the steps:

1. Navigate to Terminal | New Terminal in the menu or simply press Ctrl + Shift + ' in Visual Studio Code.
2. Type the following command in the terminal to build and run the sample app:

   dotnet run

3. Open a browser and go to http://localhost:5000/FundTransfers.
4. The browser will display the web page for searching fund transfers using keywords in the Filter By Notes field, as shown in the following screenshot:

   Ystem.

   

   Figure 2.1 – Fund Transfers page

5. In the Filter By Notes textbox, type C and then hit the Search button.
6. The web page will now return one entry finding one match for the Contingency Fund note:
   

   Figure 2.2 – Fund Transfers search result

7. Now try entering the SQL injection payload: %';create table tbl1(one varchar(10), two smallint);Select * from Customers where id like '1.
8. Notice that no error was thrown on the web page:
   

   Figure 2.3 – Successful SQL injection

9. To confirm that the SQL injection payload was executed successfully, open the \Chapter02\sql-injection\razor\ef\before\OnlineBankingApp\OnlineBank.db SQLite database using the DB Browser for SQLite tool:

Figure 2.4 – OnlineBank.db in DB Browser for SQLite

Notice the newly created tbl1 SQLite table

Now, let's see how to identify the SQL injection vulnerability in code that uses EF and mitigate the preceding issue by fixing this security flaw and applying a countermeasure.

How to do it…

Let's take a look at the steps for this recipe:

1. Launch Visual Studio Code and open the starting exercise folder by typing the following command:

   code .

2. Navigate to Terminal | New Terminal in the menu or simply press Ctrl + Shift + ' in Visual Studio Code.
3. Type the following command in the terminal to build the sample app to confirm that there are no compilation errors:

   dotnet build

4. Open the Pages/FundTransfers/Index.cshtml.cs file and locate the vulnerable part of the OnGetAsync method, where a dynamic query is composed:

   public async Task OnGetAsync()
   {
       var fundtransfer = from f in _context.FundTransfer
           select f;
       if (!string.IsNullOrEmpty(SearchString))
       {
           fundtransfer = _context.FundTransfer.            FromSqlRaw("Select * from FundTransfer                 Where Note Like'%" + SearchString +                    "%'");
       }
       FundTransfer = await fundtransfer.ToListAsync();
   }

5. To remediate the SQL injection vulnerability, let's start by adding a reference to System.
6. Next, change the preceding highlighted code into the following by using the FromSqlInterpolated method:

   fundtransfer = _context.FundTransfer.FromSqlInterpolated($"Select * from FundTransfer Where Note Like {"%" + SearchString + "%"}");

7. The FromSqlInterpolated method will create a LINQ query from the interpolated string supplied.

The interpolated parameter, SearchString, will then be converted into a DbParameter object, making the code safe from SQL injection.

How it works…

The Entity Framework allows you to execute raw SQL queries using the FromSQLRaw method. However, this method is dangerous as you can supply the argument with concatenated strings with the user input, SearchString:

_context.FundTransfer.FromSqlRaw("Select * from FundTransfer Where Note Like'%" + SearchString + "%'");

Using the payload used in the SQL injection test, imagine replacing the SearchString value with the malicious string %';create table tbl1(one varchar(10), two smallint);Select * from Customers where id like '1.

With FromSqlRaw blindly concatenating the injected input, the SQL statement now reads as follows:

Select * from FundTransfer Where Note Like'%%';create table tbl1(one varchar(10), two smallint);Select * from Customers where id like '1 %'

This is a perfectly valid series of SQL statements, except that it has a dangerous command that creates a new table or, in other cases or DBMS, could turn into a remote code execution by spawning a shell.

This way of forming SQL statements is regarded as bad coding practice. To write better and secure code, use methods such as FromSqlInterpolated to help compose harmless SQL statements with parameterized values.

There's more…

Parameterization is a proven secure coding practice that will prevent SQL injection. Another way to rewrite the code in this recipe is to use the DbParameter classes.

Introduce an instance of SqLiteParameter (which is derived from DbParameter) into the code as follows:

var searchParameter =     new SqliteParameter("searchString", SearchString);
fundtransfer = _context.FundTransfer     .FromSqlRaw("Select * from FundTransfer         Where Note Like'%@searchString%'",searchParameter);

Whitelisting is also a useful technique as a means to filter user input. You will have already seen this approach discussed in detail in Chapter 1, Secure Coding Fundamentals. Whitelisting will cause ASP.NET Core web applications to only process data that is in an expected format, but this technique is not as effective as using prepared statements or parameterized queries.

ADO.NET is a data provider platform that is integral to the .NET Framework. Since the advent of the .NET Framework, ADO.NET has been the component used to query and manipulate data in the database. ADO.NET can be used in developing data-driven ASP.NET Core web applications, but similar to any data providers, developers may write insecure code when using any of the System.Data.* or Microsoft.Data.* classes.

In this recipe, we will identify the SQL injection vulnerability in the code when using the ADO.NET and mitigate the issue by fixing this security flaw and applying a countermeasure.

Getting ready

Using Visual Studio Code, open the sample Online Banking app folder at \Chapter02\sql-injection\razor\ado.net\before\OnlineBankingApp\.

How to do it…

Let's take a look at the steps for this recipe:

1. Launch Visual Studio Code and open the starting exercise folder by typing the following command:

   code .

2. Navigate to Terminal | New Terminal in the menu or simply press Ctrl + Shift + ' in Visual Studio Code.
3. Type the following command in the terminal to build the sample app to confirm that there are no compilation errors:

   dotnet build

4. Open the Data/FundTransferDAL.cs file, which is the class that represents the data access layer of the sample application and locate the vulnerable part of the GetFundTransfers method where the user-controlled input is passed into the search parameter:

   public IEnumerable<FundTransfer> GetFundTransfers(string   search)
   {
       List<FundTransfer> fundTransfers =         new List<FundTransfer>();
       
       using (SqliteConnection con =         new SqliteConnection(connectionString))  
       {
           SqliteCommand cmd =             new SqliteCommand("Select *                 fromFundTransfer where Note like '%"                     + search + "%'", con);
           cmd.CommandType = CommandType.Text;
     
           con.Open();  
           SqliteDataReader rdr = cmd.ExecuteReader(); 

5. The preceding highlighted code is where the query is composed, and the search concatenated to form a SQL query.
6. To remediate the SQL injection vulnerability, change the preceding highlighted code:

   public IEnumerable<FundTransfer> GetFundTransfers(string   search)
   {
       List<FundTransfer> fundTransfers =         new List<FundTransfer>();
     
       using (SqliteConnection con =         new SqliteConnection(connectionString))
       {
           SqliteCommand cmd =             new SqliteCommand("Select * from                 FundTransfer where Note like '%" +                     @search + "%'", con);
           cmd.CommandType = CommandType.Text;
           cmd.Parameters.AddWithValue("@search",search);
           con.Open();
           SqliteDataReader rdr = cmd.ExecuteReader();

Using the parameterization approach, we have converted the search string into a SQL parameter and passed the value into SqlLiteParameterCollection.

How it works…

The SqlLiteCommand instance is blindly passed with a raw SQL concatenated user input. This supplied string is a source for a SQL injection. The input string search is not validated and unsanitized, letting an adversary insert an arbitrary SQL command or modify the query's intention:

SqliteCommand cmd = new SqliteCommand("Select * from FundTransfer where Note like '%" + search + "%'", con);  

You can rewrite the vulnerable ADO.NET code and make it secure by using query parameters. The AddWithValue method from SqliteParametersCollection of the SQliteCommand object allows you to add query parameters and safely pass values into the query:

cmd.Parameters.AddWithValue("@search", search);  

Changing the search string into a placeholder makes the query parameterized:

SqliteCommand cmd = new SqliteCommand("Select * from FundTransfer where Note like '%" + @search + "%'", con);  

When your ASP.NET Core web application executes the preceding lines of code, the query is now parameterized, safely passing the search value, and preventing malicious actors from altering the SQL.

There's more…

This recipe uses SQLite as the DBMS for the sample solution, but if you were to use Microsoft SQL Server, another option is to convert the query into a stored procedure and use it with DB parameters. You would then have to utilize the SQLCommand object and set the CommandType property to System.Data.CommandType.StoredProcedure, allowing the execution of parameterized stored procedures from code. These classes are available under the System.Data.SqlClient namespace and in the new Microsoft.Data.SqlClient package.

Here's a sample code snippet:

SqlCommand cmd = new     SqlCommand("sp_SearchFundTransfer",con);  
cmd.CommandType = CommandType.StoredProcedure;  
cmd.Parameters.AddWithValue("@search", search);  

To write better and secure code, use the built-in support for database features such as prepared statements or parameterized queries made possible by its data provider frameworks.