Using the SqlConnectionStringBuilder to guard against Connection String Injection Attacks

One new object in the ADO.NET 2.0 Library worth taking a look at is the SqlConnectionStringBuilder . 

Considering how late it is in the game, you are probably aware of the dangers of Sql Injection Attacks .  One the off chance you aren't, let me throw in my two cents and recommend that you familiarize yourself with them ASAP.  Anyway, injection attacks are possible when you use dynamic string concatenation to build your Sql Statements and you don't paramaterize them or escape all possible malicious input.  This presents a small problem b/c you need to employ user input in your applications in many cases, but every time you accept user input, you increase the surface are of an attack.  And regrettably, there are too many cases where people 'solve' this problem by employing a solution worse than the problem itself.  For instance, I've seen some people hell bent of using Dynamic Sql (and not parameters) solve the injection problem by disallowing all special characters.  While this does address the problem directly, it has the unintended consequence of forcing the users to use less strong passwords.  And most of the cases that I've seen where people implemented this rule, they increased the lenght of possible passwords to offset this vulnerability. Well, that 'solution' opens you up to other sorts of mischief.  After all, if you only allow people ten characters for their password, there's only so much malicious sql they can push off on you.  But assuming you don't increase the length of the password, you just pretty much hamstrung the user into have to adopt a weaker password scheme.  I've bitched about this alot in the past and it's not really the focus of this article so I'll cut it short here.

So what about Connection String injection attacks?  Well, yes, it's something you should guard against.  When I was first learning .NET, I created this application that stored User Name/Password combinations in a Database, both of which were encrypted.  This was  a particularly sensitive application and I was still pretty naive about security.  So I came up with a solution that entailed a good bit of extra maintenance but which I believed would give me extra security.  What I did was create a Sql Server login for every user.  So to even create the connection in the first place, I used their username/password combo to open the connection and then I ran an query that would check the authentication table, decrypt the username and password and pass back an output parameter indicating if they authenticated correctly or not. 

This was not just a bad idea, it was terrible.  For one thing, it was a nightmare to administer.  Every time we added a user, I'd have to create a new Sql Server account and had to use a utility I wrote to hash the password.  Other than being a pain in the neck, this made it impossible to add a new user for use with many of our applications without having to use my utlity (which people were unable to find on multiple occassions).  Another problem was with connection pooling.  If you have different connection strings for every user, it can have a serious adverse affect with Connection Pooling . Anyway, I thought all of this was worth it all in all b/c of the extra security benefit.  Then Angel Saenz-Badillos saw a post I had that described what I was doing and he set me straight.  You see, my whole concept was based on the fact that I didn't realize you could hack the connection string (don't ask me why, it should have been clear to me from the get go).  But all it takes for someone to have caused some mischief for me was to add Max Pool Size 100000000; Min Pool Size 100000000 to the password field. Sure, we are used to limiting the field size in many cases for fields that we accept input from but it's not unheard of for someone to leave it the default size.  Another trick that they could do is add "; Trusted_Connection = true".  This will cause a 'last one wins' scenario so in the case of my app, the machine account would be used instead of the assigned user account and if it had more privileges than the user account did, well, there was just an escalation of privilege.  Combined with a Sql Injection attack, this could cause some problems.

The good news is that in order to do much damage, the attacker would need to know the username and password or the machine itself would have to have sufficient permissions with the Sql Server to allow something troublesome to happen.  But relying primarily on 'luck' to not get hacked is kind of a lame strategy.  The other thing about Connection String Injection attacks is that if everything else is handled correctly (no unparamaterized queries allowed) then the attack surface is pretty much limited to non-permanent items or things that are just annoyances (although depending on the nature of your data, these annoyances could get quite serious very quickly).

So how do you address this issue?  Fortunately the good folks on the DataWorks Team gave use the SqlConnectionStringBuilder.  Basically, it handles the escaping issues for you and has the side benefit of being easier to use.  For one thing you can use it just like you would you old connection strings if you're dead set on not changing your ways:

SqlConnectionStringBuilder OldSchool = new SqlConnectionStringBuilder("Data Source=Clock1;Initial Catalog=Pr0nStarz;User Id=CuckooBurd;Password=++dfjk33nmjKLLa;");
SqlConnection cn = new SqlConnection(OldSchool.ToString());
Now you can use the SqlConnectionStringBuilder just like this, it's as easy as pie:

private void DemoConnectionStringBuilder()
{
   SqlConnectionStringBuilder ConnectionBuilder = new SqlConnectionStringBuilder();
   ConnectionBuilder.DataSource = SecurityUtil.DecryptText(@"#*a0-%%% ^889333",  SecurityUtil.CryptoProviders.TripleDES);
  ConnectionBuilder.InitialCatalog = SecurityUtil.DecryptText("pR9s4%~`");
  //Here you can specify either
  ConnectionBuilder.IntegratedSecurity = true;
  //ConnectionBuilder.UserID = SecurityUtil.DecryptText("pR9s4%~`") ;
  //ConnectionBuilder.Password =  SecurityUtil.DecryptText("!Cuck00zR~!e", SecurityUtil.CryptoProviders.TripleDES);
  using(SqlConnection MainConnection = new SqlConnection(ConnectionBuilder.ToString())){
     MainConnection.Open();
     //Do your thing
     MainConnection.Close();
 }
}
In the above example, I went with Integrated Security and commented out the username and password, but you can switch these back and forth.  All of the properties like Max Pool Size, Pooling, Workstation ID, PacketSize, everything you normally manipulate is there and since you have intellisense support, you have cues as to just about everything you may need.  Moreoever, if you want to be really safe, you can also create another wrapper class that exposes some of there properties but limits Max Pool Size or Min Pool Size in the accessors.  For instance it could check one vs the other and if you aren't comfortable with the numbers, you can throw an exception, change them to something you like, or do whatever you see fit. Same goes for the UserName/Password and Trusted Connection. Although it will escape any attempt to try to put Trusted_Connection = true as a value, you can use your class to manually ensure that only one or the other is chosen.  Right out of the Box the SqlConnectionStringBuilder is very powerful and can give you most of the functionality you'd ever want but there may be a case or two where you need everything to be even more granular - in which case you can easily implement such a scenario.  Sure, as far as features go, this is more evolutionary than revolutionary, but it's a nice touch.  If nothing else it will keep you from having to flip over to http://www.connectionstrings.com over and over when you are building new applications

-----

As an aside, I've listed the two primary targets for a Connection String injection attack but I'm not convniced that there aren't any other ones. If you can think of any other mischief you can cause via the connection string, be a pal and shoot me your idea - I'll include it as an addendum to this post and give you the credit.