Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL2000 SQL-server-2005 , +
If write dynamic query inside store procedure will it have threat of SQL Injection attack?
 
this are two example...
create procedure Details
(
   @Date int,
   @CompId int
)
as 
begin
 
declare @a varchar(max); 
 
select @a = @a + ' select VouNo,VouDesc,convert(varchar(100),CAST(' + convert(varchar(100), Amount) + ' AS NUMERIC(18,'+ (select Afterpoint from tbl_setting where CompId = @CompId) + '))) as FormattedAmount from tbl_Trans where vouDate=' + @Date;
 
exec(@a);
 
end
 
Alter procedure VisitorDetails1
@name nvarchar(50),
@City nvarchar(100),
@Dept nvarchar(max),
@TableName nvarchar(50)
as
 
IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME=@TableName)) 
--IF db_id('@TabName') IS NOT NULL
--IF OBJECT_ID('@TabName','U') is not null
begin
 
Declare @set nvarchar(50)
set @set='insert into '+@TableName+'(Name,City,Dept) values(''' + @name + ''',''' + @City + ''',''' + @Dept + ''')'
 
exec(@set)
Print 'Success'
end
else
begin
print 'Table is Not there'
end
 
I have used Dynamic SQL in these sps,...
Is it Easy to pass "SQL Injection" on these prod? if yes then how?
Posted 7-Feb-13 20:20pm

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

In yr 2nd example, if I called the SP with parameter name parameter something like
 
"'x','y','z'); drop table Customer;"
 
tehn your @set will be something like
 
insert into mytable (name, city,dept) values('x','y','z'); drop table Customer;
 
which probably isn't what you want.
  Permalink  
Comments
Aarti Meswania at 8-Feb-13 2:24am
   
then my statement in second sp will be..
 
insert into mytable (name, city,dept) values('x','y','z''); drop table Customer');
 
not
 
insert into mytable (name, city,dept) values('x','y','z'); drop table Customer;
 
Am I right? so it will not be effected by sql injection
because
"'z'); drop table Customer;"
will taken as value for last parameter 'z'
so... the query will insert this string "'z'); drop table Customer;" inside z column it will not execute it :)
_Maxxx_ at 8-Feb-13 5:38am
   
My text was just an example(I said 'something like' ) typed on the fly - get the quotes right & I think you could pass one or more parameters in such a way as to inject sql
Aarti Meswania at 8-Feb-13 5:40am
   
okay I got it you are right
 
thank you for response :)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 406
1 /\jmot 180
2 Suraj Sahoo | Coding Passion 170
3 Afzaal Ahmad Zeeshan 159
4 BillWoodruff 154
0 OriginalGriff 8,344
1 Sergey Alexandrovich Kryukov 7,407
2 DamithSL 5,634
3 Maciej Los 5,024
4 Manas Bhardwaj 4,986


Advertise | Privacy | Mobile
Web01 | 2.8.1411023.1 | Last Updated 8 Feb 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100