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.
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
If you have a specific type of value that you want to set, there are available methods for most types including
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 dayscalendar.add(Calendar.DATE, -30);var sqlf = new SqlFormat("statusdate > :1");sqlf.setDate(1, calendar.getTime());var whereClause = sqlf.format();
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
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 daysvar 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();
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
siteid values will replace the corresponding
:siteid values with correct formatting for the where clause, which in this case will be the
siteid values surrounded with single quotes.
var sqlf = new SqlFormat(mbo, "wonum = :wonum and siteid= :siteid");var whereClause = sqlf.format();
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.|
|:YES||The SQL boolean value for true (1)|
|:NO||The 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 |
|:TIME||The server time formatted using the |
|:&DATE&||The server date formatted using the |
|:&DATETIME&||The server date and time formatted using the |
|:$OLD_ + ATTRIBUTE||This retrieves old value for an attribute and is in the format of |
|:&OWNER&. + ATTRIBUTE||This retrieves the owner's attribute value and is in the format of |
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]