Professional Microsoft® SQL Server® Analysis Services 2008 with MDX

10.6. Parameterize Your Queries

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 relational databases. This is because applications that are used to get input from users use the raw 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 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 ...