Fixing SQL injection with Entity Framework

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.