Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
sql_server 217
t-sql 211
tsql 116
sqlserver 96
BRH 78
#SQLServer 66
#TSQL 56
SQL Server 34
function 11
SSMS 9

Archive · View All
August 2007 17
August 2010 8
June 2012 7
June 2011 7
November 2007 7
August 2012 6
May 2012 6
November 2011 6
August 2011 6
October 2011 6

Madhivanan's TSQL Blog

Derived Table - New approach to avoid SQL Injection

May 14 2010 6:20AM by Madhivanan   

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

Tags: t-sql, sql_server, BRH, sql_injection, dynamic_sql, #TSQL,


Madhivanan
3 · 40% · 12947
0
Liked
 
0
Lifesaver
 
 
0
Learned
 
0
Incorrect



Submit

11  Comments  

  • Nice idea, and it is new to me. But I doubt in it is effectiveness, because I actually can think of someway to break it.

    commented on May 14 2010 9:08AM
    Muhammad Al Pasha
    27 · 6% · 1920
  • Muhammad Al Pasha ,

    Thanks for the comment I dont think you can break it becuase of usage of derived table Whatever you try, you will end up with error Let me know if you have a code that breaks this

    commented on May 14 2010 9:33AM
    Madhivanan
    3 · 40% · 12947
  • I modified your own code, and it worked, and here it is: exec procuseranalysis '1=1) AS allrows; delete from dailyactivities where task=''meeting''; select (1'

    commented on May 14 2010 9:53AM
    Muhammad Al Pasha
    27 · 6% · 1920
  • Thanks

    I modified the procedure so now it is difficult to break it

    Let me know in how many attempts you are able to break it

    commented on May 14 2010 10:14AM
    Madhivanan
    3 · 40% · 12947
  • As you said in your last comment, you are making it more difficult, but I should mention that if you used a random value to nest tables then it is eventually will hit the hacker static assumption (no matter what it is), and your modification will make your procedure more complex, and cause many problems regarding cash,recompilation, procedural code (WHILE) and performance. So you should think carefully does it worth it, espically that it is breakable. By the way your modification affects adding other statements, but it did not cover other ways of sql injection like getting information from other tables, and this an example of this kind (and it works immediately): exec procuseranalysis '1=1) AS all_rows cross join (select * from syslogins '

    commented on May 14 2010 10:58AM
    Muhammad Al Pasha
    27 · 6% · 1920
  • I have included method 2 that can avoid unneccessary joins

    commented on May 17 2010 10:51AM
    Madhivanan
    3 · 40% · 12947
  • Please don't take thi the wrong way, but this is a terrible idea and very bad security advice to be giving people.

    Even your last attempt (at making sure the brackets are balanced) is completely wrong. What about text like (to modify Muhammad's comment):

    '1=1 /* ( */ ) AS all_rows cross join (select * from syslogins '

    (note the commented-out opening bracket which will throw out your checks).

    Web security is a very important thing and I think amateurish advice like this is not only unhelpful it's actually very very dangerous. Preventing SQL injection like this is not easy and your attempts may fool people into thinking they are protected when they're certainly not.

    commented on Sep 20 2010 8:46AM
    minodude
    2221 · 0% · 6
  • Try out this

    create procedure procuseranalysis
    (
    @where varchar(1000)
    )
    as

    SET @where =REPLACE( REPLACE(REPLACE(REPLACE(@where, '--', ''), ';', ''), '\*', ''), '*/', '')

    declare @sql varchar(8000)
    set @sql='select * from daily_activities where '+@where
    exec(@sql)

    commented on Jun 16 2011 12:43AM

    -1 · -1% · -1
  • Netra: no, no, a thousand times no. This won't work. In fact, it's completely useless.

    For example, the string "---;---" will replace to "-;-" (through the innermost replace), and then to "--" (the second one). Suddenly you have a "--" in your string, which you're trying to avoid.

    THIS IS WRONG. Treating untrusted input as trusted domain language (in this case SQL) is a fundamentally broken approach and will never ever work, unless you can guarantee you're smarter than every possible malicious user. And I promise you, you can't.

    commented on Jun 16 2011 12:56AM
    minodude
    2221 · 0% · 6
  • Thanks minodude,

    Your comment is highly appreciated.
    After reading your comment I changed by SP to avoid injection (may be up to some extend only).
    Kindly let me know if it still has bug.

    once again thank you very much minodude. Please see my new code.

    create procedure procuseranalysis
    (
    @where varchar(1000)
    )
    as

    SET @where = REPLACE(@where, ';', '')
    SET @where = REPLACE(@where, ',', '')
    SET @where = REPLACE(@where, '-', '')
    SET @where = REPLACE(@where, '*', '')
    SET @where = REPLACE(@where, '''', '')

    declare @sql varchar(8000)
    set @sql='select * from daily_activities where '+@where
    exec(@sql)

    commented on Jun 16 2011 5:42AM

    -1 · -1% · -1
  • Netra,

    Try passing in:

    1 = 1' + CHAR(59) + 'DROP TABLE daily_activities'

    (59 is the ASCII code for the semicolon)

    This is a losing battle! There are function calls, hexadecimal literals, and a whole bunch of other things which can be done to circumvent this kind of protection.

    commented on Jun 16 2011 6:56AM
    minodude
    2221 · 0% · 6

Your Comment


Sign Up or Login to post a comment.

    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]