Flaw in SQL Server Report Services Security

While I wouldn't take this approach in a hostile environment, Reporting Services exposes a security flaw in the way parameters are parsed.
To see this, follow the walk through for adding a parameter to a report, but instead of setting "Available Values" to "From Query", set it to "Non-Query
The help file tells you to set up your SQL string as so
="SELECT FirstName, LastName, Title FROM Employee" & IIf(Parameters!Department.Value = 0,""," WHERE (DepartmentID = " & Parameters!Department.Value & ")") & " ORDER BY LastName
Then when you deploy the report you will see a prompt that says Department and a text box.
If you enter a Department ID in the box everything works great. But, instead if you enter something like
'; Drop table Employee -
And click run report, say goodbye to your employee table
While I would never use concatenated queries in a production environment, with all of the talk about the security that went into this product, I would have thought that something would have been done to prevent such a common security flaw
LeeI haven't gotten around to testing RS yet, but are you connecting the user
as an admin? You must be otherwise they wouldn't be able to execute the
drop statement. This is just sql injection. All user access should be
restricted to stored procedures.
Eric
"Lee" <anonymous@.discussions.microsoft.com> wrote in message
news:5A570020-575D-4B5F-A775-FC9F45416D50@.microsoft.com...
> While I wouldn't take this approach in a hostile environment, Reporting
Services exposes a security flaw in the way parameters are parsed.
> To see this, follow the walk through for adding a parameter to a report,
but instead of setting "Available Values" to "From Query", set it to
"Non-Query"
> The help file tells you to set up your SQL string as so:
> ="SELECT FirstName, LastName, Title FROM Employee" &
IIf(Parameters!Department.Value = 0,""," WHERE (DepartmentID = " &
Parameters!Department.Value & ")") & " ORDER BY LastName"
> Then when you deploy the report you will see a prompt that says Department
and a text box.
> If you enter a Department ID in the box everything works great. But,
instead if you enter something like:
> '; Drop table Employee --
> And click run report, say goodbye to your employee table.
> While I would never use concatenated queries in a production environment,
with all of the talk about the security that went into this product, I would
have thought that something would have been done to prevent such a common
security flaw.
> Leesql