Introduction

Filtering data in Maximo Business Objects is a common requirement when developing an automation script. I often see the MboSet.setWhere() method invoked with a literal String where clause or more often with a concatenated string such as "wonum = '" + wonum + "'". This approach is error prone since it does not handle proper formatting based on data type and different databases have slightly different syntaxes that must be accounted for, which is not done with this strategy.

The more serious problem with this approach is that it allows for SQL injection. SQL injection lets users provide malicious values for what is being concatenated. Using our previous example, a user may provide '; drop table workorder; for the wonum value, which will result in select * from workorder where wonum = ''; drop table workorder;. With the semicolon in the where clause, the server will interpret this as two separate commands, one for select * from workorder where wonum = '' and the other as drop table workorder. As you can see, the results of this would be catastrophic, with the WORKORDER table being dropped.

For an overview of SQL injection, you can review the Wikipedia article here: https://en.wikipedia.org/wiki/SQL_injection.

Fortunately IBM provides the psdi.mbo.SqlFormat class that performs database and type specific formatting of SQL where clauses. In this post we will explore how to use the psdi.mbo.SqlFormat class to compose correctly formatted queries for any database with any attribute type.

Basic Formatting

The psdi.mbo.SqlFormat provides a simple positional replacement strategy that is indexed starting with the number one. For example if we want to build a where clause that queries for a work order based on the work order number and site id, we would create a new SqlFormat object like the following:

Copy
SqlFormat = Java.type("psdi.mbo.SqlFormat");

var sqlf = new SqlFormat("wonum = :1 and siteid = :2");

From here we can provide the format values using the numeric index. In this case because the expected values are String values we are going to use the setObject() function, which takes the index to replace, the Maximo object and attribute name that are used by the SqlFormat class to determine the correct type for formatting, and then the value. Once these values have been provided we call the format() method to get the formatted where clause.

Copy
SqlFormat = Java.type("psdi.mbo.SqlFormat");

var sqlf = new SqlFormat("wonum = :1 and siteid = :2");

sqlf.setObject(1, "WORKORDER", "WONUM", "1234");
sqlf.setObject(2, "WORKORDER", "SITEID", "bedford");

var whereClause = sqlf.format();

Note that because the SITEID attribute is of Maximo type UPPER the type conversion to uppercase is handled by the SqlFormat class.

If you have a specific type of value that you want to set, there are available methods for most types including boolean, byte[], Date, double, float, int, long, time (as Date), and timestamp (as Date). For example if I want to find work orders that have a status date in the last 30 days I can use the setDate() method as shown below.

Copy
SqlFormat = Java.type("psdi.mbo.SqlFormat");
Calendar = Java.type("java.util.Calendar");

// get a new Calendar instance.
var calendar = Calendar.getInstance();

// set the calendar back 30 days
calendar.add(Calendar.DATE, -30);

var sqlf = new SqlFormat("statusdate > :1");

sqlf.setDate(1, calendar.getTime());

var whereClause = sqlf.format();

The SqlFormat class will handle the date conversion and use the appropriate database functions to transform the Java Date object to part of the where clause.

In the previous example I used a java.util.Calendar object to do the date calculation. This pattern was replaced in Java 8 with the introduction of the java.time package. Since Maximo is generally based on older Java patterns it is a little cumbersome converting between the two, but here is the same example using the more modern java.time.LocalDateTime class.

Copy
LocalDateTime = Java.type("java.time.LocalDateTime");
ZoneId = Java.type("java.time.ZoneId");
Date = Java.type("java.util.Date");

// get an instance of LocalDateTime and subtract 30 days
var dateTime = LocalDateTime.now(ZoneId.systemDefault()).minusDays(30);

var sqlf = new SqlFormat("statusdate > :1");

sqlf.setDate(1, Date.from(dateTime.atZone(ZoneId.systemDefault()).toInstant()));

var whereClause = sqlf.format();

Formatting with a Mbo

So far we have looked at creating a formatted SQL clause using indexed replacement values. The SqlFormat class also provides the ability to replace values provided by a Mbo. This is what is used for out of the box relationships that are defined in the Database Configuration application.

In the example below we assume that the implicit mbo variable is based on on the WORKORDER object. The SqlFormat class is created with a reference to the mbo and a where clause template that includes colon prefixed attribute names that will be replaced with values from the provided Mbo. In the example below, the WORKORDER object wonum and siteid values will replace the corresponding :wonum and :siteid values with correct formatting for the where clause, which in this case will be the wonum and siteid values surrounded with single quotes.

Copy
var sqlf = new SqlFormat(mbo, "wonum = :wonum and siteid= :siteid");

var whereClause = sqlf.format();

Special Values

In addition to specific values and attribute substitution there are a number of special replacement values. The table below provides a list of these special values.

For example if we want to select non-history work orders that have an owner equal to the current user name we can

Copy
var sqlf = new SqlFormat("ishistory = :no and owner = :&PERSONID&");

var whereClause = sqlf.format();

Conclusion

In this post we demonstrated how using literal String concatenation is error prone and fragile as it does not account for proper data formatting or escaping of reserved characters. We then covered using the psdi.mbo.SqlFormat class to create robust, consistent and flexible SQL where clauses. Finally, we reviewed the special replacement values supported by the psdi.mbo.SqlFormat class to further refine your queries and provide handling for special cases such as providing user specific queries.

If you have any questions or comments please reach out to us at [email protected]