Sunday, March 08, 2009

BIRT Multi-Select Statements

Note: re-published to fix typos in the opening paragraph.  Oh the perils of late night posting.

In BIRT 2.3.1 a new extension point was added to support the creation of user defined functions.  Jason has all ready blogged about the ScriptFunctionExecutor here .  In this post, I would like to show how this extension point can be used to automate handling Multi-Select input parameters in SQL statements.

BIRT 2.3 added support for Multi-Select parameters.  The most typical application of this technology is to support using an IN statement within a SQL where clause.  Imagine having the following SQL:

select status   
from orders
where state in (?)

What you would like to be able to do is have a user select multiple parameters from a drop down list, and then have them work in the query.  At this time, BIRT does not support this, mostly because of the way JDBC property binding works.  JDBC would require a new parameter binding (?) to be added for each parameter value that was passed, e.g.

where state in (?, ?, ?)

The problem is that you don't know how many parameters are passed when you design the report.  The work around that most people use is to write an event handler that will insert SQL into the query in the BeforeOpen method of the DataSet.  
There are two problems with this approach is that you leave yourself wide open for a SQL Injection attack.  First, you have SQL in the DataSet editor and in the BeforeOpen method,  this makes the code more brittle and difficult to maintain.  The second is that you leave yourself wide open to a SQL Injection attack.  (If you don't know, please follow the link).  
What I wanted to do was figure out a way that I could simplify using multi-select statements that would have some safe guards against a SQL injection attack.  The ScriptFunctionExtension is a great way to do just that.  

First, we want to modify our SQL so that the parameters are defined within the SQL.  To use my function your SQL will look like this:
select status   
from orders
where status in ('MS:pStatus') and
state in ('MS:pState')

In this scenario you have two parameters pStatus and pState that are defined in the report parameters.  Then you modify the BeforeOpen method of your DataSet to have the following code:
this.queryText = BlackboardFunctions
            .MultiSelectSql(this.queryText, reportContext);

That's is all you have to do.  All of the SQL modification and checks for injection strings are done for you in the ScriptFunction.   Now the caveats: I realize that there are still SQL Injection issues here.  I welcome your suggestions on how to reduce the risk.  There is a way that this can be done using Java Bind variables, but it is significantly more difficult.

If you would like to download the full source to the plugin, I have it on my Subversion server here.  For the curious, the remainder of this article will talk about how it was done.

First, let's look at the ScriptFunctionFactory.  I absolutely hate having to keep code coordinated in more than one place.  The ScriptFunction requires that you define the Script in the plugin.xml and in the FunctionFactory.  In addition, you will have the actual ScriptFunctionExecutor classes.

What I did was came up with a simple rule, the name of the Function (in plugin.xml), is the name of the ScriptFunctionExecutor class.  This simplifies my FunctionFactory to just use the name passed in to find the appropriate class:
    public IScriptFunctionExecutor getFunctionExecutor(String functionName )
        throws BirtException {
        String fullClassName = plugin_id + "." + functionName;
        try {
            Class<? extends IScriptFunctionExecutor> functionClass = Class
                    .forName( fullClassName )
                    .asSubclass( IScriptFunctionExecutor.class );
            IScriptFunctionExecutor scriptFunction = functionClass
                    .newInstance();
            return scriptFunction;
        }
        catch ( Exception e ) {
            e.printStackTrace();
            throw new BirtException( plugin_id, "Unable to find class: "
                                                            + fullClassName,
                                                getResourceBundle(), e );
        }
    }


Next we need to have the ScriptFunction code that walks through the queryText replacing the messages with the appropriate parameter values
public Object execute( Object[] args, IScriptFunctionContext context ) throws BirtException {
    final String sqlText = args[ 0 ].toString();
    IReportContext reportContext = super.getReportContext( args[ 1 ] );

    // find the Multi-Select replacement string
    List<String> matchList = new ArrayList<String>();
    try {
        Pattern regex = Pattern.compile( "\\('MS:[A-Za-z0-9]+'\\)" );
        Matcher regexMatcher = regex.matcher( sqlText );
        while ( regexMatcher.find() ) {
            matchList.add( regexMatcher.group() );
        }
    }
    catch ( PatternSyntaxException ex ) {
        // Syntax error in the regular expression
    }

    String rtnStr = sqlText;
    // iterate through each multi-select parameter
    // in the sql and replace with appropriate parameter values
    for ( final String matchStr : matchList ) {
        String searchFor = matchStr.substring( 2, matchStr.length() - 2 );
        String paramName = searchFor.substring( 3 );
        Object obj = reportContext.getParameterValue( paramName );
        if ( obj == null )
            removeLine( matchStr, rtnStr );
        if ( obj instanceof Object[] ) {
            StringBuffer sb = new StringBuffer();
            Object[] pVals = (Object[]) obj;
            for ( int i = 0; i < pVals.length; i++ ) {
                super.testSqlInjection( pVals[ i ].toString() );
                sb.append( pVals[ i ].toString() );
                if ( i < pVals.length - 1 ) {
                    sb.append( "', '" );
                }
            }
            rtnStr = rtnStr.replaceAll( searchFor, sb.toString() );
        }

    }
    return rtnStr;
}

There are two sub-functions that are lurking in that text.  First, a common task for ScriptFunctions will be getting the ReportContext from the EventHandler, while not difficult, it makes sense to have appropriate error handling and testing, so I put that in an abstract super class.

protected IReportContext getReportContext( final Object rcArgument )
    throws BirtException {
    if ( rcArgument == null ) {
        throw new BirtException( InnoventFunctionFactory.plugin_id,
                                            "ReportContext object is null in "
                                                    + this.getClass()
                                                            .getSimpleName(),
                                            InnoventFunctionFactory
                                                    .getResourceBundle() );
    }
    if ( ( rcArgument instanceof IReportContext ) != true ) {
        throw new BirtException( InnoventFunctionFactory.plugin_id,
                                            "ReportCtxt is not instance of IReportContext in "
                                                    + this.getClass()
                                                            .getSimpleName(),
                                            InnoventFunctionFactory
                                                    .getResourceBundle() );
    }
    return (IReportContext) rcArgument;
}

Not completely tricky, but who wants to write that code over and over again?

The other statement is the call to test for likely SQL Injection tokens.  I know the list is not complete, but what do you expect when you have free advice.

    protected void testSqlInjection( String paramValue )
        throws BirtException {
        List<String> errList = new ArrayList<String>();
        try {
            Pattern regex = Pattern.compile( "[%]|[']|[;]|[(]|[)]|[=]" );
            Matcher regexMatcher = regex.matcher( paramValue );
            while ( regexMatcher.find() ) {
                errList.add( regexMatcher.group() );
            }
        }
        catch ( PatternSyntaxException ex ) {
            // Syntax error in the regular expression
        }
        if ( errList == null || errList.size() == 0 ) {
            return;
        }
        // Uh oh, something is fishy in this parameter
        StringBuffer sb = new StringBuffer();
        sb.append( "Failure to add parameter value :\n" );
        sb.append( paramValue );
        sb
                .append( "The following values are not allowed in parameters \n{ " );
        for ( String err : errList ) {
            sb.append( err );
            sb.append( " " );
        }
        sb.append( " }" );
        throw new BirtException( InnoventFunctionFactory.plugin_id, sb
                .toString(), InnoventFunctionFactory.getResourceBundle() );
    }

Well that is about it.  It is probably a lot easier to just download the code and have a look.

I hope to see you all at EclipseCon in two weeks.  We have a number of really great talks on BIRT including a four hour tutorial by John Ward.  I am teaming up with Seth Grimes to have a discussion about the value of Business Intelligence. I'll be showing a few BIRT reports, but I am going to try and keep a layer or two above the gory implementation details.

One talk that I am really looking forward to will show how you can integrate BIRT into your Rich Internet Applications (RAP) projects.  This talk is by Benny Muskalla from EclipseSource and it promises to be really good.

Innovent Solutions, my company, will be at the exhibitors hall.  If you enjoy the blog, drop by and say hi.

12 comments:

Vishal Kharade said...

Hi Scott,

Its seems Gr8. I have faced the same problem in 2.2.

What is the procedure to attend EclipseCon?

Is it online?

Do Reply,
Thanks for the valuable Post.

With Regards,
VishalKP

anlu said...

HI...
I am really not good at understanding these things so will you please tell me exactly how I am supporsed to go around this. I need to do a multi select in my birt report but I just cant understand why such a thing would require so much code. Anyway... In need you to break down the whole process for me please.
Tell exactly which code I need because I tried to take evrything from your server but things didnt work out at all for me. I am knew to java so simpler instructions are really appreciated. which code do I need and which is unnecessary?
Cheers!

saravanan said...

Hi Scott,

I am trying to export as plug-in using eclipse export option but getting errors for the IReportContext as "The import org.eclipse.birt.report cannot be resolved". How can I resolve this? I have set the jar file in classpath but still I am getting the error.

Regards,
Saravanan

saravanan said...

Hi Scott,
I have exported the source as jar file and copied it to the eclipse plugins directory. In BIRT functions I can see the InnoventFunctions sub category but Could not see the functions in it. I am using BIRT 2.5.1 comes with Eclipse Galileo.

Regards,
Saravanan

Anonymous said...

saravanan,

Did an update today. Turns out there are some things that don't work between 2.3 and 2.5. We have two update sites and two versions of the control now available.

If you are on 2.5.1, you will want the 2.0.0 version.

saravanan said...

Thanks for your reply Scott. So you mean to say I need to downgrade the BIRT version to 2.0.0 to get the multi selection working(using plugin function).

Anonymous said...

Saravanan,

No the plugins have their own versions. The 2.0 version of the plugin goes with the 2.5.1 version of BIRT.

The 1.0 version of the plugin works with the 2.3.2 version of BIRT.

FYI: The 2.0 version of BIRT does not support this extension point at all.

Unknown said...

Hi,

I have checkout the code and i have tried to use it with Birt 2.5.1.
I export and install the project birt_example/innovent.birt.functions to test the AddParameterBinding function.
The query seems to be well updated with the right parameters but no result are returned.

I have added some custom logs. Here is
a trace :

one match found : IN ('MS:selectedTypes')
modifyDataSets - paramName = selectedTypes
modifyDataSets - param value = [Ljava.lang.Object;@179a711
modifyDataSets - param value is array of Object
parameter count = 0 dans select * from MY_TABLE
where my_type IN (
add parameter 'selectedTypes_01'
position = 0
valeur = T01
modified query = select * from MY_TABLE
where my_type IN (?)
reordering parameters
parameter name = selectedTypes_01
data type: string
position: 1
parameter datatype: string
default value: "T01"

Can you help ?

Anonymous said...

Lauent,

Having a bit of trouble figuring out what the logs are showing me. Is there any chance that you could open a bug on the functions site and add the report to the site so I can have a look at how you implemented the SQL?

Justin said...

Thanks for this post. It was quite helpful with respect to multi select list box.

I am currently in process of implementing this in my project. Will comment more once the implementation is done.

Anonymous said...

36Hi, I am wondering if there is a easier solution to this in the new birt version 4.2?

Jason Weathersby said...

If you are using the SQL Query Builder it should support named parameters.