What do you think of when you see the following C# code:
ExecuteSql($"SELECT * FROM SomeTable WHERE SomeColumn = {someValue}");
- Huh? Nothing exciting here.
- STOP!!!! SQL injection vulnerability.
- Way cool language technique!!!! (Hint! Hint!)
Warning! Warning! Warning!
The potentially very dangerous bit of code below uses string interpolation to turn the content between $"…" into a string which is stored in variable sql.
var sql = $"SELECT * FROM SomeTable WHERE SomeColumn = {someValue}";
(Why is this possibly very, very bad? If you’re not sure, please stop now and read up on SQL injection. A solid graph of this security attack is fundamental for every developer who writes code that composes queries sent to databases—or, for that matter, to any other system or component, for this type of attack isn’t limited to just the SQL/database world.)
Wowing Technique!
However, in C#, an interpolated string literal ($"…") can output types other than a string, and that’s where the really cool language technique comes in.
If $"…"‘s output is immediately converted to a FormattableString or an IFormattable (like if it is directly assigned to a variable that is of one of those types), a value of that type (respectively) will be the result instead of a string. This opens up the opportunity to replace the default string interpolation behaviors with custom processing. Wow!
Out of the two “special” types, FormattableString is more interesting in this context. It holds a composite format string describing the interpolated string literal’s format and provides access to the value of each expression used in the original literal.
FormattableString sql = $"SELECT * FROM SomeTable WHERE Age = {age} AND Name = {name}";
var compositeFormat = sql.Expression;
// "SELECT * FROM SomeTable WHERE Age = {0} AND Name = {1}"
var arguments = sql.GetArguments();
// an array containing the values of age and name, e.g.: [10, "Joe"]
You can use these pieces of information to translate the original interpolated literal into the format you desire—say, by first SQL escaping and (where appropriate) quoting the arguments, then using those now safe-to-use values to generate SQL query text to actually execute.
Alternately, you may find it easier to define a custom format provider (and accompanying formatter) to do the heavy lifting. You’d pass this to FormattableString‘s ToString(IFormatProvider) method.
var provider = new MyCustomSqlFormatProvider();
FormattableString sql = $"SELECT * FROM SomeTable WHERE Age = {age} and Name = {name}";
var safeSqlString = sql.ToString(provider);
How you pull it off are implementation details; the general technique is our focus here.
Taking a step back to the big picture: Imagine defining a method that takes a FormattedString, does whatever is necessary to make it safe for use, then executes the result as a SQL query.
void ExecuteSql(FormattableString sql) { … }
…
ExecuteSql($"SELECT * FROM SomeTable WHERE Age = {age} and Name = {name}");
Beautiful! Consumers of your method can define queries using simple string syntax without needing to know how to escape or quote each possible type of argument. For them, those details are handled automatically, behind-the-scenes.
“Intercepting” string interpolation and “overriding” or replacing its default behavior is a very interesting technique to consider when strings need to be built with arguments that require special formatting or escaping.
Real Life Example
For an example of this technique from a well-known library, check out Entity Framework Core’s ExecuteSqlInterpolated() method (source). It takes each interpolated expression value used in the original literal and adds it as a parameter to a SQL command it builds. It then uses the original literal’s composite format string to render the originally-specified SQL query with the names of the just-defined parameters replacing the original expression values. So $"SELECT * FROM SomeTable WHERE Name = {name}" becomes SELECT * FROM SomeTable WHERE Name = @p0, accompanied by a parameter named @p0 set to name‘s value.