People use Dynamic SQL for various purposes
One of the disadvantages of it is SQL injection that can attack your database by doing the things that you didn't want to do
Consider a case where usage of Dynamic SQL in unavoidable
You have a table that records the daily activities of the employees Your manager who has little knowledge in RDBMS wants to have an application that lets him to write a dynamic query to get required informations Consider the following table
create table daily_activities
(
userid int,
task varchar(100),
hours int,
task_date datetime
)
insert into daily_activities
Select 1001,'Coding',8,'20100101' union all
Select 1001,'Ducumentation',7,'20100111' union all
Select 1002,'Testing',12,'20100223' union all
Select 1003,'Meeting',10,'20100301' union all
Select 1004,'Demo',9,'20100101' union all
Select 1003,'Training',8,'20100414' union all
Select 1003,'Coding',11,'20100502'
Now the manager writes a filter like
task='coding' and userid=1001
If you want to bring out the informations based on the above filters,only way is to use dynamic sql. Now create a procedure
create procedure proc_user_analysis
(
@where varchar(1000)
)
as
declare @sql varchar(8000)
set @sql='select * from daily_activities where '+@where
exec(@sql)
Now execute it by applying some filters
exec proc_user_analysis 'task=''coding'' and userid=1001'
Result
userid task hours task_date
----------- -------------- ----------- -----------------------
1001 Coding 8 2010-01-01 00:00:00.000
exec proc_user_analysis 'task=''Training'' and task_date between ''20100101'' and ''20100501'''
Result
userid task hours task_date
----------- ---------------- ----------- -----------------------
1003 Training 8 2010-04-14 00:00:00.000
Now see what happens when someone has access to that application and filter it using
exec proc_user_analysis '1=1'
All records from the table are displayed
Now there is a chance to delete some data from the table
exec proc_user_analysis '1=0; delete from daily_activities where task=''meeting'''
Now meeting informations will be missing from the table.
Ok. How should we avoid this?
There are atleast two methods
1 Use sp_executesql instead of exec as described here
2 Use derived table Let us alter the procedure
Method 1
alter procedure proc_user_analysis
(
@where varchar(1000)
)
as
declare @sql varchar(8000)
declare @i int, @left varchar(1000), @right varchar(1000)
select @left='', @right=''
select @i= cast(rand()*1000 as int)%20
set @sql='(select * from daily_activities where '+@where
while @i>0
begin
select @left=@left+'(select * from ',@right=@right+') as t'
set @i=@i-1
end
set @sql='select * from '+@left+@sql+@right+') as t '
exec(@sql)
Method 2
alter procedure proc_user_analysis
(
@where varchar(1000)
)
as
declare @sql varchar(8000)
declare @split varchar(100), @count int, @charcount int, @char char(1)
select @count=1, @charcount=0, @split=@where
while len(@split)>0
begin
select @char=substring(@split,1,1)
set @charcount=@charcount+case when @char='(' then 1 when @char=')' then -1 else 0 end
if @charcount=-1
begin
select 'Invalid input'
return
end
select @count=@count+1, @split=substring(@where,@count,len(@where))
end
set @sql='select * from (select * from daily_activities where '+@where+') as t '
exec(@sql)
Execute this to see if it works correctly
exec proc_user_analysis 'task=''coding'''
Result
userid task hours task_date
----------- ---------------- ----------- -----------------------
1001 Coding 8 2010-01-01 00:00:00.000
1003 Coding 11 2010-05-02 00:00:00.000
Execute the code that caused deletion of meeting informations from the table
exec proc_user_analysis '1=0; delete from daily_activities where task=''meeting'''
Result
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ';'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.
As you see using a derived table avoided the deletion and resulted to an error and your data are safe
So if you use dynamic sql to return a resultset, always use a derived table if you suspect about SQL injection