SQL Injection in 2013: Lets Work Together to Remediate

Posted by on January 4, 2013

We just started 2013 and SQL Injection has been a vulnerability plaguing us for over 10 years.  It is time to take action.  Not that we haven’t been taking action, but it is still prevalent in web applications.  We need to set attainable goals.  Does it seem attainable that we say we will eradicate all SQL Injection in 2013?  Probably not.  This is mostly due to legacy applications and the difficulty in modifying their code. There are some goals we can do to stop writing new code vulnerable to SQL Injection.  Fortunately, this is not a vulnerability that is not well understood.  Here are some thoughts for moving forward.

Don’t write SQL Injection Code

OK, this sounds like what everyone is saying, and it is.  Is it difficult to do? No.   Like anything, this is something we need to commit to and consciously make an effort to do.  Proper SQL Queries are not difficult.  Using parameterized queries is easy to do in most languages now.  Here is a quick example of a parameterized query in .Net:

using (SqlConnection cn = new SqlConnection())
{
    using (SqlCommand cmd = new SqlCommand())
    {
        string query = "SELECT fName,lName from Users WHERE fName = @fname";
        cmd.CommandText = query;
        cmd.CommandType = System.Data.CommandType.Text;
        cmd.Parameters.AddWithValue("@fname", untrustedInput);
        cmd.Connection = cn;
        cmd.Connection.Open();
        cmd.ExecuteNonQuery();
    }
}

What about stored procedures?  Stored procedures are good, but can be vulnerable to SQL Injection.  This is most common when you generate dynamic queries from within the stored procedure.  Yes, the parameters are passed to the procedure properly, but then used in an insecure way inside the procedure.  If you are unsure if your procedures are vulnerable, look for the use of EXEC or other SQL commands that run SQL code and make sure parameters are handled properly.

Often overlooked is how a Stored Procedure is called.  You are using a stored procedure but calling it like so:

string query = "EXEC spGetUser '" + untrustedinput + "'";

The above query can still be vulnerable to SQL Injection by chaining onto the EXEC statement.  So even though the stored procedure may be secure, an attacker may be able to run commands (just not see the output). 

The key to not writing vulnerable code is to not write it ever.  Whether it is a proof of concept, just some test code, or actual production code, take the time to use secure methods.  This secure way will be second nature and SQL injection reduction will be on its way.

Supportively Spread the Word

The key here is Supportively!!  Yes, we have been talking about SQL injection for years, but have we been doing it the right way, to the right people?  First, enough with the “Developers Suck”, “Your code sucks!” nonsense.  This is not productive and is probably much more destructive to the relationship between security and developers.  Second, security practitioners meet up at their cons and talk about this all year long.  This may sound crazy, but it is not the security practitioners that are writing the code.  We need to get the information into the developer’s hands and minds.  Just throwing the information on a blog (like this) or on a website like OWASP or SANS is not enough to get developers the information.  I don’t even want to guess at the number of developers that have never even heard of OWASP, but I would venture it is higher than you think.  Everyone needs to help spread the word.  Security is talking about it, developers need to be talking about it.  Major development conferences rarely have any content that is security related, that needs to change.  It needs to be thrown in everyone’s lap.   If you see someone writing something insecure, let them know so they can learn.  We can’t assume everyone knows everything.

Lets start including the secure way of writing SQL Queries in our tutorials, books, classes so all we see is the right way to do it.  I mentioned this a year or so ago and everyone cried that it would make the code samples in books and tutorials too long and impossible to follow.  First, I disagree that it would be that detrimental.  Second, where do developers get a lot of their code? From tutorials, samples, books, classes.  We don’t reinvent the wheel when we need to do something.  We look for someone that has done it, take the concept, make modifications to work in our situation and run with it.  All too often, this leads to a lot of vulnerabilities because we refuse to write secure code that is put out for anyone to use. We all need to get better at this.  And if you are the author, maybe it adds a few pages to your book ;).  

Take Responsibility

We can no longer blame others for the code we write.  Maybe the code was copied from an online resource.  As soon as it is in your paws, it is your code now.  It is not the fault of MSSQL or Oracle because they allow you to write dynamic  SQL queries.  This is the power of the system, and some people may just use it.  It is our responsibility to know how to use the systems we have. Many frameworks now try to help stop SQL Injection by default.  If you are relying on frameworks, know how they work, and keep them patched.  We just saw Ruby release a patch to fix a SQL Injection issue. 

Conclusion

So maybe this was a lot of rambling, or maybe it will mean something and get a few people thinking about defending against SQL Injection.  I apologize for some small tangents, those are part of another post that will be coming soon.  The purpose of this post is to start setting some goals that we can achieve in 2013.   Not everyone can eat an entire apple in one bite, so lets take some small bites and really chew on them for the year.  Lets focus on what we can do and do it well.

Tags: , ,

Comments

Comments are closed.