SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Search Using Stored Procedure


Search Using Stored Procedure

Author
Message
enriquezreyjoseph
enriquezreyjoseph
SSC-Enthusiastic
SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)

Group: General Forum Members
Points: 181 Visits: 191
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 :-)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214662 Visits: 41979
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
mahmad_khoja
mahmad_khoja
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1281 Visits: 83
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
mahmad_khoja
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1281 Visits: 83
One correction :

SET @SqlQueryFirstName = @SqlQuery + ' firstname = ' + @firstname
kapil_kk
kapil_kk
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5308 Visits: 2767
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
Sean Lange
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62459 Visits: 17955
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
enriquezreyjoseph
enriquezreyjoseph
SSC-Enthusiastic
SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)

Group: General Forum Members
Points: 181 Visits: 191
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
pietlinden
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14052 Visits: 14128
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
enriquezreyjoseph
SSC-Enthusiastic
SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)

Group: General Forum Members
Points: 181 Visits: 191
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
Sean Lange
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62459 Visits: 17955
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.

_______________________________________________________________

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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search