Search Using Stored Procedure

Question

Search Using Stored Procedure

enriquezreyjoseph

Ten Centuries

Points: 1291
More actions
September 22, 2013 at 7:51 pm

#298133

Hi to all 🙂
I want to have a result like this ---- SELECT * FROM TestMyView WHERE firstname = 'test5'
but my code will produce like this ---- @sqlquery firstname = @firstname
what is wrong with this??...
use Biography
Declare @firstname varchar(50),@middlename varchar(50),@lastname varchar(50), @sex varchar(50),@status varchar(50),@SqlQuery varchar(max),@bioID int, @SqlQueryFirstName varchar(max)
SET @bioID = 13
SET @firstname = 'test5'
SET @middlename = 'test'
SET @lastname = 'tes'
SET @sex = 'Female'
SET @status = 'single'
SET @sqlquery = ''
SET @SqlQueryFirstName = ''
SET @sqlquery = 'SELECT * FROM TestMyView WHERE '
SET @SqlQueryFirstName = '@SqlQuery ' + 'firstname = @firstname'
print(@SqlQueryFirstName)
thanks 🙂

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

Probably the biggest thing wrong with it is that it's very prone to SQL Injection. Please see the following article for how to do "Catch All" queries without the chance of SQL Injection.

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mahmad_khoja SSCommitted Points: 1511 More actions · Answer 2

Hi,

Instead of writing SET @SqlQueryFirstName = '@SqlQuery ' + 'firstname = @firstname' this use below code.

SET @SqlQueryFirstName = @sqlquery + 'firstname = @firstname'

i.e. remove single quotes from @sqlquery.

mahmad_khoja SSCommitted Points: 1511 More actions · Answer 3

One correction :

SET @SqlQueryFirstName = @sqlquery + ' firstname = ' + @firstname

kapil_kk SSC-Insane Points: 21316 More actions · Answer 4

Instead of this:

SET @SqlQueryFirstName = '@SqlQuery ' + 'firstname = @firstname'

Write this:

SET @SqlQueryFirstName = @sqlquery + 'firstname ='+ @firstname

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

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.

_______________________________________________________________

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/

enriquezreyjoseph Ten Centuries Points: 1291 More actions · Answer 6

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

pietlinden SSC Guru Points: 63506 More actions · Answer 7

Young Jedi,

Read these:

http://www.sqlservercentral.com/articles/Security/sqlinjection/1269/

http://www.sqlservercentral.com/articles/Editorial/77168/

http://www.sqlservercentral.com/articles/sql+injection/65129/

I would consider dynamic SQL as an absolute last resort. Not a tool to be used by ex-Access programmers (I am one!) that have moved on to SQL Server. If the articles above don't scare you, ask your boss what would happen if you let someone run something like that...

enriquezreyjoseph Ten Centuries Points: 1291 More actions · Answer 8

pietlinden (9/23/2013)
Young Jedi,
Read these:
http://www.sqlservercentral.com/articles/Security/sqlinjection/1269/
http://www.sqlservercentral.com/articles/Editorial/77168/
http://www.sqlservercentral.com/articles/sql+injection/65129/
I would consider dynamic SQL as an absolute last resort. Not a tool to be used by ex-Access programmers (I am one!) that have moved on to SQL Server. If the articles above don't scare you, ask your boss what would happen if you let someone run something like that...

thanks piet 🙂

But what is the meaning of "Young Jedi"? hehe

Sean Lange SSC Guru Points: 286590 More actions · Answer 9

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/

enriquezreyjoseph Ten Centuries Points: 1291 More actions · Answer 10

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/[/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.

Thank you sean :-)..your the champion....you made my day (-: