SQL Server – Dynamic PIVOT Table – SQL Injection

by

We’ve done a lot of work similar to your example. We haven’t worried about SQL injenction, in part because we have complete and total control over the data being pivoted–there’s just no way malicious code could get through ETL into our data warehouse.

Some thoughts and advice:

  • Are you required to pivot with nvarcahr(500) columns? Ours are varchar(25) or numerics, and it would be pretty hard to sneak damaging code in through there.
  • How about data checking? Seems like if one of those strings contained a “]” character, it’s either a hack attempt or data that will blow up on you anyway.
  • How robust is your security? Is the system locked down such that Malorey can’t sneak his hacks into your database (either directly or through your application)?

Hah. It took writing all that to remember function QUOTENAME(). A quick test would seem to indicate that adding it to your code like so would work (You’ll get an error, not a dropped temp table):

SELECT
        @columns = 
        STUFF
        (
                (
                        SELECT DISTINCT
                                ', [' + quotename(ColumnB, ']') + ']'
                        FROM
                                #PivotTest
                        FOR XML PATH('')
                ), 1, 1, ''
        )

This should work for pivot (and unpivot) situations, since you almost always have to [bracket] your values.

More Related Contents:

  1. SQL Server Pivot Table with multiple column aggregates
  2. PIVOT in sql 2005
  3. SQL Server pivot vs. multiple join
  4. T-SQL: Opposite to string concatenation – how to split string into multiple records [duplicate]
  5. What is the best way to create and populate a numbers table?
  6. Sql Server string to date conversion
  7. SQL Server query to find all permissions/access for all users in a database
  8. SQL Server IN vs. EXISTS Performance
  9. Cannot truncate table because it is being referenced by a FOREIGN KEY constraint?
  10. What is the maximum characters for the NVARCHAR(MAX)? [duplicate]
  11. Atomic UPSERT in SQL Server 2005
  12. Do I really need to use “SET XACT_ABORT ON”?
  13. SQL Server sp_msforeachtable usage to select only those tables which meet some condition
  14. SQL Server ORDER BY date and nulls last
  15. Creating audit triggers in SQL Server
  16. Regular Expressions in SQL Server servers?
  17. How to determine total number of open/active connections in ms sql server 2005
  18. How to remove accents and all chars a..z in sql-server?
  19. Find non-ASCII characters in varchar columns using SQL Server
  20. How to query values from xml nodes?
  21. Transpose rows and columns with no aggregate
  22. How to query for Xml values and attributes from table in SQL Server?
  23. Violation of UNIQUE KEY constraint on INSERT WHERE COUNT(*) = 0 on SQL Server 2005
  24. SQL Server recursive query
  25. Milliseconds wrong when converting from XML to SQL Server datetime
  26. nvarchar(max) vs NText
  27. Cannot find either column “dbo” or the user-defined function or aggregate “dbo.Splitfn”, or the name is ambiguous
  28. Date range overlapping check constraint
  29. Difference between database and schema
  30. Forgot SQL Server Password

Leave a Comment