At callout D, the procedure constructs
the PIVOT query and invokes it using
dynamic SQL. Note two important points
here. First, the derived table PivotInput
contains only the columns that need to be
involved in the pivot operation: grouping
columns (@on_rows), the rotation column
(@on_cols), and the aggregation column
(@agg_col). This technique lets you prevent
implicit grouping by columns that you don’t
want to take part in the grouping activity.
Second, the list of rotation elements stored
in @cols that the procedure constructed
dynamically is part of the concatenation (in
the parentheses following the IN clause),
letting you rotate a dynamic number of elements that are unknown ahead of time. In
the query string you’re constructing, the IN
list appears to be a list of known elements.
Mitigating SQL Injection Risks
As I’ve noted, sp_pivot’s implementation
doesn’t guard against SQL injection attacks.
Coverage of SQL injection is outside the
scope of this article, but you can learn about
SQL injection and how to prevent it by
reading “SQL Injection: The Hacker’s Gold
Mine” (May 2006, InstantDoc ID 49639) and
“Preventing SQL Injection Attack” (August
2004, InstantDoc ID 43012). The following
example shows how easy it is to inject code
by using the sp_pivot stored procedure:
EXEC Northwind.dbo.sp_pivot
@query = N’dbo.Orders’,
@on_rows = N’1 AS c) AS D;
PRINT ‘’SQL Injection!
This could have been much
worse than a PRINT
command!’’;
SELECT * FROM (select
EmployeeID’,
@on_cols = N’ShipVia’,
@agg_func = N’COUNT’,
@agg_col = N’*’;
This invocation of sp_pivot injects a PRINT
statement, but you could inject much more
destructive code, such as DROP commands
and xp_cmdshell.
You can mitigate the risks of SQL injection, but bear in mind that hackers continue
to find creative ways to inject code into your
systems. If you use dynamic SQL, your operating premise should be that hackers will be
able to inject their own code through it. The
only way to prevent injection is to not use
dynamic SQL. With that in mind, here are a
few ways you can mitigate the risks of SQL
injection in sp_pivot:
Limit the size of your input parameters. Sp_
pivot defines all parameters as NVARCHAR
(MAX), giving hackers a lot of leeway
for injecting code. You can define all pivoting elements (grouping column, rotation
column, aggregation column) as sysname,
for example, to limit the input string size
and the opportunities for injecting code.
Also, query the metadata to verify that specified columns exist in the input table.
Do not accept a query as input. Instead,
accept only a table or view name as input,
and check to be sure that the object exists.
If you need to perform manipulation to
prepare pivoting elements that have expressions, you can always create a view by using
such manipulation and then provide the
view as input.
Limit the aggregate function name size. For
example, define the size as NVARCHAR(12),
and check that the function appears in a list of
your supported functions.
Use the QUOTENAME function to
quote object and column names with square
brackets. For example, QUOTENAME
(N'col1') becomes [col1], while QUOTENAME
(N'col1] DROP TABLE T1 --') becomes [col1]]
DROP TABLE T1 --]. The closing square
bracket, which is often used as an attempt to
inject code, is doubled by the QUOTENAME
function, neutralizing injection attempts.
Check the contents of the @cols variable,
which contains the concatenated list of rotation
elements. Remember that SQL injection
can be achieved by injecting code into your data. Look for known
strings used in SQL
injection, such as --,
sp_, xp_, DROP, and
so on.
Create the procedure
in a specific database
and by using the usp_ prefix. To conform to
Microsoft’s recommendations and to prevent use of the stored
procedure globally in
the instance, create sp_
pivot only in the database where you want
to allow usage and by
using the usp_ prefix
instead of sp_.
To implement
these security measures, run the code in Web Listing 1 (which
you can download at
InstantDoc ID 94268)
to create the usp_pivot
stored procedure in the
Northwind database.
The code in Listing
2 invokes the procedure to return the
sum of freight for each
employee and shipper; Table 1 shows the
results.
As always, tightening
security comes at the
cost of decreased flexibility. And although
these measures help
you mitigate the
risk of SQL injection attack, when you
use dynamic SQL, it’s
hard—if not impossible—to guarantee
that you can block all SQL injection
attempts. Use dynamic SQL with caution.
Still, with these safety measures in place,
usp_pivot provides a powerful tool for pivoting data and giving users information as
they need to see it.
End of Article
Prev. page
1
2
[3]
next page -->
