I'm writing an application which needs to store a time interval as a value in an Oracle database. There is a datatype INTERVAL HOUR TO SECOND that accepts properly formatted varchar2 string values as such:
insert into ar_tint (duration)
values (interval '29:03:20' hour to second)
The above SQL executes properly inside a <cfquery> block. Note that the argument must be in single quotes (per Oracle's own documentation on this subject), double quotes will throw an ORA-00917 'missing comma' error.
As this argument is a string, I expected the following to work after a <cfset igg='29:03:20'>:
values (interval <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value='#igg#'> hour to second)
What actually happens is that Oracle returns the following:
Error Executing Database Query.ORA-00917: missing commaThe error occurred in D:/webroot/path/to/interval.cfm: line 21 19 : 20 : INSERT into ar_tint (duration) 21 : values (interval <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value='#igg#'> hour to second) 22 : </cfquery> 23 : | VENDORERRORCODE | 917 | | SQLSTATE | 42000 | | SQL | INSERT into ar_tint (duration) values (interval (param 1) hour to second) |
|
Now, if I change the value attribute in that cfqueryparam to the string literal '29:03:20', I still get the error even though according to CF's own docs, <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value='29:03:20'> should evaluate to '29:03:20' (single quotes) which is correct. Changing the cfsqltype to CF_SQL_CHAR has no effect. Again, this is supposed to be passed as text, not converted to any internal date/time datatype (Oracle evaluates it).
Our university Information Security team strongly recommends we use CFQUERYPARAM to sanitize inputs, so I'm perplexed at what this CF function is passing as (param 1) to Oracle and whether binds are the source of the problem, or I'm somehow formatting this SQL incorrectly.
I've asked someone in IT to query SYS.V_$SQLAREA for us to read back the sent SQL to determine what's actually happening.
Here's the stack trace if it helps any:
java.sql.SQLSyntaxErrorException: ORA-00917: missing comma
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:91)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:173)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1030)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:194)
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:947)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1222)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3381)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3482)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1373)
at coldfusion.server.j2ee.sql.JRunPreparedStatement.execute(JRunPreparedStatement.java:91)
at coldfusion.sql.Executive.executeQuery(Executive.java:1380)
at coldfusion.sql.Executive.executeQuery(Executive.java:1127)
at coldfusion.sql.Executive.executeQuery(Executive.java:1058)
at coldfusion.sql.SqlImpl.execute(SqlImpl.java:341)
at coldfusion.tagext.sql.QueryTag.executeQuery(QueryTag.java:915)
at coldfusion.tagext.sql.QueryTag.doEndTag(QueryTag.java:590)
at cfinterval2ecfm1470562816.runPage(D:\webs\www6dev\library\info\makerbot\management\interval.cfm:21)
at coldfusion.runtime.CfJspPage.invoke(CfJspPage.java:231)
at coldfusion.tagext.lang.IncludeTag.doStartTag(IncludeTag.java:418)
at coldfusion.filter.CfincludeFilter.invoke(CfincludeFilter.java:65)
at coldfusion.filter.ApplicationFilter.invoke(ApplicationFilter.java:399)
at coldfusion.filter.RequestMonitorFilter.invoke(RequestMonitorFilter.java:48)
at coldfusion.filter.MonitoringFilter.invoke(MonitoringFilter.java:40)
at coldfusion.filter.PathFilter.invoke(PathFilter.java:94)
at coldfusion.filter.ExceptionFilter.invoke(ExceptionFilter.java:70)
at coldfusion.filter.ClientScopePersistenceFilter.invoke(ClientScopePersistenceFilter.java:28)
at coldfusion.filter.BrowserFilter.invoke(BrowserFilter.java:38)
at coldfusion.filter.NoCacheFilter.invoke(NoCacheFilter.java:46)
at coldfusion.filter.GlobalsFilter.invoke(GlobalsFilter.java:38)
at coldfusion.filter.DatasourceFilter.invoke(DatasourceFilter.java:22)
at coldfusion.filter.CachingFilter.invoke(CachingFilter.java:62)
at coldfusion.CfmServlet.service(CfmServlet.java:201)
at coldfusion.bootstrap.BootstrapServlet.service(BootstrapServlet.java:89)
at jrun.servlet.FilterChain.doFilter(FilterChain.java:86)
at coldfusion.monitor.event.MonitoringServletFilter.doFilter(MonitoringServletFilter.java:42)
at coldfusion.bootstrap.BootstrapFilter.doFilter(BootstrapFilter.java:46)
at jrun.servlet.FilterChain.doFilter(FilterChain.java:94)
at jrun.servlet.FilterChain.service(FilterChain.java:101)
at jrun.servlet.ServletInvoker.invoke(ServletInvoker.java:106)
at jrun.servlet.JRunInvokerChain.invokeNext(JRunInvokerChain.java:42)
at jrun.servlet.JRunRequestDispatcher.invoke(JRunRequestDispatcher.java:286)
at jrun.servlet.ServletEngineService.dispatch(ServletEngineService.java:543)
at jrun.servlet.jrpp.JRunProxyService.invokeRunnable(JRunProxyService.java:203)
at jrunx.scheduler.ThreadPool$DownstreamMetrics.invokeRunnable(ThreadPool.java:320)
at jrunx.scheduler.ThreadPool$ThreadThrottle.invokeRunnable(ThreadPool.java:428)
at jrunx.scheduler.ThreadPool$UpstreamMetrics.invokeRunnable(ThreadPool.java:266)
at jrunx.scheduler.WorkerThread.run(WorkerThread.java:66)
3
Replies
AdChoices