000035034 - RSA Identity Governance and Lifecycle SQL workflow node fails when there is a %  sign iis the query

Document created by RSA Customer Support Employee on Apr 20, 2017
Version 1Show Document
  • View in full screen mode

Article Content

Article Number000035034
Applies ToRSA Product Set: RSA Identity Governance and Lifecycle
RSA Version/Condition: 6.9.1 P08 and above
IssueThe RSA Identity Governance and Lifecycle SQL workflow node fails when there is a %  sign in the query.
The following error is captured in the WorkPoint.log: 
2017-03-12 14:33:22,061 [Worker_actionq#ActionQ1#WPDS_9] ERROR com.workpoint.server.script.ScriptEngine - A script error has occurred. Error occurred in Statement #1 in Script ID 17:WPDS, 
Script Name = 'SQL Query', Script type = 'Action'. Job ID = 3061:WPDS, Process Ref = 'WF_FF_39', Node Name = 'Occurance Check1', WorkItem = 12665:WPDS:1 
java.sql.SQLException: Invalid column index 
at oracle.jdbc.driver.OraclePreparedStatement.setStringInternal(OraclePreparedStatement.java:5377) 
at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:8406) 
at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:8336) 
at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:9097)

ResolutionWe must not do any direct manipulation of data expanded from a workflow variable. Although this had worked in previous versions, it is no longer accepted due to new formatting rules to prevent SQL injection vectors. 
If you need to manipulate the data expanded from a variable, do it separately from the variable expansion. 
For example, this syntax is no longer allowed: 

Instead use the following syntax: 
'%' || '${var}' || '%'

This allows the variable expansion to occur, after which the concatenation will contain the variable expansion and be safe to manipulate.