[Mondrian] RE: Parameterization of MDX queries

Julian Hyde jhyde at pentaho.com
Tue Jul 21 19:52:07 EDT 2009


Brian,
 
I am not aware of any MDX (or SQL) injection vulnerabilities in Mondrian,
either now or in the past.
 
I don't include the facilities in the schema file, where a database designer
can include arbitrary SQL for a column expression or a table expression.
(And, as of last week, also via a SQL hint.) It is assumed that the schema
file is only writeable by trusted parties.
 
But there is no way that an adversary can cause their SQL fragment to be
executed on the database by entering MDX via the driver, via JPivot, or via
XMLA.
 
If that is not the case, please log a bug and we will take it very
seriously.
 
Julian


  _____  

From: Brian Vandenberg [mailto:phantall at gmail.com] 
Sent: Tuesday, July 21, 2009 4:34 PM
To: jhyde at pentaho.com; Mondrian developer mailing list
Subject: Re: [Mondrian] RE: Parameterization of MDX queries


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/d768e2fd/attachment.html 


More information about the Mondrian mailing list