<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content="text/html; charset=us-ascii" http-equiv=Content-Type>
<META name=GENERATOR content="MSHTML 8.00.6001.18783"></HEAD>
<BODY>
<DIV><SPAN class=688494523-21072009><FONT color=#000080 size=2
face="Lucida Sans">Brian,</FONT></SPAN></DIV>
<DIV><SPAN class=688494523-21072009><FONT color=#000080 size=2
face="Lucida Sans"></FONT></SPAN> </DIV>
<DIV><SPAN class=688494523-21072009><FONT color=#000080 size=2
face="Lucida Sans">I am not aware of any MDX (or SQL) injection vulnerabilities
in Mondrian, either now or in the past.</FONT></SPAN></DIV>
<DIV><SPAN class=688494523-21072009><FONT color=#000080 size=2
face="Lucida Sans"></FONT></SPAN> </DIV>
<DIV><SPAN class=688494523-21072009><FONT color=#000080 size=2
face="Lucida Sans">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.</FONT></SPAN></DIV>
<DIV><SPAN class=688494523-21072009><FONT color=#000080 size=2
face="Lucida Sans"></FONT></SPAN> </DIV>
<DIV><SPAN class=688494523-21072009><FONT color=#000080 size=2
face="Lucida Sans">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.</FONT></SPAN></DIV>
<DIV><SPAN class=688494523-21072009><FONT color=#000080 size=2
face="Lucida Sans"></FONT></SPAN> </DIV>
<DIV><SPAN class=688494523-21072009><FONT color=#000080 size=2
face="Lucida Sans">If that is not the case, please log a bug and we will take it
very seriously.</FONT></SPAN></DIV>
<DIV><SPAN class=688494523-21072009><FONT color=#000080 size=2
face="Lucida Sans"></FONT></SPAN> </DIV>
<DIV><SPAN class=688494523-21072009><FONT color=#000080 size=2
face="Lucida Sans">Julian</FONT></SPAN></DIV><BR>
<BLOCKQUOTE
style="BORDER-LEFT: #000080 2px solid; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; MARGIN-RIGHT: 0px">
<DIV dir=ltr lang=en-us class=OutlookMessageHeader align=left>
<HR tabIndex=-1>
<FONT size=2 face=Tahoma><B>From:</B> Brian Vandenberg
[mailto:phantall@gmail.com] <BR><B>Sent:</B> Tuesday, July 21, 2009 4:34
PM<BR><B>To:</B> jhyde@pentaho.com; Mondrian developer mailing
list<BR><B>Subject:</B> Re: [Mondrian] RE: Parameterization of MDX
queries<BR></FONT><BR></DIV>
<DIV></DIV>Julian,<BR><BR> 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.<BR><BR> 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.<BR><BR> 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.<BR><BR>-Brian<BR><BR>
<DIV class=gmail_quote>On Tue, Jul 21, 2009 at 10:44 PM, Julian Hyde <SPAN
dir=ltr><<A
href="mailto:jhyde@pentaho.com">jhyde@pentaho.com</A>></SPAN> wrote:<BR>
<BLOCKQUOTE
style="BORDER-LEFT: rgb(204,204,204) 1px solid; MARGIN: 0pt 0pt 0pt 0.8ex; PADDING-LEFT: 1ex"
class=gmail_quote><BR><BR>> Thomas Morgner wrote:<BR>><BR>> Kurtis
and I currently run though the new reporting<BR>> datasources and wonder
how MDX query parametrization<BR>> works.<BR>><BR>> From
digging into the Mondrian and Olap4J code, I<BR>> know that Mondrian
supports prepared-statement-style<BR>> parameters. (I really hate the
string-concatenation<BR>> way the platform uses in all examples. This
smells<BR>> too strongly like SQL-Injection to me.)<BR><BR>I like
prepared-statement-style parameters too. MDX-injection is less a<BR>concern
than SQL injection (because MDX has good access control) but still
a<BR>worry; and string-concatenation parameters are less useful because they
have<BR>less metadata. The MDX standard didn't have them until recently, so
I added<BR>parameters as the solution.<BR><BR>Microsoft's MDX now has
LIMITED support for parameters. You can parameterize<BR>only scalar values,
whereas Mondrian can also have member-valued parameters.<BR>See <A
href="http://msdn.microsoft.com/en-us/library/ms145518.aspx"
target=_blank>http://msdn.microsoft.com/en-us/library/ms145518.aspx</A>.<BR><BR>>
I also found the "Parameter" function and have the<BR>> feeling that this
is how parameters are specified.<BR><BR>Correct. I propose that we use
Mondrian's parameters (using the 'Parameter'<BR>and 'ParamRef' functions)
rather than Microsoft-style parameters (using '@')<BR>for now. Usually I bow
to Microsoft, but Mondrian's parameters are superior.<BR><BR>I know it will
create interop problems if someone wants to run the same MDX<BR>via olap4j
and talk to both Mondrian and SSAS. We can fix that later.<BR><BR>The olap4j
API supports parameters as you would expect. Once you've prepared<BR>a
statement, you can call PreparedOlapStatement.getParameterMetaData.
This<BR>returns a OlapParameterMetaData, which is a subclass of the
JDBC<BR>ParameterMetaData object.<BR><BR><A
href="http://www.olap4j.org/api/org/olap4j/OlapParameterMetaData.html"
target=_blank>http://www.olap4j.org/api/org/olap4j/OlapParameterMetaData.html</A><BR><BR>><BR>>
Now here's the real question:<BR>><BR>> (1) Is this the right spot, or
is there some other<BR>> magic. The function says: Internal use only, so
I'm<BR>> cautious.<BR><BR>Yes, it's the right spot.<BR><BR>> (2) Where
can we use it? Are there restrictions like<BR>> on
JDBC-Prepared-statements where placeholders can<BR>> appear?<BR><BR>I
don't think there are restrictions. You can use a parameter anywhere
that<BR>the value (scalar or member) would be valid.<BR><BR>> (3) Is
there syntax documentation out there? Microsoft's<BR>> MDX documentation
is not very helpful on that one.<BR><BR>As I already said, we're not going
with Microsoft's standard on this one.<BR><BR>There's a very brief mention
of parameters in<BR><A
href="http://mondrian.pentaho.org/documentation/mdx.php"
target=_blank>http://mondrian.pentaho.org/documentation/mdx.php</A>. Very
terse, and it still<BR>manages to get it wrong; the function is called
Parameter, not Param. The<BR>functions are actually Parameter and
ParamRef.<BR><BR>A call to Parameter defines a parameter's name, data type
and default value.<BR>A parameter always has a default value (it makes life
simpler for the UI).<BR>If the parameter is a member, then the data type is
the name of a hierarchy<BR>that the parameter must belong to. This metadata
potentially allows a user<BR>interface to generate a list of values for the
parameter. (CDF folks, take<BR>note!)<BR><BR>By the way, the purpose of
ParamRef is if you want to define a parameter<BR>once and use it several
times in the same query. You could use Parameter<BR>several times but you'd
have to make sure the definitions (data type,<BR>default value) are
identical otherwise mondrian would complain.<BR><BR>> (4) Do you have
examples of this functionality or<BR>> can you point me to testcase code
that I can use as<BR>> example for how to parametrize
queries?<BR><BR>Your best documentation is in the testcase:<BR><A
href="http://perforce.eigenbase.org:8080/open/mondrian/testsrc/main/mondrian/test/"
target=_blank>http://perforce.eigenbase.org:8080/open/mondrian/testsrc/main/mondrian/test/</A><BR>ParameterTest.java<BR><BR>>
Thanks for any help and insight you can offer.<BR><BR>You're
welcome.<BR><BR>Cc: mondrian
developers.<BR><BR>Julian<BR><BR><BR>_______________________________________________<BR>Mondrian
mailing list<BR><A
href="mailto:Mondrian@pentaho.org">Mondrian@pentaho.org</A><BR><A
href="http://lists.pentaho.org/mailman/listinfo/mondrian"
target=_blank>http://lists.pentaho.org/mailman/listinfo/mondrian</A><BR></BLOCKQUOTE></DIV><BR></BLOCKQUOTE></BODY></HTML>