sp_executesql 何處可用變數

朋友問了個概念問題如下:

Dim cmd As New SqlCommand("DECLARE @i INT;SET @i=@j;select @k from sys.objects", cnn)
cmd.Parameters.Add(New SqlParameter("@j", 10))
cmd.Parameters.Add(New SqlParameter("@k", "*"))

則 ADO.NET 實際組的語法如下:

exec sp_executesql N’DECLARE @i INT;SET @i=@j;select @k from sys.objects’,N’@j int,@k nvarchar(1)’,@j=10,@k=N’*’

哪那些語法部分可以當作參數用呢?

我想基本 Rule 是:參數部分在 sp_executesql 預存程序的 SQL 語法中會被當作常數,而非關鍵字或物件名稱,做些簡單的驗證(Where 部分就不測了,因為那是最常用的部分):

use tempdb
go
–常數值的取代結果
exec sp_executesql
N’DECLARE @i INT;SET @i=@j;select @i;select @k,@t from sys.objects’,
N’@j int,@k nvarchar(1),@t nvarchar(100)’,
@j=10,@k=N’*’,@t=N’sys.objects’
go

image

/*
訊息 102,層級 15,狀態 1,行 1
接近 ‘@i’ 之處的語法不正確。
*/
exec sp_executesql
N’create table @i(@j @k)’,
N’@i sysname,@j sysname,@k sysname’,
@i=N’t’,@k=N’c1′,@t=N’int’
GO

/*
訊息 102,層級 15,狀態 1,行 1
接近 ‘@i’ 之處的語法不正確。
*/
exec sp_executesql
N’@i’,
N’@i nvarchar(1000)’,
@i=N’create table t(c1 int)’
GO

–當然可以,所以 exec 比較會有 Sql Injection
exec sp_executesql
N’EXEC(@i)’,
N’@i nvarchar(1000)’,
@i=N’create table t(c1 int)’

/*
訊息 1087,層級 15,狀態 2,行 1
必須宣告資料表變數 "@i"。
*/
exec sp_executesql
N’INSERT @i VALUES(1)’,
N’@i sysname’,
@i=N’t’

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 變更 )

Twitter picture

You are commenting using your Twitter account. Log Out / 變更 )

Facebook照片

You are commenting using your Facebook account. Log Out / 變更 )

Google+ photo

You are commenting using your Google+ account. Log Out / 變更 )

連結到 %s

%d 位部落客按了讚: