IIF Parameter Embedded inside SSRS SQL Query

Question

I hope you could help me understand the following phrase. They are part of an SQL query in my SSRS report. From Report Builder -> Report -> a Dataset, I selected Dataset Properties -> Query

The section below is part of the WHERE clause: it refers to the SSRS parameters and I could not understand what they meant. They do not look like the simple IIF statement I used to work with, as they contains many signs like "','") & "') " & .
I appreciate very much if you can help explain the meaning by breaking them down.

AND PTE_code = '" & Parameters!p_TE.Value & "'") & " 
AND PTY_code IN ('" & Join(Parameters!p_TY.Value,"','") & "') " & Iif(IsNothing(Parameters!pFDate.Value)," "," 
AND (H_expiry_date >= '" & UCase(Format(Parameters!pFDate.Value,"dd-MMM-yyyy")) & "' OR H_expiry_date IS NULL)" ) & Iif(IsNothing(Parameters!pTDate.Value)," "," 
AND (H_expiry_date <= '" & UCase(Format(Parameters!pTDate.Value,"dd-MMM-yyyy")) & "' OR H_expiry_date IS NULL)" ) & iif(Parameters!p_PC.Value="ALL"," "," AND PC = '" & Parameters!p_PC.Value & "' ")

Notes: PTE_code, PTY_code, H_expiry_date, PC are the field names from tables. p_TE, p_TY, p_FDate, p_TDate, p_PC are parameters from the report.

Jamie F Jamie F · Accepted Answer · 2017-09-01T22:01:25

Where ever there is a double quote in this, the SQL query string is getting composed in Reporting Services. The single quotes are sent to the DB server as part of the query string, delimiting the strings: so this will actually send a query something like:

AND PTE_code = 'p_TEValue' 
AND PTY_code IN ('p_TYValue1','p_TYValue2') 
AND (H_expiry_date >= 'pFDateValueFormatted' OR H_expiry_date IS NULL) 
AND (H_expiry_date <= 'pTDateValueFormatted' OR H_expiry_date IS NULL)
AND PC = 'p_PCValue'

The IIF statements put a single space instead of parts of the where above, when some of the parameters are empty. (The second parameter of the IIF is a space, which is returned when the first parameter of the IIF evaluates to true. The third (last) parameter of the IIF is returned if the first parameter evaluates to false.

This query should be rewritten to avoid the SQL injection vulnerability on the p_TEValue parameter, by the way. And I would probably rewrite to avoid the Date string formatting, if H_expiry_date is a DATETIME column.

IIF Parameter Embedded inside SSRS SQL Query

1 Answers