If you’ve been using Reporting Services 2005 to generate MDX you’ll have probably noticed that the StrTo<Something> family of functions (StrToSet, StrToMember, StrToTuple, StrToValue) have gained an extra, optional parameter – the CONSTRAINED flag. It’s not mentioned in Books Online and since someone has been asking about it on the newsgroup I thought it would be worth a quick post explaining what it does.
Basically it’s a security feature for middle-tier scenarios to stop MDX injection attacks. Running parameterised MDX queries in RS is probably going to be the most common example of when you’d want to use it, but I guess anywhere where you are generating dynamic MDX in code you’re running the risk of an evil user trying to pass you a stored procedure call (or something worse) instead of the member name or key you were expecting. By putting the CONSTRAINED flag in your query you automatically restrict what the StrTo<Something> functions can accept as their first parameter:
- StrToMember can only take a string containing a member name
- StrToSet can only take an explicitly defined set, ie a list of member names or tuples in curly brackets
- StrToTuple can only take an explicitly defined tuple, ie a list of member names in brackets
- StrToValue can only take a constant value
What’s not allowed with CONSTRAINED is an MDX expression which evaluates to either a Member, Set, Tuple or Value. Here’s an Adventure Works query which illustrates this:
WITH
MEMBER
MEASURES.STRTOSET_TEST1 AS COUNT(STRTOSET("{[Measures].[Internet Order Quantity]}"))MEMBER
MEASURES.STRTOSET_TEST2 AS COUNT(STRTOSET("MEASURES.MEMBERS"))MEMBER
MEASURES.STRTOSET_TEST3 AS COUNT(STRTOSET("{[Measures].[Internet Order Quantity]}", CONSTRAINED))MEMBER
MEASURES.STRTOSET_TEST4 AS COUNT(STRTOSET("MEASURES.MEMBERS", CONSTRAINED))SELECT
{MEASURES.STRTOSET_TEST1, MEASURES.STRTOSET_TEST2, MEASURES.STRTOSET_TEST3, MEASURES.STRTOSET_TEST4} ON 0FROM
[ADVENTURE WORKS]
The final calculated member here produces an error because "MEASURES.MEMBERS" is an expression which evaluates to a set and is not an explicitly defined set like the ones in the first or third calculated members.
UPDATE: Jon makes a good point in the comments…
Hi Chris,I\’m worried to hear about "MDX injection attacks". I heard a story about someone who was attacked in a nightclub by a stranger wielding a syringe apparently containing HIV, but are we now to believe there\’s a risk is similarly being attacked and injected with MDX? What are the implications of such an attack? If it would improve my OLAP-guru status then very good, but I\’m concerned there might be additional side effects to the intermingled of my blood with a multi-dimensional query language.Any further information you can give on this alarming development would be much appreciated.Colin
Chris/Microsoft devs,What\’s to stop you getting round this using the same sort of techniques you\’d use in a normal SQL injection attack? Specifically, by including quotes and comments within the supposedly constrained set expression (in the case of StrToSet) so as to comment-out the following CONSTRAINED keyword and hence execute arbitrary MDX within your StrToSet\’s expression?For example, take the following query:SELECT StrToSet("MyString", CONSTRAINED) on 0FROM [MyCube](where the value MyString is supplied by RS/whatever client app we have that allows direct input)If we use, say, [Month].[Month].Members as the value for MyString then as expected the query is not allowed to execute, because of the restrictions impose by CONSTRAINED.However, now try the following value for MyString instead:[Month].[Month].Members") on 0 –Substituting this into our query the actual MDX that gets run is:SELECT StrToSet("[Month].[Month].Members") on 0 –", CONSTRAINED) on 0FROM [MyCube]The comment (–) ensures that the CONSTRAINED keyword is removed from the query, and the query runs and gives results, using the "illegal" expression [Month].[Month].Members). Hence we have a simple mechanism for returning to a state in which we can run whatever MDX expression we like, even when CONSTRAINED is imposed in the middle-tier.The only way I can see CONSTRAINED working in this way is if RS/client app prevents you from including quotes (and/or comments) in your MyString expression – I don\’t know if this is the case or not since I haven\’t tried it in RS – however as an MDX language feature in its own right I can\’t really see how this is going to stop injection attacks?This method for carrying out injection attacks is hardly a secret so it\’s hard to believe this is an oversight; is there more to CONSTRAINED than mees the eye?Thanks,Jon
Hmm, good question… Mosha?
To prevent MDX injection attack, one should use standard technique of using parameters in conjunction with CONSTRAINED flag – i.e. SELECT StrToMember(@m1, CONSTRAINED) – this is well known technique for prevention of in-line comment injection. Also, MDX injection attacks are harder then SQL injection attacks, since it is very difficult to come up with MDX which will cause damage, especially when you realize, that ";" separation of statements won\’t work for queries – it only works in MDX scripts. Nevertheless, it is possible that attacker will know about some special sproc which he will try to invoke – so combination of parametric query and CONSTRAINED flag should do the job.BTW, the original design for CONSTRAINED flag had more in mind performance for StrToXXX family of functions, since there are certain optimizations that can be done when the input string is of constrained form. Not all these optimizations were implemented in Yukon, but it is still a good idea to use the flag when possible. Of course, if use of StrToXXX can be avoided – the performance will be better without it. Sometime significantly better.Mosha
So, in terms of security, does CONSTRAINED actually add anything above and beyond the parameter-driven (rather than ad hoc) input method? On its own, CONSTRAINED is insufficient to prevent MDX injection attacks (although, as you say, it\’s difficult to see how someone could do much harm with an MDX injection unless stored procs are involved), and surely if you can enforce the use of parameters, and keep their values under your control, then you have no fear of an attack anyway (with or without CONSTRAINED)?The optimization opportunity you mention is interesting, and definitely worth knowing … Chris, fancy investigating this and posting some results? 🙂
CONSTRAINED can have an unwelcome side effect in Reporting Services 2005 parameter queries because it precludes the use of UNKNOWNMEMEBER with StrToSet. The MDX designer generated query will produce a restriction violation error if the parameter set includes Unknown and user selects it from the dropdown because it uses UNKNOWNMEMEBER and not an explicit reference.
I\’ve just discovered that the "slice" for a partition is run through StrToSet with the CONSTRAINED parameter. The unfortunate downside of this is that you can\’t set the slice of a partition to something like {null:[Date].[Calendar Year].&[2003]}. It has to be hardcoded to an explicit list of the members: {[Date].[Calendar Year].&[2001],[Date].[Calendar Year].&[2002],[Date].[Calendar Year].&[2003]}
For more info:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=74837&SiteID=1
As we\’ve just discovered, it\’s pretty important to set the slice for a partition in SSAS2005 if you want to make sure SSAS only scans the minimum number of partitions in all situations:
http://markhill.org/blog/?p=11
Hi Chris,
I am trying to pass multiple parameter values in DAX query dataset to generate a report, can you help me with that please?
Thanks,
Nikhil
Interesting question – I don’t know off the top of my head, but let me think about it and get back to you… If I can work out a way of doing it, it would make a good blog post!