Preventing SQL injection in C# applications

SQL injection is a common attack vector that remains one of the most prevalent security risks for applications today.

One of the reasons SQL injection attacks are so pervasive is the fact that injection vulnerabilities are very easy for attackers to discover and exploit, sometimes with devastating consequences.

On the other hand, mitigating SQL injection attacks is straightforward and can be easily avoided with the right knowledge and the right code in place.

In this article, I am going to explain how a basic SQL injection attack is typically performed. I will then show you how to prevent this attack in C# applications using SQL parameters for different .NET libraries and packages.

SQL injection explained

A SQL injection attack involves the injection/insertion of untrusted data into a SQL query, causing a transition from the data context into the query context.

This can result in a number of undesirable consequences. It might mean the leakage of sensitive data, the updating or removal of unintended records, or even the alteration of the database schema or the destruction of the entire database.

Given the very real possibility of these potentially devastating consequences, SQL injection is something that needs to be taken very seriously.

Your application will be open to possible SQL injection attacks anytime that a user is able to pass along untrusted data. This could be via a query string in a web application or via a form that takes input from the user.

If you are not handling untrusted data correctly, your application will be vulnerable to an attack. Usually, a SQL injection vulnerability boils down to code that is dynamically building up SQL queries. If these queries are not correctly separating input values from the SQL text they present a prime opportunity for an attacker.

In the following sections, I’ll show you some bad application code and the dangers it presents to help you envisage what a potential SQL injection attack looks like.

Bad code

Let’s start off with a bad example of SQL query code i.e. what not to do!

The first few examples I am going to show you use standard ADO.NET classes and methods to build up the query and execute it to retrieve the results. Later, we’ll look at how some popular ORMs help you mitigate SQL injection attacks.

All of the examples in the following sections can be tested using the standard AdventureWorks SQL Server database.

Note that you will need a System.Data.SqlClient using statement in order for the code sample to work.

string connectionString = "Server=.;Database=AdventureWorksDW2017;Trusted_Connection=True;";
 
string searchTerm = "AW00011010";
 
// BAD CODE. DO NOT USE!!
string sql = $@"SELECT EmailAddress
                FROM dbo.DimCustomer
                WHERE CustomerAlternateKey = '{searchTerm}'";
 
var emails = new List<string>();
 
using (var connection = new SqlConnection(connectionString))
{
    connection.Open();
 
    using (var command = new SqlCommand(sql, connection))
    using (var reader  = command.ExecuteReader())
    {
        while (reader.Read())
        {
            string email = reader.GetString(0);
            emails.Add(email);
        }
    }
}
 
Console.WriteLine("Records count: {0}", emails.Count);

Note that the code sample is purely designed for demonstration purposes. Aside from the SQL injection vulnerability, in production code your connection string should be pulled from a configuration setting and the code to execute ad-hoc queries should be abstracted away within suitable objects and methods (or use an ORM).

Code walkthrough

Let’s walk through what the sample code is doing.

First of all, the connection string for connecting to the AdventureWorks database is set.

Following this, a search term variable is initialised to simulate some user-inputted (i.e. untrusted) data.

Next, the SQL query that is to be executed is composed.

Note how the search term text is interpolated directly into the string, surrounded by single quotes – piecing together a SQL statement like this without parameters is the thing that we want to avoid at all costs!

The SQL that is generated by the code will look as follows.

SELECT EmailAddress
FROM dbo.DimCustomer
WHERE CustomerAlternateKey = 'AW00011010'

After this, a list of strings is initialised that will hold the emails that are returned by the query.

The rest of the code is the standard ADO.NET pattern of creating and opening a connection, creating a command and data reader, then reading the results.

The results are added to a generic list of strings.

Finally, the number of emails records that were read is output to the console. In the case of the standard AdventureWorks database, one record should be returned.

The dangers

The bad code works, but why is it bad?

It is easy to demonstrate the dangers of the code by changing the searchTerm variable as follows.

