[Mondrian] RE: Parameterization of MDX queries

Brian Vandenberg phantall at gmail.com
Tue Jul 21 19:33:36 EDT 2009


Julian,

  Have things changed w/respect to MDX injection in Mondrian?  I've seen
instances where I was able to [unintentionally] put stuff into MDX queries
that altered the underlying sql before.

  Probably the most serious variation on this theme had to do with data
already in the database.  If a field contains text that could trigger an sql
injection attack and is used to populate a query generated in JPivot,
execution of the now modified query could result in arbitrary sql execution.

  I didn't pursue researching this in-depth at the time because I was busy,
and seeing what pitfalls might lie in that direction wasn't something I
could afford to spend time on, though I dimly recall writing a bug on it.

-Brian

On Tue, Jul 21, 2009 at 10:44 PM, Julian Hyde <jhyde at pentaho.com> wrote:

>
>
> > Thomas Morgner wrote:
> >
> > Kurtis and I currently run though the new reporting
> > datasources and wonder how MDX query parametrization
> > works.
> >
> >  From digging into the Mondrian and Olap4J code, I
> > know that Mondrian supports prepared-statement-style
> > parameters. (I really hate the string-concatenation
> > way the platform uses in all examples. This smells
> > too strongly like SQL-Injection to me.)
>
> I like prepared-statement-style parameters too. MDX-injection is less a
> concern than SQL injection (because MDX has good access control) but still
> a
> worry; and string-concatenation parameters are less useful because they
> have
> less metadata. The MDX standard didn't have them until recently, so I added
> parameters as the solution.
>
> Microsoft's MDX now has LIMITED support for parameters. You can
> parameterize
> only scalar values, whereas Mondrian can also have member-valued
> parameters.
> See http://msdn.microsoft.com/en-us/library/ms145518.aspx.
>
> > I also found the "Parameter" function and have the
> > feeling that this is how parameters are specified.
>
> Correct. I propose that we use Mondrian's parameters (using the 'Parameter'
> and 'ParamRef' functions) rather than Microsoft-style parameters (using
> '@')
> for now. Usually I bow to Microsoft, but Mondrian's parameters are
> superior.
>
> I know it will create interop problems if someone wants to run the same MDX
> via olap4j and talk to both Mondrian and SSAS. We can fix that later.
>
> The olap4j API supports parameters as you would expect. Once you've
> prepared
> a statement, you can call PreparedOlapStatement.getParameterMetaData. This
> returns a OlapParameterMetaData, which is a subclass of the JDBC
> ParameterMetaData object.
>
> http://www.olap4j.org/api/org/olap4j/OlapParameterMetaData.html
>
> >
> > Now here's the real question:
> >
> > (1) Is this the right spot, or is there some other
> > magic. The function says: Internal use only, so I'm
> > cautious.
>
> Yes, it's the right spot.
>
> > (2) Where can we use it? Are there restrictions like
> > on JDBC-Prepared-statements where placeholders can
> > appear?
>
> I don't think there are restrictions. You can use a parameter anywhere that
> the value (scalar or member) would be valid.
>
> > (3) Is there syntax documentation out there? Microsoft's
> > MDX documentation is not very helpful on that one.
>
> As I already said, we're not going with Microsoft's standard on this one.
>
> There's a very brief mention of parameters in
> http://mondrian.pentaho.org/documentation/mdx.php. Very terse, and it
> still
> manages to get it wrong; the function is called Parameter, not Param. The
> functions are actually Parameter and ParamRef.
>
> A call to Parameter defines a parameter's name, data type and default
> value.
> A parameter always has a default value (it makes life simpler for the UI).
> If the parameter is a member, then the data type is the name of a hierarchy
> that the parameter must belong to. This metadata potentially allows a user
> interface to generate a list of values for the parameter. (CDF folks, take
> note!)
>
> By the way, the purpose of ParamRef is if you want to define a parameter
> once and use it several times in the same query. You could use Parameter
> several times but you'd have to make sure the definitions (data type,
> default value) are identical otherwise mondrian would complain.
>
> > (4) Do you have examples of this functionality or
> > can you point me to testcase code that I can use as
> > example for how to parametrize queries?
>
> Your best documentation is in the testcase:
>
> http://perforce.eigenbase.org:8080/open/mondrian/testsrc/main/mondrian/test/
> ParameterTest.java
>
> > Thanks for any help and insight you can offer.
>
> You're welcome.
>
> Cc: mondrian developers.
>
> Julian
>
>
> _______________________________________________
> Mondrian mailing list
> Mondrian at pentaho.org
> http://lists.pentaho.org/mailman/listinfo/mondrian
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20090721/1542cedd/attachment.html 


More information about the Mondrian mailing list