Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase ««12

Search Using Stored Procedure Expand / Collapse
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?

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'


--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)


--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 made my day (-:
Post #1498094
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse