Highlighted

Cannot use SQL interval literals with

New Here ,
Dec 02, 2016

Copy link to clipboard

Copied

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 comma

The 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)

TOPICS
Database access

Views

544

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

Cannot use SQL interval literals with

New Here ,
Dec 02, 2016

Copy link to clipboard

Copied

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 comma

The 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)

TOPICS
Database access

Views

545

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Dec 02, 2016 0
Advocate ,
Dec 02, 2016

Copy link to clipboard

Copied

<cfset igg='29:03:20'>

Don't you mean:

<cfset igg="'29:03:20'">

Cheers

Eddie

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Dec 02, 2016 0
New Here ,
Dec 02, 2016

Copy link to clipboard

Copied

No, because the string delimiters (quotes) are supposed to be provided by CFQUERYPARAM's output, not the developer (and, it doesn't work if you try). And to repeat myself, this is a formatted string, not an exotic data type.

Our IT staff are against the wall today, so I'll need to schedule a time next week to run the query and have them check the SQL query entries (before the cache clears itself) to see what's happening.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Dec 02, 2016 0
BKBK LATEST
Adobe Community Professional ,
Dec 08, 2016

Copy link to clipboard

Copied

As you have to pass the quote characters, too, what about something like

<cfset igg="\'29:03:20\'">

followed by

values (interval <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#igg#"> hour to second)

Or

<cfset iggString ="interval \'29:03:20\' hour to second">

followed by

values (<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#iggString#">)

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Dec 08, 2016 0