Using C# 6 to make 'bad' SQL awesome

I'm busy going through Pluralsight watching Exploring C# 6 with Jon Skeet by Rob Conery. Jon Skeet goes ahead and makes this epic method that looks like it's so naughty but is pretty cool.

Follow @jonskeet Follow @robconery

#Introduction

The reason for saying how to make bad SQL awesome is mainly because is most applications you'd generally want to have your SQL in stored procs and not hard coded into your C# code so that you can do proper t-sqlt tests and other also get other goodness from the sql engine that you wouldn't get executing raw sql all the time.

The method that is written helps make a SqlCommand from what looks like a bad coding practice that would generally allow for SQL injection.

#The magic method

The code that Jon writes look something like below

public static SqlCommand ToSqlCommand(FormattableString formattableString, SqlConnection sqlConnection)
{
    var args = formattableString.GetArguments();
    var sql = string.Format(CultureInfo.InvariantCulture, 
                                formattableString.Format, 
                                Enumerable.Range(0, args.Length)
                                            .Select(index => $"@p{index}")
                                            .ToArray());
    var command = new SqlCommand(sql, sqlConnection);
    for(int index = 0; index < args.Length;index++)
    {
        command.Parameters.AddWithValue($"@p{index}", args[index]);
    }
    return command;
}

It's so simple but so magical. Basically it's just looping though all the locations you have argument placeholders, with string format these would be the parts, in fact before doing the replacement here the formattableString.Format properties value shows the placeholders.

#Using it in an example

Using this code is super simple

static void Main(string[] args)
{
    // assume these come from inputs to make it scary
    var name = "Gordon";
    var surname = "Beeming";

    var sqlConnection = new SqlConnection();
    var theCommand = ToSqlCommand($@"
SELECT * 
FROM dbo.tb_People 
WHERE Name = {name} AND Surname = {surname}", sqlConnection);
    Console.WriteLine(theCommand.CommandText);
    Console.WriteLine();
    for (int index = 0; index < theCommand.Parameters.Count; index++)
    {
        Console.WriteLine($"{theCommand.Parameters[index].ParameterName} = {theCommand.Parameters[index].Value}");
    }
    Console.WriteLine();
}

The code looks so clean although every time I look at it I feel dirty like I'm opening the app up for SQL Injection but with the magic of C# 6 this code is perfectly safe and will execute with parameters as we'd expect

#

#Running the example code

Now we aren't actually executing the code in this sample but you can see that from it's output it wouldn't give us any issues when it does execute.

100% legal and safe 😁

#

#Download the sample code

The sample project used for this post is in GitHub if you want it.

Jon also just mentioned that in his Demo Repo on GitHub there is a advanced version of this that handles types as well 🙂