SQL Injection - Are parameterized queries safe?

This is a repost and rework of a popular blog entry from my previous blog, the original post can be found here.

SQL Injection is one of those vulnerabilities that crop up without even realizing it. Attackers potentially use SQL Injection to

• Bypass authentication
• Gain access to sensitive information
• Interrupt system availability by tampering with or destroying data
• Elevate privileges by tampering with data

As most developers already know, a few of the key ways to protect against SQL injection is to

1. Validate user input to ensure that the input conforms to the business requirements, and ensure that suspicious input is filtered to reduce the possible attack vectors.
2. Apply the principals of least privilege to further limit the scope of a successful hack
3. Use parameterized SQL queries rather than building SQL statements dynamically using string concatenation

Following the above guidelines will go along way to protect your software from a range of strategies used by potential hackers to penetrate your systems. This is not a complete guide, it is just a starting point, I am not even talking about the various web site vulnerabilities like cross site scripting etc.

The goal of this post is to demonstrate a scenario where parameterized queries could still pose a potential vulnerability. Using the following code as an example, a .NET developer might feel that the code is reasonably secure, using a nice parameterized query to a stored procedure.

oCmd.CommandText = "VulnerableDynamicSQL";
oCmd.CommandType = CommandType.StoredProcedure;
oCmd.Parameters.Add( "@userName", strUserName);
using (oCon)
{
  oCon.Open();
  string result = (string)oCmd.ExecuteScalar();
}

Everything seems fine, but unfortunately the code in the stored procedure could still pose a problem to the unsuspecting caller. The following rather contrived implementation of the stored procedure would leave the door wide open to SQL injection.

create proc VulnerableDynamicSQL(@userName nvarchar(25)) as
  declare @sql nvarchar(255)
  set @sql = 'select TelephoneNumber from users where UserName = ''' + @userName + ''''
  exec sp_executesql @sql

go

For example, the user input below, passed to the above stored procedure demonstrates a simple SQL Injection attack

';drop table users --

This input would result in the procedure formulating and executing the following SQL statement;

select TelephoneNumber from users where UserName = '';drop table users –-'

I have chosen the above for simplicity and obviously this would require adequate permissions to be successful, however this does not make the example any less significant. Had the procedure been a procedure used to authenticate users of a system; only a slightly more complex injection would be required to bypass the authentication and gain unauthorized admin access to the system.

As I mentioned above, the procedure could be written without using dynamic SQL, and resulted in more secure implementation. However there are situations in which dynamic SQL is indispensible and this raises the question, Can dynamic SQL be used more securely? The answer is a definite ‘YES’. Firstly you should always consider if you require dynamic SQL normally there is an alternate solution that does not require dynamic SQL. However if you have exhausted all possibilities and the only recourse is dynamic SQL then there is a safer way to use it. Just like we are able to use parameters for our SQL statements from within .NET, so can you use parameters for your dynamic SQL. The following procedure is a reimplementation using parameterized dynamic SQL.

create proc SaferDynamicSQL(@userName nvarchar(25))  as
  declare @sql nvarchar(255)
  set @sql = 'select TelephoneNumber from users where UserName = @p_userName'
  exec sp_executesql @sql, N'@p_userName nvarchar(25)', @p_userName = @userName

go

As you can see in this case I have defined a parameter to be used in the dynamic SQL statement called @p_userName. This parameter is defined as part of the call to sp_executesql and its value set in the same call.

Don’t make the mistake of thinking that security alone will prevent users from performing a successful SQL injection. It might prevent the user from dropping your tables, but you will still be open to many other forms of attack. When ever you concatenate strings to build SQL statements think carefully of the potential implications. Security requires a mindset where you are always questioning the implications of your decisions and even questioning things you might not have been responsible for.

And most importantly: Always validate user input.