AntiSQLi: The New Black Magic

Posted by on July 3, 2013

As a Principal Security Consultant, I see too many sites that still have SQL Injection vulnerabilities.  As a developer, I have spent years writing code and having a security background, I often wonder why we still have so many out there.  Of course, we have issues like legacy code, which no one wants to touch.  You know what I am talking about.   We have third party software that we can’t touch.  Then we have our own code that we should be securing, but maybe we just haven’t fully implemented properly calling SQL in a secure manner.   IronBox and Security Innovation, Inc have banded together to help try and make this easier for developers with AntiSQLi.  (Important to note that this is still in beta)

The goal of this new library is to make it easy for developers to make SQL calls in a secure manner using a syntax they are already familiar with.  The methods they have created are very similar to using String.Format.  Lets take a look at what their way of SQL Queries looks like.  The following shows how simple it is to create a parameterized query with their new library:  (Make sure us add “using IronBox.AntiSQLi.SqlClient;” to your file)

SqlParameterizedQuery query = new SqlParameterizedQuery();
query.LoadQueryText("SELECT * FROM Users WHERE UserId = {0} AND Password = {1}", 
                     txtEmail.Text, txtPassword.Text);

 

As you can see from the code example from above, this is very similar to building your query using String.Format, however it is drastically more secure.  That black magic that is in the title of this post… that is what adds the security.  The LoadQueryText method does some magic behind the scenes that creates a command object and adds parameters for each of the parameters you passed into the method call.   Talk to anyone about making secure SQL calls and Parameterized queries will be mentioned.  So lets take a look at what happens after this call is executed:

The image above shows how the library automatically parameterizes the query replacing the {0} and {1} with @AntiSQLiParam0 and @AntiSQLParam1.  The next image shows the parameter properties for one of these objects.

Seems pretty simple right?  Lets take a quick look at how we would have done this ourselves with the command object.

using (SqlCommand cmd = new SqlCommand())
{
    cmd.CommandText = "SELECT * FROM Users WHERE UserId = @userId AND Password = @password";
    cmd.CommandType = System.Data.CommandType.Text;
    cmd.Parameters.Add("@userId", System.Data.SqlDbType.VarChar, 100);
    cmd.Parameters.Add("@password", System.Data.SqlDbType.VarChar, 50);
    cmd.Parameters["@userId"].Value = txtEmail.Text;
    cmd.Parameters["@password"].Value = txtPassword.Text;
}

This takes a bit more code when we do this manually, however we have complete control over all of the properties created for each parameter.  We could shrink this code a little by using cmd.Parameters.AddWithValue(), which is fairly common.   However we are not here to discuss performance issues.  One thing to keep in mind is that with more parameters, the latter set of commands grows much more than the AntiSQLi code.  This doesn’t mean that one is better than the other.  Some people rate them selves on lines of code (right or wrong).

There are still other options, like using Linq, Entity Framework, or other ORM’s that are avialable.  This is just another option to help get developers involved in helping cut down on the number of SQL Injection vulnerabilities exist out there.  This library is still in Beta, and I haven’t gotten a chance to play with it too much, but I wanted to let people know about it to know their options.  If you check this out, let me know what you think about it.

You can get the library from GitHub: https://github.com/IronBox/AntiSQLi

Tags:

Comments

Comments are closed.