views:

67

answers:

1

I have been investigating Table-Valued Parameters in SQL Server 2008, and I've discovered that when passing such a parameter to a stored procedure, a query such as the following is sent to the database server:

declare @p1 dbo.MyTypeName
insert into @p1 values(N'row1col1',N'row1col2')
insert into @p1 values(N'row2col1',N'row2col2')
insert into @p1 values(N'row3col1',N'row3col2')
insert into @p1 values(N'row4col1',N'row4col2')
insert into @p1 values(N'row5col1',N'row5col2')

exec StoredProcedureName @MyParam=@p1

My question is, how secure against SQL injection attacks is this, given that the insert statements are not parameterized? I tried the most trivial attack against it, and the quotes were properly escaped, but has anyone run an exhaustive test, or is there something else going on here that would protect me?

+1  A: 

You don't need to worry about SQL Injection attacks with TVPs.

This seems like a bunch of singleton calls but it's not. As my colleage Keith Elmore at Microsoft CSS pointed out, that is just the convention used to allow the data to be displayed or copied/pasted into a query window and run. The TVP isn’t actually implemented in that way—the rows are streamed across just like a bulk insert—but it may have led to this impression that it does simple inserts.

Source: http://blogs.msdn.com/mikecha/archive/2009/08/07/two-fast-ways-to-bulk-insert-client-generated-data-to-sql-database.aspx

Randolph Potter
Perfect, thanks. I looked and looked, but I couldn't find anything.
Mark