Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Should this code include QUOTENAME

Status
Not open for further replies.

makeitwork09

Technical User
Sep 28, 2009
170
US
I found the following code at and was wondering if it needs to include QUOTENAME to help avoid SQL injection? And if so, where specifically is it required.

Code:
DECLARE @PivotColumnHeaders VARCHAR(MAX)
SELECT @PivotColumnHeaders = 
  COALESCE(
    @PivotColumnHeaders + ',[' + cast(Name as varchar) + ']',
    '[' + cast(Name as varchar)+ ']'
  )
FROM Sales.SalesTerritory

DECLARE @PivotTableSQL NVARCHAR(MAX)
SET @PivotTableSQL = N'
  SELECT *
  FROM (
    SELECT
      YEAR(H.OrderDate) [Year],
      T.Name,
      H.TotalDue
    FROM Sales.SalesOrderHeader H
    LEFT JOIN Sales.SalesTerritory T
      ON H.TerritoryID = T.TerritoryID
  ) AS PivotData
  PIVOT (
    SUM(TotalDue)
    FOR Name IN (
      ' + @PivotColumnHeaders + '
    )
  ) AS PivotTable
'

EXECUTE(@PivotTableSQL)

For reference, if I were to use the code I will be using Microsoft SQL Server 2005

Thanks
 
If you were to quote the values out, it would need to be done when setting the @PivotColumnHeaders variable.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)
MCM (SQL 2008)
MVP

My Blog
 
I would do this minor adjustment:
Code:
DECLARE @PivotColumnHeaders NVARCHAR(MAX)
SELECT @PivotColumnHeaders = stuff((select ', ' + quotename([Name])
from Sales.SalesTerritory order by [Name] FOR XML PATH('')),1,2,'')


PluralSight Learning Library
 
Thanks all for the response.

After doing the research to find this code and using it in my code, I was not able to use it. Unfortunately, the admin decreased the 2005 compatability from 90 to 80 because some programmers do not know how to code in the current standard. Yes, so rather than tell them to learn the code (ie. left outer join instead of *=), the admin changed the compatibility. To say I am dumbfounded and disappointed is an understatement.

In addition, my time was wasted and I now have to figure out an alternate solution.

Thanks again. Any suggestions are greatly appreciated.
 
Please go to your admin and kick him in the junk and tell'm it's a present from @mrdenny for going with that solution.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)
MCM (SQL 2008)
MVP

My Blog
 
No, do the more brutal method. Go to ownership and tell them their admin just wasted money...the business paid for SS2005 and is only getting the value of SS2000. Even better, take dollar figures on the difference between the cost of SS2005 and SS2000, I would guess there is a difference - especially if the company already had SS2000 and paid to upgrade to SS2005.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875
 
I agree with both. BTW, I found recently that we were also using compatibility 80 (and I found it by accident when I was trying PIVOT helping someone in the forum). Luckily in our case it was easy to discuss and get an approval to change the compatibility mode. I also found that I wasted about a day of tests, as my new tests in 90 mode behave differently.

PluralSight Learning Library
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top