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 Sunday, September 22, 2013 7:51 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: 81, 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
Post #1497256
Posted Sunday, September 22, 2013 11:23 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:13 PM
Points: 36,995, Visits: 31,514
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1497264
Posted Monday, September 23, 2013 12:30 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, June 10, 2014 3:10 AM
Points: 1,134, Visits: 82
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.



Post #1497274
Posted Monday, September 23, 2013 12:47 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, June 10, 2014 3:10 AM
Points: 1,134, Visits: 82
One correction :

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

Post #1497277
Posted Monday, September 23, 2013 4:46 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:34 AM
Points: 1,888, Visits: 2,326
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/
Post #1497319
Posted Monday, September 23, 2013 8:18 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:32 PM
Points: 13,302, Visits: 12,168
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 Moden's 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)
Post #1497405
Posted Monday, September 23, 2013 6:34 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: 81, 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!!!
Post #1497604
Posted Monday, September 23, 2013 9:14 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 12:13 AM
Points: 745, Visits: 4,776
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...
Post #1497629
Posted Monday, September 23, 2013 9:19 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: 81, 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
Post #1497631
Posted Tuesday, September 24, 2013 7:43 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:32 PM
Points: 13,302, Visits: 12,168
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 Moden's 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)
Post #1497856
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse