Saturday, April 11, 2009

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)
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


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


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.


  1. You should be aware that your "fix" presented in 'SaferDynamicSQL' is actually susceptable to the exact same injection attack.

  2. Anonymous,

    SaferDynamicSQL uses bind parameter syntax, therefore the value of the parameter will not alter the structure of the SQL statement, therefore removing the risk of SQL injection attempts via manipulation of the value.

    So in the case that the user enters ';drop table users -- as his input the entire string will be treated as the value that UserName is compared against.

  3. Forgive me if I'm being slack, but wouldn't you just do

    create proc SaferDynamicSQL(@userName nvarchar(25)) as
    select TelephoneNumber from users where UserName = @p_userName


  4. if I am using parameterized query in the right way (binding variables), then do i need to perform input validation for those dangeriouis characters?
    the parameterized query should neutralize them, correct?

  5. <quote>
    Forgive me if I'm being slack, but wouldn't you just do

    create proc SaferDynamicSQL(@userName nvarchar(25)) as
    select TelephoneNumber from users where UserName = @p_userName


    Your are correct, that would solve the problem, my goal here was to demonstrate how dynamic SQL can cause this particular vulnerability and how to protect against it. There are cases where dynamic SQL is either the simplest or only option, this example is neither of those, but it serves for the purposese of the explanation (I hope).

  6. Yi,

    If you are using binding variable that would go a long way to protecting you from SQL injection. However I do recommend that you continue to validate you r input snce there are a host of other vulnerabilities eg. Cross Site Scripting (XSS) and Cross Site Request Forgery (CSRF) to name two. Even if you are not building a web appilication today, who knows what the future holds, you might need to build a web portal to provide certain information via a browser interface and suddenly your application becomes vulnerable.

    In short it is probably always good to validate input stemming from external sources.