Flaw in SQL Server Report Services Security

While I wouldn't take this approach in a hostile environment, Reporting Serv
ices 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!Departme
nt.Value = 0,""," WHERE (DepartmentID = " & Parameters!Department.Value &
")") & " ORDER BY LastName"
Then when you deploy the report you will see a prompt that says Department a
nd 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, wi
th all of the talk about the security that went into this product, I would h
ave thought that something would have been done to prevent such a common sec
urity 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...

quote:

---

> While I wouldn't take this approach in a hostile environment, Reporting

---

Services exposes a security flaw in the way parameters are parsed.

quote:

---

> 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"

quote:

---

> 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"

quote:

---

> Then when you deploy the report you will see a prompt that says Department

---

and a text box.

quote:

---

> If you enter a Department ID in the box everything works great. But,

---

instead if you enter something like:

quote:

---

> '; 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.

quote:

---

> Lee

---