string searchTerm = "' OR 1=1--";

Let’s pretend this search term has been passed in by the user via some type of filtering feature in our application.

The SQL that is generated will now look as follows.

SELECT EmailAddress
FROM dbo.DimCustomer
WHERE CustomerAlternateKey = '' OR 1=1--

This query is returning over 18,000 records, a potential data leakage!

By starting off the search term with a single quote we have managed to close the opening single quote in the SQL query string that is generated by the code.

By adding OR 1=1 we have specified a condition that will always evaluate to true, thus essentially removing any filtering and returning all results.

The -- text indicates a comment. If the SQL query string in the code contained additional conditions, the double-dash would ‘comment out’ the rest of the SQL code so that it would have no effect on the preceding injection SQL.

Now imagine what else an attacker could do. Depending on the type of SQL query being executed by the application, an attacker could potentially add, update, or delete data. They could even alter the database schema if the application is connecting to SQL Server as a highly privileged user (another thing to avoid from a security standpoint)!

It’s worth noting that aside from the simple ' OR 1=1-- example shown above, there are plenty of other ways that an attacker can exploit a SQL injection vulnerability using carefully crafted strings that are customised according to the input mechanism.

Now that we’ve seen an example of a SQL injection attack, let’s take a look at how we can correct the bad code from the previous section.

Good code

The bad code sample is bad due to the manner in which it builds up the SQL query string dynamically in the code. The code doesn’t take into account the fact that the search term is untrusted data that has been supplied by the user.

To fix this issue, we’ll need to introduce SQL parameters.

string connectionString = "Server=.;Database=AdventureWorksDW2017;Trusted_Connection=True;";
 
string searchTerm = "AW00011010";
 
string sql = $@"SELECT EmailAddress
                FROM dbo.DimCustomer
                WHERE CustomerAlternateKey = @SearchTerm";
 
var emails = new List<string>();
 
using (var connection = new SqlConnection(connectionString))
{
    connection.Open();
 
    using (var command = new SqlCommand(sql, connection))
    {
        command.Parameters.Add(new SqlParameter("@SearchTerm", searchTerm));
 
        using (var reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                string email = reader.GetString(0);
                emails.Add(email);
            }
        }
    }
}
 
Console.WriteLine("Records count: {0}", emails.Count);

The above code looks very similar to the previous example. The key differences are in the SQL query string and the introduction of SQL parameters.

What fixes the issue

Instead of the search term being injected into the SQL string (i.e. WHERE CustomerAlternateKey = '{searchTerm}') the SQL string now references a parameter name instead (i.e. WHERE CustomerAlternateKey = @SearchTerm).

The @SearchTerm parameter value is specified by adding a SQL parameter before executing the query, as follows.

command.Parameters.Add(new SqlParameter("@SearchTerm", searchTerm));

As you can see, we didn’t have to make very many changes to greatly improve the safety of the code.

If you now try changing the value of the searchTerm variable to ' OR 1=1-- you will find that no results are returned. This is because the SQL parameter is encapsulating the search term data such that it is sent to the server separate from the SQL query text. This prevents an unintended transition from the data context into the query context.

Something you should be aware of is that the SqlParameter constructor I am using above infers the SQL data type based on the C# data type. If you need more control over the data type you can adjust the code a little bit.

var parameter   = new SqlParameter("@SearchTerm", SqlDbType.NVarChar);
parameter.Value = searchTerm;
command.Parameters.Add(parameter);

In the revised code above, a SQL data type of NVarChar is specified and the value is set separately, as there isn’t a SqlParameter constructor available that accepts both the data type and the value.

Making things easier

Although it didn’t take much code to fix the SQL injection issue, you can imagine that with more complex queries it can be a little tedious to build up lots of SqlParameter objects and add them to the command for every ad-hoc query that you want to run.

If using an ORM isn’t an option, there are other ways of simplifying the code.

