Using C# 6 to make 'bad' SQL awesome

category: [ 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

ABOUT ME

Gordon Beeming works at Nologo Studios in the sunny city of Durban, South Africa. He is the Lead for the Data and Services Team and has a strong focus on Developer Efficiencies and R&D. When he's not hacking away at a keyboard in Visual Studio he'll generally be relaxing with his family or hitting the black top getting in some mileage. He is a Visual Studio ALM Ranger and Visual Studio ALM MVP.

Follow me on Strava

TOOLS

I plan on writing a bunch of online tools and sharing the code for how I made those tools. If you have any feedback you can ping me on Twitter (@GordonBeeming) or mail me [email protected].