Experiencing 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)
There are a few parts to these issues:
- 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}'
Upgrade 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:
- 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}'