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.
There is another question here on SO with some great answers on SQL Injection...
Are Parameters really enough to prevent Sql injections?
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...
If you're creating a statement in a stored proc and using sp_executesql, a parameterized query is a false safety net.
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).
Try searching for vulnerabilities in ADO.net, there can be a security flaw.
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.
