Examples of SQL injection even when using SQLParameter in .NET?

Question

I heard that SQL Injection can still be possible when using ADO.NET SQLParameter (paramterised query) for SQL Server.

I am looking for real examples in C#/VB code as proof.

EDIT: I am looking for SPECIFIC working examples. Not introductions to sql injection or how to prevent it.

Answer 1

There is another question here on SO with some great answers on SQL Injection...

http://stackoverflow.com/questions/306668/are-parameters-really-enough-to-prevent-sql-injections/306676#306676

This example comes directly from the link above from Steven A. Lowe.

An example, where parameter @p1 is intended to be a table name

create procedure dbo.uspBeAfraidBeVeryAfraid ( @p1 varchar(64) ) 
AS
    SET NOCOUNT ON
    declare @sql varchar(512)
    set @sql = 'select * from ' + @p1
    exec(@sql)
GO

Here is some further reading...

• Mikes Dot Netting
• ASP.NET Forums
• MSDN

Answer 2

If you're creating a statement in a stored proc and using sp_executesql, a parameterized query is a false safety net.

Answer 3

Try searching for vulnerabilities in ADO.net, there can be a security flaw.

Answer 4

A specific example...

create procedure dbo.spVulnerable
@firstname varchar(200)
as
exec ('select id from tblPerson where firstname = ''' + @firstname + '''')
go

I might have gotten the syntax wrong but no matter how you parameterize @firstname it is still vulnerable to something like the following:

"Joe' or 1=1"

As long as you use dynamic SQL you are potentially vulnerable to SQL injection. Other than using parameterized SQL, the only solution is to whitelist your input (or, if you're feeling brave, try to strip the input of dangerous characters).

Answer 5

Say you have a products catalog for your web size and your search page allows to search by product name, description, color and size (say you sell bras):

create table [products] (
    product_id int identity(1,1) not null primary key
    , name varchar(256)
    , description varchar(max)
    , color varchar(256)
    , size varchar(256));
GO  

create procedure usp_dynamicSearch
    @product varchar(256) = NULL
    , @description varchar(256) = NULL
    , @color varchar(256) = NULL
    , @size varchar(256) = NULL
as
begin
    set nocount on;
    declare @sql nvarchar(max)
     , @and nvarchar(5);
    set @sql = N'SELECT 
     product_id, name, description, color, size 
     FROM products
     WHERE ';
    set @and = N'';
    if (@product is not null) 
    begin
     set @sql = @sql + N'name LIKE ''' + @product + N'''';
     set @and = N' AND ';
    end
    if (@description is not null) 
    begin
     set @sql = @sql + @and + N'description LIKE ''' + @description + N'''';
     set @and = N' AND ';
    end
    if (@color is not null) 
    begin
     set @sql = @sql + @and + N'color = ''' + @color + N'''';
     set @and = N' AND ';
    end
    if (@size is not null) 
    begin
     set @sql = @sql + @and + N'size = ''' + @size + N'''';
    end 
    exec sp_executesql @sql;
end
GO

You use a stored procedure that dynamically constructs a SQL appropiate for the search. You invoke it by passing parameters:

exec usp_dynamicSearch @color = N'Red', @size = N'58-DD';

Since the procedure constructs dynamic SQL in a careless fashion, is still open to SQL injection:

exec usp_dynamicSearch @color = N'Red', @size = N''';
INSERT INTO products (name, description) 
values (''31337'', ''haxorz!''); 
--';

The unwanted product was sinserted in the catalog (to make this a bening attack...). In this case, the appropiate fix is to use parameters in the dynamic SQL as well and further pass the parameters to the sp_executesql invocation:

alter procedure usp_dynamicSearch
    @product varchar(256) = NULL
    , @description varchar(256) = NULL
    , @color varchar(256) = NULL
    , @size varchar(256) = NULL
as
begin
    set nocount on;
    declare @sql nvarchar(max)
     , @and nvarchar(5);
    set @sql = N'SELECT 
     product_id, name, description, color, size 
     FROM products
     WHERE ';
    set @and = N'';
    if (@product is not null) 
    begin
     set @sql = @sql + N'name LIKE @product';
     set @and = N' AND ';
    end
    if (@description is not null) 
    begin
     set @sql = @sql + @and + N'description LIKE @description';
     set @and = N' AND ';
    end
    if (@color is not null) 
    begin
     set @sql = @sql + @and + N'color = @color';
     set @and = N' AND ';
    end
    if (@size is not null) 
    begin
     set @sql = @sql + @and + N'size = @size';
    end 
    exec sp_executesql @sql , N'@product varchar(256)
     , @description varchar(256)
     , @color varchar(256)
     , @size varchar(256)'
     , @product, @description, @color, @size;
end
GO

So sp_executesql and dynamic SQL is the main concern. Other than that, there are also various system procedures that build dynamic SQL under the covers and historically some were proven to be vulnerable, specially on SQL 2000.