Reflection can be a great help in making the code needed to generate SQL parameters more concise. Below is a method that will take an object (ideally an anonymous object) and convert its public property names and values into SqlParameter objects.

/// <summary>
/// Creates SQL parameters based on the public properties exposed by the specified object.
/// </summary>
/// <param name="parametersObject">The (anonymous) object containing the parameter properties</param>
/// <returns>A collection of SQL parameters</returns>
public static IEnumerable<SqlParameter> CreateParameters(object parametersObject)
{
    var parameters = new List<SqlParameter>();
 
    foreach (PropertyInfo property in parametersObject
        .GetType()
        .GetProperties(BindingFlags.Public | BindingFlags.Instance))
    {
        parameters.Add(new SqlParameter($"@{property.Name}", property.GetValue(parametersObject, null)));
    }
 
    return parameters;
}

The revised code from the previous example that makes use of the CreateParameters method is shown below.

string connectionString = "Server=.;Database=AdventureWorksDW2017;Trusted_Connection=True;";
 
string searchTerm = "AW00011010";
 
var parameters = CreateParameters(new { SearchTerm = searchTerm });
 
string sql = $@"SELECT EmailAddress
                FROM dbo.DimCustomer
                WHERE CustomerAlternateKey = @SearchTerm";
 
var emails = new List<string>();
 
using (var connection = new SqlConnection(connectionString))
{
    connection.Open();
 
    using (var command = new SqlCommand(sql, connection))
    {
        foreach (var parameter in parameters)
        {
            command.Parameters.Add(parameter);
        }
 
        using (var reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                string email = reader.GetString(0);
                emails.Add(email);
            }
        }
    }
}
 
Console.WriteLine("Records count: {0}", emails.Count);

In the above code, the parameters needed by the SQL query are built by the CreateParameters method. After the SqlCommand is created a foreach loop is used to add the parameters to the command parameters collection.

The benefit of the CreateParameters method becomes much more apparent when you are building up a SQL query that needs to use a number of different parameters.

In real-world code, you’ll need to consider where some of the above code could fit into your existing data layer, or ideally, look into using an ORM instead along with the Repository Pattern.

ORMs

Using SqlClient and the other types within ADO.NET is useful for demonstrating SQL injection attacks so that you can understand things at a lower level.

However, the majority of applications today use an ORM package to help abstract and simplify database access.

In addition to the productivity benefits and reduction in the amount of code you need to write, an ORM will usually enhance the security of your application.

The security benefits typically come about through the automatic parameterisation of queries for you under the hood. However, even for raw SQL queries, most ORMs do a decent job of encouraging you to use SQL parameters by providing simple ways of specifying the parameters in cases where the higher-level ORM queries aren’t sufficient.

Dapper

One of my favourite ORMs is Dapper. If you’re looking for something that is simple and lightweight, this may be the package that you are looking for.

It’s a little bit different to other ORMs due to its lightweight nature that has a fairly big focus on raw SQL queries. However, it allows you to execute these queries safely by using SQL parameters.

Dapper makes it easy to parameterise your SQL queries, as the various Query methods that are provided include a param parameter. This parameter allows you to pass an anonymous object containing the property names and values of the SQL parameters you want to add to the query. This is very similar to the functionality of the CreateParameters method that I demonstrated in the previous section.

I have included an example of using Dapper below that achieves the same end result as the ADO.NET code in a more concise manner, thanks to the Query method.

string connectionString = "Server=.;Database=AdventureWorksDW2017;Trusted_Connection=True;";
 
string searchTerm = "AW00011010";
 
string sql = $@"SELECT EmailAddress
                FROM dbo.DimCustomer
                WHERE CustomerAlternateKey = @SearchTerm";
 
using (var connection = new SqlConnection(connectionString))
{
    connection.Open();
    
    var emails = connection.Query<string>(
        sql, 
        new { SearchTerm = searchTerm });
 
    Console.WriteLine("Records count: {0}", emails.Count());
}

