February 13, 2009 at 5:30 am
Hi Guys,
Can I have your assistance please.
I'm trying to write the query below but I'm having issue with the line below the WHERE clause.
This is the result from @query:
SELECT ID, Email, Class, Session, Quantity FROM TEST WHERE session IN (@Session)
The @Session above should be 'HCI' but I can't get it to come across as 'HCI.
DECLARE @Session Varchar(20)
SET @Session = 'HCI'
DECLARE @query NVARCHAR(4000)
SET @query = '
SELECT
ID
, Class
, Session
, Quantity
FROM
TEST
WHERE
Session IN (' + '@Session'+')'
SELECT @query
February 13, 2009 at 5:46 am
Where Session IN (''' + @Session + ''')'
By putting quotes around @Session you're treating it as a string literal, not as a variable.
Why dynamic SQL for this?
Are you aware this is vulnerable to SQL Injection?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 13, 2009 at 5:47 am
SET @query = '
SELECT
ID
, Class
, Session
, Quantity
FROM
TEST
WHERE
Session IN (''' + @Session + ''')'
February 13, 2009 at 5:53 am
GilaMonster (2/13/2009)
Where Session IN (''' + @Session + ''')'By putting quotes around @Session you're treating it as a string literal, not as a variable.
Why dynamic SQL for this?
Are you aware this is vulnerable to SQL Injection?
Thanks, it is working now.
What do you mean vulnerable to SQL Injection, sorry I'm new to these?
I am creating a reporting services report, this is my full query.
DECLARE @Session Varchar(20)
SET @Session = 'HCI'
DECLARE @cols NVARCHAR(2000)
SELECT @cols = COALESCE(@cols + ',[' + Session + ']',
'[' + Session + ']')
FROM TestView
WHERE Session = @Session
DECLARE @query NVARCHAR(4000)
SET @query = N'SELECT ID, Email, '+
@cols + ',class
FROM
(SELECT
ID
, Class
, Session
, Quantity
FROM
TEST
WHERE
Session IN (''' + @Session +''')
)p
PIVOT
(
SUM([Quantity])
FOR Session IN
( '+
@cols +' )
) AS pvt'
EXECUTE(@query)
February 13, 2009 at 6:05 am
sun_kuang (2/13/2009)
What do you mean vulnerable to SQL Injection, sorry I'm new to these?
Google SQL injection. If you're working with dynamic or ad-hoc SQL, you need to be aware of it.
As a very basic intro, think what will happen if someone sets the value for @session to this:
'); DELETE FROM test --
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 13, 2009 at 6:13 am
GilaMonster (2/13/2009)
sun_kuang (2/13/2009)
What do you mean vulnerable to SQL Injection, sorry I'm new to these?Google SQL injection. If you're working with dynamic or ad-hoc SQL, you need to be aware of it.
As a very basic intro, think what will happen if someone sets the value for @session to this:
'); DELETE FROM test --
Thank you for your assistance and everyone else.
I will read on it.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply