Secure Your .NET Application

What Is SQL Injection?

SQL injection happens when an input parameter that is known to be passed into a raw SQL statement is modified by an attacker so that the SQL statement is executed differently than intended. That probably sounds a little complicated, so let’s look at an example.

Suppose you have a database with two tables. The first table of NonsensitiveStuff contains data that you might make publicly available. The second table of SensitiveStuff contains private user information that you must protect.

Now suppose you have an API endpoint. All it does is take a parameter of an Id and uses it to request data from NonsensitiveStuff.

SELECT * FROM NonsensitiveStuff WHERE Id=[passed value]

Now let’s set up a call to the API endpoint and pass the Id value:

https://my-api-endpoint.com?Id=23

This query will result in the record matched with  Id = 23  being retrieved from NonsensitiveStuff. But what happens if you inject some extra text into the URL?

https://my-api-endpoint.com?Id=23;drop%20table%20SensitiveStuff

If your app fails to protect against SQL injection attacks, then the following query is executed:

SELECT * FROM NonsensitiveStuff WHERE Id=23;DROP TABLE SensitiveStuff

This is a catastrophic attack, as all of your users’ private data has just been deleted from the database. If the attacker has access to additional table names, they could drop every one of them from the database.

Prevent SQL Injection

Fortunately, preventing such attacks is quite easy, provided you follow a few simple rules:

1. Always use .NET routing procedures when constructing API endpoints and including user input in URL construction. Standard .NET routing goes a long way toward eliminating these attacks.

2. For any API that accepts SQL, always parameterize user input. You can include parameter placeholders in the query string and then supply parameter values as additional arguments. Any parameter values you supply automatically converts to a DbParameter, which provides effective sanitation and validation. For example, the code segment below passes a single parameter to a stored procedure. The supplied value is wrapped in a parameter, and the generated parameter name is inserted where the {0} placeholder is specified:

var user = "joeuser";
var blogs = db.Users
    .FromSql("EXECUTE dbo.GetUserProfile {0}", user)
    .ToList();

The same query can be executed using string interpolation syntax, which is supported in EF Core 2.0 and above:

var user = "joeuser";
var blogs = db.Users
    .FromSql($"EXECUTE dbo.GetUserProfile {user}")
    .ToList();

You might also construct a DbParameter for the query and supply it as a parameter value. This method lets you use named parameters in the SQL query string:

var user = new SqlParameter("user", "joeuser");
var blogs = db.Users
    .FromSql("EXECUTE dbo.GetUserProfile @user”, user)
    .ToList();

Any of the above methods provides sufficient protection against SQL injection attacks and should be standard procedure for the construction and execution of SQL queries in your code.

Let’s Recap!

You have learned about the different types of cross-site attacks that allow attackers to inject malicious code into websites, and how open redirect attacks are used to trick users into providing sensitive information. You’ve also learned how to prevent such attacks on your .NET Core applications. 

Additionally, you learned about SQL injection, a technique by which an attacker injects raw SQL statements into query parameters that are passed to database queries. Finally, you learned the two simple rules to follow to prevent SQL injection attacks in your applications:

1. Always use .NET routing procedures when constructing API endpoints.

2. Always parameterize user input.

Now that you’ve completed the first part of this course, it’s time to test your knowledge. Are you ready? Let’s move on to your first quiz!