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.

Raising Errors in Event Handlers

Let me start with a simple scenario.  You are writing a BIRT event handler and you expect the user to set a UserDefinedProperty that will guide the event handler code.  How do you handle the situation where the user forgets to add the UserDefinedVariable?  How do you provide a message back to the user to help them solve the problem?

In the past I have focused on using logging and stepping through code.  While effective, it requires the person using your event handler code to have a relatively high level of sophistication.  Wouldn't it be great if you could pass an error message from your event handler code to the ReportEngine, so that the message would be displayed through the standard error handling mechanism?

The methods that are used to add exceptions to a report are not visible from the Script API.  I hope that we can get an enhancement into BIRT 2.5 that will make this easy to do, but what if you want to add code like this to the early version of BIRT.

It turns out that it is easy enough to do using Java reflection.  The ReportContext object wraps an instance of the ExecutionContext.  ExecutionContext supports a method:
     addException(BirtException exception)


All we need to do is figure out how to expose this method to our EventHandlers (Java or JavaScript).  To do this I created a static method called addBirtException, the complete code listing is as follows:

public static void addBirtException(IReportContext reportContext , String errorMessage) {
    addBirtException(reportContext , errorMessage, BirtException.WARNING);
}

public static void addBirtException(IReportContext reportContext , String errorMessage, Integer severity) {
    
    BirtException be = new BirtException("org.eclipse.birt.report.engine", errorMessage, new Object[]{""} );
    be.setSeverity(severity);

    try {
        // get the protect field 'context' from reportContext
        Class rciClass = reportContext.getClass();
        Field fieldFromScript = rciClass.getDeclaredField("context");
        if (fieldFromScript == null) {
            return;
        }

        // instantiate the ExecutionContext object that
        // populates the context field
        fieldFromScript.setAccessible(true);
        Object execContext = fieldFromScript.get(reportContext);

        // now get a handle to the addException method on ExecutionObject
        Class execClass = execContext.getClass();
        Method addMethod = execClass.getMethod("addException", new Class[] { BirtException.class });

        // finally invoke the method which will add the BirtException 
        // to the report
        addMethod.setAccessible(true);
        addMethod.invoke(execContext, new Object[] { be });

        // Lots of ways for this to break...
    } catch (Exception e) {
        logger.warning(e.getMessage());
        e.printStackTrace();
    } 
    return;
}

Once you have the ScriptUtil class in your classpath you can call it from your Java class by  adding this code:

    ScriptUtil.addBirtException(reportContext, "Simple Error Message", BirtException.WARNING);

Will cause your report to run with the following error messages to display in your report.

But why stop there.  Now that you have the ability you can go ahead and provide detailed help back to the people that are using your report or event handler class:

    ScriptUtil.addBirtException(reportContext, "Simple Error Message", BirtException.WARNING);

    StringBuffer sb = new StringBuffer();
    sb.append("Complex error message that is designed to help a\n");
    sb.append("report developer that is using your event handler\n");
    sb.append("to successfully debug an improper implementation.\n");
    sb.append("Could also be used to help end users identify report\n");
    sb.append("issues to support and help desk staff.\n");
    sb.append("\t - Can include error codes from other products\n");
    sb.append("\t - Can include domain specific messages.\n\n");
    sb.append("No more looking around for message logs or tracing through\n");
    sb.append("gnarly stack traces");
    ScriptUtil.addBirtException(reportContext, sb.toString(), BirtException.WARNING);

gives you:

This code is also easily callable from JavaScript.

Packages.deapi_event_handler.ScriptUtil.addBirtException(addBirtException, "my message");

So pretty cool eh?

But there is more, the BirtException supports localization, resource bundles, formatting all of the features that you may want in advanced report development.

Using BIRT with PHP

Just wanted to post a note saying The Server Side just published an article we worked on that demonstrates how BIRT can be called using PHP. This article covers using the BIRT Report Engine within PHP to implement running and rendering of reports and includes details on how to handle drill through and BIRT libraries.

The article is available here.

A version of the integration (Containing BIRT 2.3.1) is also available on BIRT Exchange here. Be sure to look at the notes in the download page that discuss JDBC drivers and event handler jars.