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/[/url]
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.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/