Using C# 6 to make 'bad' SQL awesome

categories: [ Just Coding ] tags: [ CSharp-6 ] [ SQL Injection ]
created: 09 Feb 2017 @ 19:46 modified: 24 Apr 2017 @ 13:57

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.

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

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 {0} parts, in fact before doing the replacement here the formattableString.Format properties value shows the {0} placeholders.

Using it in an example

Using this code is super simple

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.

cmd_2017-02-09_21-43-26

100% legal and safe Open-mouthed smile

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 Smile