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

---

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

FirstWeekOfYear as parameter of DatePart

Haw to make FirstWeekOfDay parameter work in SQL Server 2000.
In Access 2000 datepart function has 3 parameters.
Why it is't in SQL Server 2000.
Help pleaseStraight from Books Online:

SET DATEFIRST
Sets the first day of the week to a number from 1 through 7.

Syntax
SET DATEFIRST { number | @.number_var }

Arguments
number | @.number_var

Is an integer indicating the first day of the week, and can be one of these values.

Value First day of the week is
1 Monday
2 Tuesday
3 Wednesday
4 Thursday
5 Friday
6 Saturday
7 (default, U.S. English) Sunday

Remarks
Use the @.@.DATEFIRST function to check the current setting of SET DATEFIRST.

The setting of SET DATEFIRST is set at execute or run time and not at parse time.

Permissions
SET DATEFIRST permissions default to all users.

Examples
This example displays the day of the week for a date value and shows the effects of changing the DATEFIRST setting.

-- SET DATEFIRST to U.S. English default value of 7.
SET DATEFIRST 7
GO
SELECT CAST('1/1/99' AS datetime), DATEPART(dw, '1/1/99')
-- January 1, 1999 is a Friday. Because the U.S. English default
-- specifies Sunday as the first day of the week, DATEPART of 1/1/99
-- (Friday) yields a value of 6, because Friday is the sixth day of the
-- week when starting with Sunday as day 1.
SET DATEFIRST 3
-- Because Wednesday is now considered the first day of the week,
-- DATEPART should now show that 1/1/99 (a Friday) is the third day of the -- week. The following DATEPART function should return a value of 3.
SELECT CAST('1/1/99' AS datetime), DATEPART(dw, '1/1/99')
GO

First/Last Day of Month (current and previous three months)

Hi,
I want to set a parameter to use the first and last days of the month for
two parameters' defaults. I then want to set the first day of the previous
two months as the "available values" for on of the parameters and the last
day of the previous two months for the other parameter's "available values".
Can anyone tell me why this isn't working (I want to get last day of month):
=DATEADD("dd", - DAY(DATEADD("m", 1, Now)), DATEADD("m", 1, Now))
I have tried using the Today funcion instead of Now with no joy. I started
doing this with DATEADD and DATEDIFF, as is commonly found as the solution
for this on tons of tech articles. However, I have learnt that although
T-SQL can work out what you want to do with DATEADD (which expects DateTime)
and DATEDIFF(which returns an Integer), reporting services cannot. So I
found the above expression that doesn't use DATEDIFF, but I still can't get
it to work. The expression builder doesn't seem to find a problem with the
syntax (no green or red wavy lines), but when I run the report I get the
following:
An error occurred duing local report processing. Error during processing of
'SDate' report parameter.
Please tell me someone has an answer for me. This should be so simple. I
bet I am going to kick myself when (if!!) the answer comes through.
TIA,
JarrydHi,
Well I just did this in T-SQL as part of the procedure that feeds the
report. Created a virtual table and added a second dataset to the report.
That seems to work. One thing I find odd - the "calendar" button can't be
used to set the date properly. We are in the UK and so the calendar
generates a UK style date, but Reportiong Services won't have it; you have
to manually enter it in USA format!! How do you solve it? Please don't
tell me you have to programtically grab the variable and cast it in USA
style. That's just silly. But if so, then where do you do it? I am
assuming you have to go to Dataset>Parameters and jimmy the value field's
value (def: Parameters!My_Param.Value) to grab the value and reorder the
days and months but I can't get it to work. The only other place I know of
is the Report>Report Parameters form, but that doesn't look too promising.
TIA,
Jarryd
"Jarryd" <noemail@.nodomain.com> wrote in message
news:u4I1mEMqHHA.2372@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I want to set a parameter to use the first and last days of the month for
> two parameters' defaults. I then want to set the first day of the
> previous two months as the "available values" for on of the parameters and
> the last day of the previous two months for the other parameter's
> "available values".
> Can anyone tell me why this isn't working (I want to get last day of
> month):
> =DATEADD("dd", - DAY(DATEADD("m", 1, Now)), DATEADD("m", 1, Now))
> I have tried using the Today funcion instead of Now with no joy. I
> started doing this with DATEADD and DATEDIFF, as is commonly found as the
> solution for this on tons of tech articles. However, I have learnt that
> although T-SQL can work out what you want to do with DATEADD (which
> expects DateTime) and DATEDIFF(which returns an Integer), reporting
> services cannot. So I found the above expression that doesn't use
> DATEDIFF, but I still can't get it to work. The expression builder
> doesn't seem to find a problem with the syntax (no green or red wavy
> lines), but when I run the report I get the following:
> An error occurred duing local report processing. Error during processing
> of 'SDate' report parameter.
> Please tell me someone has an answer for me. This should be so simple. I
> bet I am going to kick myself when (if!!) the answer comes through.
> TIA,
> Jarryd
>