000032810 - RSA Identity Governance and Lifecycle SQL SELECT or SQL EXECUTE nodes with syntax errors

Document created by RSA Customer Support Employee on Jun 14, 2016Last modified by RSA Customer Support on Jan 23, 2018
Version 4Show Document
  • View in full screen mode

Article Content

Article Number000032810
Applies ToRSA Product Set: Identity Governance and Lifecycle
RSA Version/Condition: 6.8.1P19 or later, 6.9.1 P09 or later, 7.0.0 GA or later
 
IssueExperiencing one or more of the following symptoms:
  • 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)
CauseThere are a few parts to these issues:
  1. 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.
  2. 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}

  1. 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}'
ResolutionUpgrade to 6.8.1 P21 or later, 6.9.1 P 10 or later or 7.0.0 P 03 or later to obtain all fixes for the security vulnerability and related fixes.
Check the following SQL SELECT or SQL EXECUTE nodes:
  1. Remove any special q part of the SQL statement.
    1. 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}

  1. Change to:

UPDATE MY_CHANGE_REQUEST
SET notes=?

  1. Then bind the value for abc.
Note: We are now handling all special characters, therefore anything special like q[] syntax is discouraged.


  1. 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}'


Attachments

    Outcomes