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.