The Dapper Query method takes care of converting the SearchTerm property defined within the anonymous object into an appropriate SQL parameter to fulfil the query requirements.

It’s worth noting that the Dapper ORM is implemented as a set of extension methods on the IDbConnection interface. This means that it can be used with SQLite, MySQL, PostgreSQL, and any other database where a .NET ADO provider is available.

Other useful Dapper extension methods include QueryFirstOrDefault and QuerySingleOrDefault.

Async versions of these methods are also available e.g. QueryFirstOrDefaultAsync.

Entity Framework

If you’ve worked with Entity Framework before, like most ORMs, you’ll know that it is much more focused on the mapping of objects to database tables than it is on running ad-hoc queries.

The original .NET Framework version of Entity Framework provides the SqlQuery method that allows SQL parameters to be passed into it as follows.

string connectionString = "Server=.;Database=AdventureWorksDW2017;Trusted_Connection=True;";
 
string searchTerm = "AW00011010"; 
 
string sql = $@"SELECT EmailAddress                  
                FROM dbo.DimCustomer
                WHERE CustomerAlternateKey = @SearchTerm";

using (var dbContext = new AdventureWorksDbContext(connectionString))
{
    var emails = dbContext.Database.SqlQuery<string>(
        sql, 
        new SqlParameter("@SearchTerm", searchTerm)).ToList();
    
    Console.WriteLine("Records count: {0}", emails.Count());
}

Entity Framework Core provides the FromSqlRaw method that allows you to convert raw SQL queries into objects and it accepts parameters as you would expect.

You can also use the FromSqlInterpolated method to use standard string interpolation in your SQL query and Entity Framework Core will automatically convert the interpolated string values into SQL parameters. However, I would urge caution with this approach as it feels a bit too similar to the bad code I’ve shown earlier in this article and I believe it could lead to mistakes being made somewhere along the line in your codebase.

I encourage you to check out this Microsoft Docs page which does a great job of explaining the FromSqlRaw and FromSqlInterpolated methods.

Other considerations

When used correctly, SQL parameters prevent your code from being vulnerable to SQL injection attacks.

Having said that, it’s important to adhere to the principle of security in-depth when developing software applications. This means that where possible, you shouldn’t rely on a single layer of defence. Instead, build up multiple layers of security so that if one defence fails, there is a backup in place.

Some other approaches to consider for preventing successful SQL injection attacks include the following.

  1. Restrict the database user accounts used by your application to only have access to the minimum amount of data and permissions needed to perform their function. This is commonly referred to as the principle of least privilege.
  2. Use a testing tool, such as CircleCI, to automate SQL injection attack tests. This will give you much more confidence that your application is properly handling user input in relation to SQL queries.
  3. Create an allowed list of input data characters for certain functions of your application. The practicality of this will depend on the particular feature of the application you are securing.

There may be other things that are worth considering, but these are the most important ones that I would encourage you to evaluate for your application.

Use an ORM instead of raw SQL where possible. For most applications, only resort to writing SQL queries when the ORM doesn’t provide a way of getting the data for you in some other way e.g. via LINQ, or if performance is critical.

Above all, make sure that you are using SQL parameters in all of your queries, regardless of where or how they are defined. This is the single most important thing you can do to mitigate SQL injection.

Summary

In this article, I’ve explained what SQL injection is, why it’s so dangerous, and how you can prevent it.

I’ve shown you some bad code, some good code, and some code that can help make it easier to work with SQL parameters in C# applications.

I’ve explained how you can make sure that you are using SQL parameters when running ad-hoc queries in popular ORMs, namely Dapper and Entity Framework.

Finally, I’ve covered some additional considerations you should make to help prevent successful SQL injection attacks from taking place.


I hope you enjoyed this post! Comments are always welcome and I respond to all questions.

If you like my content and it helped you out, please check out the button below 🙂

Comments