Parameterize Your Queries | Professional SQL Server Analysis Services 2005 with MDX (Programmer to Programmer)

Parameterized queries in MDX, as the name suggests, help in passing parameters to a query where the values for the parameters are substituted before query execution. Why are parameterized queries important? You might have heard about attacks on web sites where users hack the sites by entering their own SQL, and as a result see data they should not see or change the data in the relational databases. This is because of those applications that are used to get input from users and concatenate the input string to form SQL queries. Often such applications run the queries under administrative privileges. Knowing this, hackers can enter inputs that are SQL constructs and that are executed along with the full SQL query. This is called SQL injection because hackers inject their own SQL queries within the overall query.

Similar threats exist for MDX as well. One of the main reasons why such attacks are possible is because user input is not validated.

Analysis Services overcomes the MDX injection by allowing parameters to be passed along with queries. Analysis Services validates these parameters, replaces the parameters in the query with the values, and then executes the query. The parameters to a query are represented within the query prefixed with the @ symbol. The following is a parameterized query. In this query the Number of children of a customer is the parameter.

     SELECT NON EMPTY { [Measures].[Internet Sales Amount] } ON COLUMNS, NON EMPTY {     ([Customer].[Customer Geography].[Country].ALLMEMBERS ) } DIMENSION PROPERTIES     MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT (     STRTOSET (@CustomerTotalChildren, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( {     [Date].[Fiscal].[Fiscal Year].&[2004], [Date].[Fiscal].[Fiscal Year].&[2005] } ) ON     COLUMNS FROM ( SELECT ( { [Product].[Product Categories].[Subcategory].&[26],     [Product].[Product Categories].[Subcategory].&[27] } ) ON COLUMNS FROM [Adventure     Works]))) WHERE ( [Product].[Product Categories].CurrentMember,     [Date].[Fiscal].CurrentMember, IIF ( STRTOSET (@CustomerTotalChildren,     CONSTRAINED).Count = 1, STRTOSET (@CustomerTotalChildren, CONSTRAINED),     [Customer].[Total Children].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR,     FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

Your client application would send the preceding query along with the list of parameters and values. When you execute the query you will get the results similar to executing regular queries. The following is an XMLA script that shows how the parameters are sent to Analysis Services. You have a name and value pair specified for each parameter in the query under the Parameters section of the XMLA script for query execution.

     <Envelope xmlns=";http://schemas.xmlsoap.org/soap/envelope/">         <Body>             <Execute xmlns="urn:schemas-microsoft-com:xml-analysis">                 <Command>                     <Statement>     select [Measures].members on 0,                    Filter (Customer.[Customer Geography].Country.members,                             Customer.[Customer Geography].CurrentMember.Name = @CountryName) on 1     from [Adventure Works]     </Statement>         </Command>         <Properties />         <Parameters>             <Parameter>                 <Name>CountryName</Name>                 <Value>'United Kingdom'</Value>             </Parameter>           </Parameters>         </Execute>       </Body>     </Envelope>

You will see an example of parameterized queries using the client object model ADOMD.Net in Chapter 9.