SQL injection

If you’re writing raw SQL queries that aren’t parameterized, or it makes use of string concatenation or string formatting, then your code may be vulnerable to SQL injection. We’ve taught ourselves that seeing code like this is bad.

context.Posts.FromSql($@" 
    SELECT * 
    FROM Posts 
    WHERE Title= {title}");

Just imagine if the title variable had a value of ''; DROP TABLE Posts; --. That would result in the Customers table being dropped from the database, so we avoid this by parameterizing our queries.

xkcd

String interpolation

String interpolation was introduced in C# 6.0, and offers a more readable and expressive way to format strings. For the most part it takes the place of string.Format, but the compiler can also turn an interpolated string literal into a FormattableString if you ask for one.

In Entity Framework Core 1.x, the code above would be vulnerable to SQL injection. However, one of the new features of Entity Framework Core 2.0 is support for string interpolation in raw SQL methods.

This new feature provides overloads for the raw SQL methods that take a FormattableString. The FormattableString type exposes the format string and the arguments that were passed to it. Based on that information, EF is now able to build a parameterized version of the query using the interpolated string literal.

Making our own

Below is an example of how you might implement this functionality with regular ADO.

Then we can write code like this.

connection.CreateCommand($@" 
    SELECT * 
    FROM Employees 
    WHERE FirstName = {firstName} AND LastName = {lastName}");

A parameter will be created for each of the FormattableString arguments, and the parameter names will be passed to the format string, resulting in the following query.

SELECT * FROM Employees WHERE FirstName = @p0 AND LastName = @p1

Other thoughts

An issue has already been created on Dapper’s repository requesting a similar feature. However, there are concerns about the risks involved with its implementation and use.

Nevertheless, the concept is still really neat, and there may be other ways to take advantage of the FormattableString type for areas other than data access (e.g., logging).