RiCo技術農場

使用sp_executesql的好處

1.針對dynamic sql可以提高執行計畫重用率

2.減少sqlinjection風險

 

使用exec的好處

1.方便

2.簡單

/*
RiCo 技術農場
https://dotblogs.com.tw/ricochen

使用 sp_executesql 取代 exec
*/
drop table if exists dbo.sqlinjection 

create table sqlinjection (c1 varchar(10)) 
insert into sqlinjection select 'rico is me'

declare @c1 nvarchar(50) = N'rico''; drop table dbo.sqlinjection; --''';
declare @sql nvarchar(MAX) = N'select * from dbo.sqlinjection where c1 = @c1;';
exec sp_executesql @sql, N'@c1 nvarchar(50)', @c1;

declare @c1 nvarchar(50) = N'rico''; drop table dbo.sqlinjection; --''';
declare @sql nvarchar(MAX) = N'select * from dbo.sqlinjection where c1 = ''' + @c1 + '''';
exec(@sql);

if (exists (select 1 
            from INFORMATION_SCHEMA.TABLES 
            where TABLE_SCHEMA = 'dbo' 
            and  TABLE_NAME = 'sqlinjection'))
 select * from dbo.sqlinjection 
else
 select N'找不到資料表'

 

 

參考

淺談執行計畫快取和重用

Using sp_executesql