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

Document created by RSA Customer Support Employee on Jun 14, 2016Last modified by RSA Customer Support Employee on Apr 21, 2017
Version 3Show Document
  • View in full screen mode

Article Content

Article Number000032810
Applies ToRSA Product Set: RSA Identity Governance and Lifecyle
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. Secure vulnerability fixes were added to 6.8.1P19 or later, 6.9.1 P09 or later, 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, 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