SQL injections are one of the most common and dangerous security threats you can face, and no programming language or stack is immune to them. Yes, .NET SQL injection is a thing and you'd better learn how to prevent it.
That's what this post is about. We'll start with a brief explanation of SQL injections in general. You'll understand what this attack is, how it works, and why it's so dangerous.
After that, we'll get to the .NET-specific portions of the post, where we'll show you what SQL injections look like in .NET and how you can prevent them. Let's get started.
.NET SQL Injection: What Is It? Why Care?
As promised, let's start with the basics. What is SQL injection? Why is it so important?
A SQL injection is a type of injection attack in which an ill-intended actor successfully injects—you've guessed it!—excerpts of SQL code into your application. They do that by exploring vulnerabilities that exist in portions of the app where it interacts with—and receives data from—the external world. In web applications, classical entry points for SQL injection attacks can be form fields and URL parameters.
How does a SQL injection attack work? You'll learn about the mechanics of the attack in more detail soon. For now, suffice it to say that, when successfully injecting SQL code, an attacker is able to append text to a legitimate query in the application. The query is sent to the database and executed, but its behavior is now different from what it's supposed to be.
A successful SQL attack can be devastating. It can enable the attacker to:
Access/steal unauthorized data
Include unauthorized data in the target database
change or delete data from the database
To sum it up: SQL injections can be really bad for your organization. The consequences can be catastrophic, including not only financial but also legal and reputational ones.
.NET SQL Injection: A Simple Example
I promised I would explain how a .NET SQL injection works in further detail. Time to deliver on that promise.
Let's say you've written a blog engine using .NET. A very common feature for blog engines is to include the title of posts in their URLs without spaces or special characters—we call that a "slug." That slug is then used to retrieve the relevant post from the database.
So, it's not unreasonable to think your app would have code like this:
var query = "SELECT Title, Body, Excerpt FROM Post WHERE Slug = '" + slug + "' ORDER BY Published DESC";
// query execution, etcSo, what's the problem? Considering the slug variable comes from user input and it's simply concatenated as is in the query, this application is open to a SQL injection. The malicious actor could edit the URL so it looks like this:
<YOUR-DOMAIN-NAME>/posts/'; DROP TABLE Post;--The string concatenation would result in the following query:
SELECT Title, Body, Excerpt FROM Post WHERE Slug = ''; DROP TABLE Post;--' ORDER BY Published DESCAs you can see, the single quote provided by the malicious actor matched the opening one already existing within the query. Then, the malicious query emits a DROP TABLE statement. Following that, it includes the "--" character, which makes everything else be considered as a comment.
How to Prevent .NET SQL Injection
As you've seen, SQL injection attacks can deal a devastating blow to your application. How can you avoid that?
A lot of the advice to prevent SQL injections is related to "sanitizing your input." While distrusting all user input is a wise choice in general, sanitizing inputs won't suffice as a solution. This article by Kevin Smith gives a nice explanation as to why. So, what are the valid solutions?
Validating Input
Often, the methods in your application will accept only a small subset of all possible values as valid. For instance, if your ASP.NET MVC app has an action that gets an integer as a parameter...Well, there's really no reason to allow it to receive a string. Indicating the correct type in the action method will cause attempts of passing anything that's not an integer to fail.
Using Allowlists
This is perhaps a continuation of the previous idea. In some situations, the set of valid values will be small enough that you'll be able to create a list with all of them. For instance, imagine a bug-tracking system in which you can filter tickets by their severity (e.g. low, medium, high, critical.) Since the set of possible values is so small, the application code can keep the whole list in memory and validate the URL parameters. If it's not on the list, it's a hard no.
Using Parametrized Queries
The most excellent method when it comes to avoiding .NET SQL injections—or injections in any other tech stack—is to use parametrized queries.
Parametrized queries avoid SQL injection in a clever way: You simply don't include the user-provided values in the query at all. They are provided to the database separately, never being part of the text of the SQL query itself.
The query in our previous example would be written like this:
var query = "SELECT Title, Body, Excerpt FROM Post WHERE Slug = @slug ORDER BY Published DESC";We avoid string concatenation by using a placeholder for the slug. Great, but how do we pass the actual value?
After creating a new SqlCommand, you would add a new parameter using the AddWithValue method:
var query = "SELECT Title, Body, Excerpt FROM Post WHERE Slug = @slug ORDER BY Published DESC";
var command = new SqlCommand(query, connection);
command.Parameters.AddWithValue("@slug", slug);Remediation: The Principle of Least Privilege
A great security practice—not only for avoiding SQL injections, but in general—is what's sometimes called the principle of least privilege. It simply means always choosing the least possible privilege for executing any sensitive action. For instance, if you're integrating with some API and you need to generate a token, make sure you only grant the permissions you'll actually use in your app, and nothing more.
In the case of databases, the principle of least privilege means using database logins that can only do the bare minimum. Remember our first example? The malicious SQL code generated was this:
SELECT Title, Body, Excerpt FROM Post WHERE Slug = ''; DROP TABLE Post;--' ORDER BY Published DESCIf the application's connection string used a database account with just reading privileges, the malicious query above wouldn't be able to cause any harm.
Does SQL Injection Still Work in 2021? Yes, but It Doesn't Have to!
If you Google "SQL injection", the "people also ask" section will show you questions like: Does SQL injection still work in 2021?
It's curious that many people consider SQL injections an "archaic" type of attack. And it's depressing to think that, yes, SQL injections still work, being consistently ranked in the top 10 OWASP security threats report.
The good news is that SQL injections aren't inevitable. As you've seen today, with the help of parametrized queries, you can avoid string concatenation, which means raw input values are never part of the text of the SQL query. With the use of an ORM such as Entity Framework, the risk of an injection is further decreased since queries aren't assembled by string manipulation.
To sum it up:
As a rule, never trust user input without performing validation on it
Always use parametrized queries
Abide to the rule of least privilege
Following the tips above will dramatically reduce the chances of a SQL injection attack in your application. Stay safe, and thanks for reading!
This post was written by Carlos Schults. Carlos is a consultant and software engineer with experience in desktop, web, and mobile development. Though his primary language is C#, he has experience with a number of languages and platforms. His main interests include automated testing, version control, and code quality.
