After listening to Joe Stagner on .NET Rocks, I was surprised at the response with regard to his statement that he is able to perform a SQL injection even with parameterized queries. Why was I surprised? Well when he made that statement immediately I could think of scenarios where this was possible. More statements made me feel that many people might not realize that this could be a very real problem so I have decided to share some of these scenarios, for this post I will keep it simple and start with the most obvious.
One of the more frowned on practices when writing a stored procedure is the use of dynamic SQL. Be that as it may, I still come across more than a fair share of dynamic SQL and here in lies a potential vulnerability. To demonstrate this I provide the following procedure.
create proc VulnerableDynamicSQL(@userName nvarchar(25))
as
declare @sql nvarchar(255)
set @sql = 'select * from users where UserName = '''
+ @userName + ''''
exec sp_executesql @sql
go
Obviously this procedure is contrived to make the point and not to do anything useful and definitely would not warrant the use of dynamic SQL. The unsuspecting programmer that is calling this procedure from .NET code might think do something like the following.
oCmd.CommandText = "VulnerableDynamicSQL";
oCmd.CommandType = CommandType.StoredProcedure;
oCmd.Parameters.Add( "@userName", strUserName);
oCon.Open();
string result = (string)oCmd.ExecuteScalar();
oCon.Close();
Feeling perfectly safe with the above code, the .NET programmer has written a secure piece of code. But unfortunately this code is just as susceptible to a SQL injection, albeit not at the .NET code level. For example the following user input could be used to exploit the above vulnerability.
';drop table users --
This input from the user would result in the following SQL being executed:
select * from users where UserName = '';drop table users --'
Again I have chosen the above for simplicity and obviously this would require adequate permissions to be successful. This does not make this sample any less significant, had the procedure been a procedure used to authenticate users of a system; only slightly more complex injection would be required to bypass the authentication.
Can dynamic SQL be done more securely? The answer to that 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 us 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 * 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.