RSA Identity Governance and Lifecycle SQL workflow node fails when there is a % sign iis the query
Originally Published: 2017-04-13
Last Modified: 2023-10-06
Article Number
000061037
Applies To
RSA Product Set: RSA Identity Governance and Lifecycle
RSA Version/Condition: 6.9.1 P08 and above
 
Issue
The 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)


 
Resolution
We 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:  
'%${var}%'

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.

Related Articles

Trending Articles

Don't see what you're looking for?