1

I am trying to write a Java customization that does a SQL query on the WORKORDER table using a where clause that a user can enter. However, this is super vulnerable to SQL injection. I know Maximo has some form of SQL where clause validation - users are allowed to enter where clauses in WOTRACK -> Advanced Query -> Where clause, and it seems from my experimenting that there is some sort of validation there to prevent SQL injection from happening. I am looking for a way to piggyback on Maximo's validation of user where clauses on the WORKORDER table, if one exists. Specifically looking for something in Maximo's Java classes that would accomplish this.

Jesse Williams
  • 140
  • 1
  • 8
  • "...using a where clause that a user can enter" -- NEVER. DO. THIS. I hope I was clear enough. If you do this you will be fighting a losing battle in terms of security. Get rid of this funcitonality. – The Impaler Aug 25 '20 at 19:13
  • I would normally agree with @TheImpaler, but IBM has been supporting the ability of users to enter their own where clauses in Maximo for more than a decade -- since at least Maximo 5.x, and we're now looking at Maximo 8. So, I'm not so sure this particular case of allowing users to enter where clauses is a "losing battle". – Preacher Aug 25 '20 at 22:56
  • @Preacher It's just I have too many ghosts. The problem I see is the OP would be one bug away of losing the entire database. Hackers have very clever tools. If the OP implements this it should be on a **secondary** database that is suitable to be compromised or lost. I would not recommend to do this on the main database. – The Impaler Aug 26 '20 at 01:42
  • @TheImpaler I hear you. I do. But since IBM Maximo already lets users do this, and since OP is trying to be safe by doing what the product already does *in the same way the product already does it* (using the same API), I think OP is fine. If OP was NOT going to do it the way Maximo already does it, they would not have posed this question, and I would have failed their code in a review. – Preacher Aug 26 '20 at 14:37
  • @TheImpaler I fully understand SQL injection and the implications. Since Maximo already supports it, there is no _added_ danger in me also supporting it, assuming I support it using the same APIs. To be fair to you, I am completely baffled as to how Maximo does this, and I suspect their protections against SQL injection here are imperfect, because I know this is a near impossible problem to solve. But if Maximo already supports it, and I use the same APIs they use, I am not _adding_ vulnerabilities to Maximo. I only asked the question because I know how dangerous SQL injection is. – Jesse Williams Aug 26 '20 at 15:39
  • https://community.ibm.com/community/user/iot/communities/community-home/digestviewer/viewthread?MessageKey=36e83991-aaa4-41d3-8dfb-6d5c7a7d7e58&CommunityKey=3d7261ae-48f7-481d-b675-a40eb407e0fd&tab=digestviewer#bm36e83991-aaa4-41d3-8dfb-6d5c7a7d7e58 – User1974 Aug 27 '20 at 05:14
  • 2
    https://www.ibm.com/support/pages/protecting-against-sql-injection – User1974 Aug 27 '20 at 05:18
  • @TheImpaler I suspect that you are having a knee jerk reaction and have very little experience with Maximo. Maximo has an MBO layer that sits between the application and all JDBC calls. Among other things, the MBO layer scans the queries for injection attacks before they get sent to the database. It is fairly easy for users to inject long running queries that can degrade the performance of the database, but those are killed off pretty quickly. – JMR Apr 14 '21 at 20:32

1 Answers1

2

You may be interested in the psdi.common.parse.ParserService or psdi.mbo.SqlFormat classes. Check it out in Maximo's JavaDocs. There may be other classes, too, but those are good places to start. ParserService even has a checkSqlInjection(where) method.

Preacher
  • 2,127
  • 1
  • 11
  • 25
  • I had run onto that class and tried to use it, but any where clause I put into it (even invalid SQL) gets spit back to me without any errors. Based on the limited docs available on the class, it looks to me like they are using it to substitute values into SQL expressions (similar to PreparedStatement substituting ? with values, but with var names). I'm wondering if maybe I need to somehow tell the SqlFormat instance that it is operating on the WO table/app in some way before it will do anything. – Jesse Williams Aug 26 '20 at 15:30
  • Yes, SqlFormat is like PreparedStatement, but the named substitution variables only make sense if the where clause is used as a relationship from a parent object to a child object, in which case you would be looking at the constructor that takes an MboRemote, or maybe the format() method that takes MboSetInfo, so SqlFormat knows where to look for the attribute names to find the substitution values. SqlFormat also supports numbered substitution variables, which you set using the setObject() and etc methods. – Preacher Aug 26 '20 at 18:10
  • 1
    I have run onto `MboSetRemote.setUserWhereAfterParse`, which seems to be doing exactly what I would expect, mirroring the Maximo front end functionality perfectly in every case I have tested. Every where clause that maximo rejects results in an exception with the same error message maximo would give. As far as I can tell, this is the solution I am looking for. – Jesse Williams Aug 27 '20 at 23:58