January 31, 2022


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:

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.

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.

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.

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.

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.

:&UNIQUEID&The name of the unique Id field.
:&USERNAME&The current user name.
:YESThe SQL boolean value for true (1)
:NOThe SQL boolean value for false (0)
:&MBONAME&The name of the current Maximo Business Object e.g. WORKORDER
:&OWNERNAME&The name of the owner Maximo Business Object
:&APPNAME&The current application name, may be null.
:&PERSONID&The current user's person Id.
:&HOSTNAME&The value of the mxe.hostname property.
:TIMEThe server time formatted using the mxe.db.format.time property database time function.
:&DATE&The server date formatted using the mxe.db.format.date property database date function.
:&DATETIME&The server date and time formatted using the mxe.db.format.timestamp property database timestamp function.
:$OLD_ + ATTRIBUTEThis retrieves old value for an attribute and is in the format of $OLD_ plus the attribute name, e.g. $OLD_LEAD
:&OWNER&. + ATTRIBUTEThis retrieves the owner's attribute value and is in the format of &OWNER&. plus the attribute name, e.g. &OWNER&.WONUM

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

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


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]

In the time it took you to read this blog post...

You could have deployed Opqo, our game-changing mobile solution for Maximo.

Opqo is simple to acquire, simple to deploy and simple to use, with clear transparent monthly pricing that is flexible to your usage.