Fixing SQL injection in ADO.NET

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.