RSA Identity Governance and Lifecycle SQL SELECT or SQL EXECUTE nodes with syntax errors
Originally Published: 2016-03-23
Article Number
Applies To
RSA Version/Condition: 6.8.1P19 or later, 6.9.1 P09 or later, 7.0.0 GA or later
Issue
- Workflow is stuck in either the SQL SELECT or SQL EXECUTE node.
- Change requests go to an error state.
- Workflow presents one of the following errors:
java.sql.SQLException: ORA-00933: SQL command not properly ended
java.sql.SQLException: ORA-00907: missing right parenthesis
Invalid column index
The following are samples of complete stacks that can be found in the UI, the aveksaServer.log and or in the WorkPoint.log:
ERROR com.workpoint.server.script.ScriptEngine - A script error has occurred. Error occurred in Statement #1
in Script ID 20:WPDS, Script Name = 'SQL Execute', Script type = 'Action'. Job ID = 5037:WPDS,
Process Ref = 'WF_RR_4', Node Name = 'Update Notes', WorkItem = 28073:WPDS:1
java.sql.SQLException: Invalid column index
at oracle.jdbc.driver.OraclePreparedStatement.setStringInternal(OraclePreparedStatement.java:5303)
at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:8323)
Worker_actionq#ActionQ1#WPDS_46 ERROR com.workpoint.queue.work.ActionQWorker - Exception
caught during script evaluation. Script 'SQL Query', ScriptID=17:WPDS, JobID=64797:WPDS, JobRef='27803', ProcessRef='WF_FF_252',
JobRowVersion =4, NodeName='Select CAS1 as Email', JobNodeID
=1839699:WPDS, WorkItem=1582928:WPDS:1, FIFO=295382, Sort Order=1 (offset=85ms)
Exception was: com.workpoint.common.exception.ScriptEngineException: InvocationTargetException caught when executing Java class.
Command = '#Wed Jun 04 12:33:52 CDT 2008
Version=1
Method=query
JavaClass=com.aveksa.server.workflow.scripts.action.QueryActions
Parameter3=ThisJobNodeData
Parameter2=ThisJobData
Parameter1=ClientContext
ParameterType3=OpTypeNotSpecified
ParameterType2=OpTypeNotSpecified
ParameterType1=OpTypeNotSpecified
<New=Statement>
'; nested exception is:
java.sql.SQLException: ORA-00933: SQL command not properly ended
Error occurred in Statement #1 in Script ID 17:WPDS, Script Name = 'SQL Query', Script type = 'Action'. Job ID = 64797:WPDS,
Process Ref = 'WF_FF_252', Node Name = 'Select CAS1 as Email',
WorkItem = 1582928:WPDS:1
com.workpoint.common.exception.ScriptEngineException: InvocationTargetException caught when executing Java class.
Command = '#Wed Jun 04 12:33:52 CDT 2008
Version=1
Method=query
JavaClass=com.aveksa.server.workflow.scripts.action.QueryActions
Parameter3=ThisJobNodeData
Parameter2=ThisJobData
Parameter1=ClientContext
ParameterType3=OpTypeNotSpecified
ParameterType2=OpTypeNotSpecified
ParameterType1=OpTypeNotSpecified
State:
Error - Exception caught during script evaluation.
Script 'SQL Query', ScriptID=17:WPDS, JobID=235081:WPDS, JobRef='34164', ProcessRef='WF_RR_484', JobRowVersion =5, NodeName='WF Path',
JobNodeID=3237509:WPDS, WorkItem=2566197:WPDS:1, FIFO=1079311, Sort Order=1.
Exception was: com.workpoint.common.exception.ScriptEngineException: InvocationTargetException caught when executing Java class.
Command = '#Wed Jun 04 12:33:52 CDT 2008 Version=1
Method=query
JavaClass=com.aveksa.server.workflow.scripts.action.QueryActions
Parameter3=ThisJobNodeData
Parameter2=ThisJobData
Parameter1=ClientContext
ParameterType3=OpTypeNotSpecified
ParameterType2=OpTypeNotSpecified
ParameterType1=OpTypeNotSpecified ';
nested exception is: java.sql.SQLException: Invalid column index Error occurred in Statement #1 in Script ID 17:WPDS
, Script Name = 'SQL Query'
, Script type = 'Action'.
Job ID = 235081:WPDS
, Process Ref = 'WF_RR_484'
, Node Name = 'WF Path'
, WorkItem = 2566197:WPDS:1
2015-12-29 12:26:10,099 INFO [STDOUT] 2015-12-29 12:26:10,098 [Worker_actionq#ActionQ1#WPDS_2] 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 = 235081:WPDS, Process Ref = 'WF_RR_484', Node Name = 'WF Path', WorkItem = 2566197:WPDS:1
java.sql.SQLException: Invalid column index
at oracle.jdbc.driver.OraclePreparedStatement.setStringInternal(OraclePreparedStatement.java:5303)
at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:8301)
at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:8237)
at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:8981)
at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:8962)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.setObject(OraclePreparedStatementWrapper.java:214)
at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.setObject(WrappedPreparedStatement.java:478)
at com.aveksa.server.workflow.scripts.utils.variable.VariableUtils.getEvaluatedPreparedStatement(VariableUtils.java:168)
at com.aveksa.server.workflow.scripts.action.QueryActions.processSql(QueryActions.java:490)
at com.aveksa.server.workflow.scripts.action.QueryActions.access$000(QueryActions.java:45)
at com.aveksa.server.workflow.scripts.action.QueryActions$1.preProcess(QueryActions.java:440)
at com.aveksa.server.workflow.RetryEnabledOperationsJobUtils.executeJobStrategyWithProcessing(RetryEnabledOperationsJobUtils.java:168)
at com.aveksa.server.workflow.RetryEnabledOperationsJobUtils.saveWithProcessing(RetryEnabledOperationsJobUtils.java:105)
at com.aveksa.server.workflow.scripts.action.QueryActions.query(QueryActions.java:435)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:622)
at com.workpoint.server.script.StatementEngineJava.execute(Unknown Source)
at com.workpoint.server.script.ScriptEngine.A(Unknown Source)
at com.workpoint.server.script.ScriptEngine.execute(Unknown Source)
at com.workpoint.server.monitor.ActionMonitorHelper.A(Unknown Source)
at com.workpoint.server.monitor.ActionMonitorHelper.execute(Unknown Source)
at com.workpoint.server.pojo.ScriptExecAsyncPvtBean.executeScriptMonitor(Unknown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:622)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:112)
at org.jboss.ejb3.interceptor.InvocationContextImpl.proceed(InvocationContextImpl.java:166)
at org.jboss.ejb3.interceptor.EJB3InterceptorsInterceptor.invoke(EJB3InterceptorsInterceptor.java:63)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at org.jboss.ejb3.entity.TransactionScopedEntityManagerInterceptor.invoke(TransactionScopedEntityManagerInterceptor.java:54)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at org.jboss.ejb3.AllowedOperationsInterceptor.invoke(AllowedOperationsInterceptor.java:47)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at org.jboss.aspects.tx.TxPolicy.invokeInNoTx(TxPolicy.java:66)
at org.jboss.aspects.tx.TxInterceptor$Supports.invoke(TxInterceptor.java:144)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at org.jboss.aspects.tx.TxPropagationInterceptor.invoke(TxPropagationInterceptor.java:95)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at org.jboss.ejb3.stateless.StatelessInstanceInterceptor.invoke(StatelessInstanceInterceptor.java:62)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at org.jboss.aspects.security.AuthenticationInterceptor.invoke(AuthenticationInterceptor.java:77)
at org.jboss.ejb3.security.Ejb3AuthenticationInterceptor.invoke(Ejb3AuthenticationInterceptor.java:110)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at org.jboss.ejb3.ENCPropagationInterceptor.invoke(ENCPropagationInterceptor.java:46)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at org.jboss.ejb3.asynchronous.AsynchronousInterceptor.invoke(AsynchronousInterceptor.java:106)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at org.jboss.ejb3.stateless.StatelessContainer.dynamicInvoke(StatelessContainer.java:304)
at org.jboss.ejb3.remoting.IsLocalInterceptor.invokeLocal(IsLocalInterceptor.java:81)
at org.jboss.ejb3.remoting.IsLocalInterceptor.invoke(IsLocalInterceptor.java:72)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at org.jboss.ejb3.stateless.StatelessRemoteProxy.invoke(StatelessRemoteProxy.java:107)
at com.sun.proxy.$Proxy323.executeScriptMonitor(Unknown Source)
at com.workpoint.client.Monitor.executeScriptMonitor(Unknown Source)
at com.workpoint.queue.work.ActionQWorker.A(Unknown Source)
at com.workpoint.queue.work.ActionQWorker.run(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1146)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:701)
2015-12-29 12:26:10,431 INFO [STDOUT] 2015-12-29 12:26:10,430 [Worker_actionq#ActionQ1#WPDS_2]
ERROR com.workpoint.queue.work.ActionQWorker - Exception caught during script evaluation. Script 'SQL Query',
ScriptID=17:WPDS, JobID=235081:WPDS, JobRef='34164', ProcessRef='WF_RR_484', JobRowVersion =5, NodeName='WF Path',
JobNodeID=3237509:WPDS, WorkItem=2566197:WPDS:1, FIFO=1079311, Sort Order=1 (offset=16ms)
Exception was: com.workpoint.common.exception.ScriptEngineException: InvocationTargetException caught when executing Java class.
Command = '#Wed Jun 04 12:33:52 CDT 2008
Version=1
Method=query
JavaClass=com.aveksa.server.workflow.scripts.action.QueryActions
Parameter3=ThisJobNodeData
Parameter2=ThisJobData
Parameter1=ClientContext
ParameterType3=OpTypeNotSpecified
ParameterType2=OpTypeNotSpecified
ParameterType1=OpTypeNotSpecified
<New=Statement>
'; nested exception is:
java.sql.SQLException: Invalid column index Error occurred in Statement #1 in Script ID 17:WPDS, Script Name = 'SQL Query',
Script type = 'Action'. Job ID = 235081:WPDS, Process Ref = 'WF_RR_484', Node Name = 'WF Path', WorkItem = 2566197:WPDS:1
com.workpoint.common.exception.ScriptEngineException: InvocationTargetException caught when executing Java class.
Command = '#Wed Jun 04 12:33:52 CDT 2008
Version=1
Method=query
JavaClass=com.aveksa.server.workflow.scripts.action.QueryActions
Parameter3=ThisJobNodeData
Parameter2=ThisJobData
Parameter1=ClientContext
ParameterType3=OpTypeNotSpecified
ParameterType2=OpTypeNotSpecified
ParameterType1=OpTypeNotSpecified
<New=Statement>
'; nested exception is:
java.sql.SQLException: Invalid column index Error occurred in Statement #1 in Script ID 17:WPDS, Script Name = 'SQL Query',
Script type = 'Action'. Job ID = 235081:WPDS, Process Ref = 'WF_RR_484', Node Name = 'WF Path', WorkItem = 2566197:WPDS:1
at com.workpoint.server.script.ScriptEngine.execute(Unknown Source)
at com.workpoint.server.monitor.ActionMonitorHelper.A(Unknown Source)
at com.workpoint.server.monitor.ActionMonitorHelper.execute(Unknown Source)
at com.workpoint.server.pojo.ScriptExecAsyncPvtBean.executeScriptMonitor(Unknown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:622)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:112)
at org.jboss.ejb3.interceptor.InvocationContextImpl.proceed(InvocationContextImpl.java:166)
at org.jboss.ejb3.interceptor.EJB3InterceptorsInterceptor.invoke(EJB3InterceptorsInterceptor.java:63)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at org.jboss.ejb3.entity.TransactionScopedEntityManagerInterceptor.invoke(TransactionScopedEntityManagerInterceptor.java:54)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at org.jboss.ejb3.AllowedOperationsInterceptor.invoke(AllowedOperationsInterceptor.java:47)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at org.jboss.aspects.tx.TxPolicy.invokeInNoTx(TxPolicy.java:66)
at org.jboss.aspects.tx.TxInterceptor$Supports.invoke(TxInterceptor.java:144)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at org.jboss.aspects.tx.TxPropagationInterceptor.invoke(TxPropagationInterceptor.java:95)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at org.jboss.ejb3.stateless.StatelessInstanceInterceptor.invoke(StatelessInstanceInterceptor.java:62)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at org.jboss.aspects.security.AuthenticationInterceptor.invoke(AuthenticationInterceptor.java:77)
at org.jboss.ejb3.security.Ejb3AuthenticationInterceptor.invoke(Ejb3AuthenticationInterceptor.java:110)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at org.jboss.ejb3.ENCPropagationInterceptor.invoke(ENCPropagationInterceptor.java:46)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at org.jboss.ejb3.asynchronous.AsynchronousInterceptor.invoke(AsynchronousInterceptor.java:106)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at org.jboss.ejb3.stateless.StatelessContainer.dynamicInvoke(StatelessContainer.java:304)
at org.jboss.ejb3.remoting.IsLocalInterceptor.invokeLocal(IsLocalInterceptor.java:81)
at org.jboss.ejb3.remoting.IsLocalInterceptor.invoke(IsLocalInterceptor.java:72)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at org.jboss.ejb3.stateless.StatelessRemoteProxy.invoke(StatelessRemoteProxy.java:107)
at com.sun.proxy.$Proxy323.executeScriptMonitor(Unknown Source)
at com.workpoint.client.Monitor.executeScriptMonitor(Unknown Source)
at com.workpoint.queue.work.ActionQWorker.A(Unknown Source)
at com.workpoint.queue.work.ActionQWorker.run(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1146)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:701)Cause
- Security vulnerability fixes were added to 6.8.1 P19 or later, 6.9.1 P09 or later and 7.0.0 GA or later that prevent variables entered in custom forms in a non-secured manner by the application and allowing SQL injection and XSS attacks when they are referred to in a SQL SELECT Node, SQL EXECUTE Node or E-mail Node in the workflow.
- Specific syntax with special quoting done in the SQL statement (i. e., the q part in the statement). For example:
UPDATE MY_CHANGE_REQUEST
SET notes=SUBSTR(REPLACE(q'[${jobUserData_PublicData_BusinessJustification}]',Chr(39),Chr(96)),0,4000)
WHERE ID =${access_request_requestID}
- Using quotes without isolating Workflow variables. For example:
SELECT form_id AS formid FROM MY_CHANGE_REQUESTS
WHERE name = '${access_request_cri_meu_UNIQUE_ID}${access_request_name}'Resolution
Check the following SQL SELECT or SQL EXECUTE nodes:
- Remove any special q part of the SQL statement.
- In the example, the q'[]' is used to do special quoting. We are now doing variable replacement using bind parameters, so we are updating the SQL on the fly from something like:
UPDATE MY_CHANGE_REQUEST
SET notes=${access_request_requestID}
{abc}
- Change to:
UPDATE MY_CHANGE_REQUEST SET notes=?
- Then bind the value for abc.
Note: We are now handling all special characters, therefore anything special like q[] syntax is discouraged.
- Separate each workflow variable using single quotes and use || to append/concatenate values, if needed. For example:
SELECT form_id AS formid FROM MY_CHANGE_REQUESTS
WHERE name = '${access_request_cri_meu_UNIQUE_ID}'||'${access_request_name}'
Related Articles
Using SQL Select nodes with RSA Identity Management and Governance Number of Views RSA Governance & Lifecycle Webservice RESTful Connector Datasheet Number of Views Unable to search or filter on specific user in Approval Node in a workflow in RSA Identity Governance and Lifecycle Versio… Number of Views Approvals using PublicData_ form variables auto-approve by System in RSA Identity Governance & Lifecycle Number of Views Select User Groups for Self-Service Number of Views
Trending Articles
How to recover the Application and AFX after an unexpected database failure in RSA Identity Governance & Lifecycle Troubleshooting AFX Connector issues in RSA Identity Governance & Lifecycle RSA MFA Agent 2.3.6 for Microsoft Windows Installation and Administration Guide RSA Release Notes for RSA Authentication Manager 8.8 RSA Authentication Manager Upgrade Process
Don't see what you're looking for?
