This website uses cookies. By clicking Accept, you consent to the use of cookies. Click Here to learn more about how we use cookies.
Accept
Reject
Register Now
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

SecurID® Governance & Lifecycle Knowledge Base

Find answers to your questions and identify resolutions for known issues with knowledge base articles written by SecurID Governance & Lifecycle experts.

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

Article Number

000032810

Applies To

RSA 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
 

Issue

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)

Cause

There 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}'

Resolution

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:
  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}'
0 Likes
Was this article helpful? Yes No
No ratings

In this article

Version history
Last update:
‎2020-12-12 09:57 PM
Updated by:
Administrator Administrator

Related Content

© 2023 RSA Security LLC or its affiliates. All rights reserved.