Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Search Using Stored Procedure Expand / Collapse
Author
Message
Posted Tuesday, September 24, 2013 6:28 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, October 21, 2013 3:10 AM
Points: 79, Visits: 191
Sean Lange (9/24/2013)
enriquezreyjoseph (9/23/2013)
Sean Lange (9/23/2013)
kapil_kk (9/23/2013)
Instead of this:
SET @SqlQueryFirstName = '@SqlQuery ' + 'firstname = @firstname'

Write this:
SET @SqlQueryFirstName = @SqlQuery + 'firstname ='+ @firstname


Instead of this, read the article that Jeff suggested. The approach of executing parameters is VERY VERY VERY bad idea.


Hi Sean

Looks very interesting to me....can you give a link to me regarding "The approach of executing parameters is VERY VERY VERY bad idea"..??..

Thanks my friend Cheers!!!


How about this one?

http://bobby-tables.com/

If that doesn't explain it how about this simple code example.

--First we need to setup a table
create table MyLoginTable
(
LoginID int identity primary key,
UserName varchar(100),
UserPassword char(36)
)

insert MyLoginTable
select 'JModen', 'JModenPassword' union all
select 'GShaw', 'GShawPassword' union all
select 'SJones', 'SJonesPassword'

go

--Now we need to create a proc to pass our parameters
create proc MyLoginProc
(
@UserName varchar(100),
@Password varchar(40)
) as
declare @SQL varchar(200)
set @SQL = 'select * from MyLoginTable
where UserName = ''' + @UserName
+ ''' and UserPassword = ''' + @Password + ''''

print @SQL
exec( @SQL)

go

--This looks pretty harmless. Why is executing parameters such a bad idea?
exec MyLoginProc 'jmoden', 'JModenPassword'

--what happens when you run this one?
exec MyLoginProc ''' or 1 = 1--', ''

--How about this one? I just cleaned up the proc and the table from your database by using parameters to a proc.
exec MyLoginProc ''' or 1 = 1;drop proc MyLoginProc; drop table MyLoginTable;--', ''


Still not convinced that executing parameters directly is a bad idea? Check out the article I suggested from Gail. It shows you how to use dynamic sql and keep it safe from sql injection.



Thank you sean ..your the champion....you made my day (-:
Post #1498094
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse