prevent MDX Injection Attacks

Question

What is a good way to protect against MDX Injection attacks with C# ? I am currently using ADOMDClient is there a different client I should use ?

Answer 1

We can create parameterized MDX queries to prevent MDX injectection attacks by passing user-specified strings into the MDX StrToSet, StrToTuple, StrToMember, and StrToValue functions.

Here are a few examples of the MDX StrToSet function from this MSDN page.

The following example returns the set of members of the State-Province attribute hierarchy using the StrToSet function. The set specification provides a valid MDX set expression.

SELECT StrToSet ('[Geography].[State-Province].Members')  
ON 0  
FROM [Adventure Works]

The following example returns an error due to the CONSTRAINED flag. While the set specification provides a valid MDX set expression, the CONSTRAINED flag requires qualified or unqualified member names in the set specification.

SELECT StrToSet ('[Geography].[State-Province].Members', CONSTRAINED)  
ON 0  
FROM [Adventure Works]

The following code example demonstrates how you can create a parameterized query, and how to execute it using the AdomdConnection object.

Assume we have the following generic C# method that executes a parameterized MDX query and returns a CellSet.

public CellSet GetCellSet(string connectionString, string query, IDictionary<string, object> parms)
{
    using (var conn = new AdomdConnection(connectionString))
    {
        // Open the connection.
        conn.Open();

        // Create the command.
        using (var cmd = conn.CreateCommand())
        {
            // Set the command query.
            cmd.CommandText = query;

            // Add any query parameters.
            if (parms != null)
            {
                foreach (var kv in parms)
                {
                    var parameter = cmd.CreateParameter();
                    parameter.ParameterName = kv.Key;
                    parameter.Value = kv.Value;

                    cmd.Parameters.Add(parameter);
                }
            }

            // Execute the query and return the CellSet.
            return cmd.ExecuteCellSet();
        }
    }
}

Let's say we have another method that allows clients to pass in a string representation of an MDX set expression. The method will select the set from the cube and return a CellSet of the results.

public CellSet GetMdxSetOnColumns(string setExpression)
{
    var connectionString = "replace with your connection string";

    // The query parameter @TheSet will be replaced with setExpression.
    var query = "SELECT StrToSet(@TheSet) ON 0 FROM [Adventure Works]";

    // Add the passed in string as a query parameter.
    var parms = new Dictionary<string, object>();

    // You can omit the "@" in front of the parameter name here.
    parms.Add("TheSet", setExpression);

    return GetCellSet(connectionString, query, parms);
}

Client code can call this method like this.

var cellSet = GetMdxSetOnColumns("[Geography].[State-Province].Members");