search

daust / JasperReportsIntegration

JasperReportsIntegration provides an interface to use the JasperReports reporting engine in an Oracle database application, specifically with Oracle Application Express (Oracle APEX).
BSD 3-Clause "New" or "Revised" License
55 stars 25 forks source link

Display Query Source Code if Query has Error #91

Closed Samialfakih closed 2 years ago

Samialfakih commented 2 years ago

Type Exception Report

Message net.sf.jasperreports.engine.JRException: Error executing SQL statement for: #Report Name#.

Description The server encountered an unexpected condition that prevented it from fulfilling the request.

Exception

net.sf.jasperreports.engine.JRRuntimeException: net.sf.jasperreports.engine.JRException: Error executing SQL statement for: #Report Name#. net.sf.jasperreports.engine.fill.JRFillSubreport.prepare(JRFillSubreport.java:969) net.sf.jasperreports.engine.fill.JRFillElementContainer.prepareElements(JRFillElementContainer.java:542) net.sf.jasperreports.engine.fill.JRFillBand.fill(JRFillBand.java:453) net.sf.jasperreports.engine.fill.JRFillBand.fill(JRFillBand.java:428) net.sf.jasperreports.engine.fill.JRVerticalFiller.fillColumnBand(JRVerticalFiller.java:2602) net.sf.jasperreports.engine.fill.JRVerticalFiller.fillDetail(JRVerticalFiller.java:825) net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReportStart(JRVerticalFiller.java:266) net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReport(JRVerticalFiller.java:110) net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:621) net.sf.jasperreports.engine.fill.BaseReportFiller.fill(BaseReportFiller.java:414) net.sf.jasperreports.engine.fill.JRFiller.fill(JRFiller.java:120) net.sf.jasperreports.engine.JasperFillManager.fill(JasperFillManager.java:319) de.oc.integration.jasper.webapp.ReportWrapper.service(ReportWrapper.java:254) javax.servlet.http.HttpServlet.service(HttpServlet.java:733) org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53) Root Cause

net.sf.jasperreports.engine.JRException: Error executing SQL statement for: #Report Name#. net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:357) net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1257) net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:726) net.sf.jasperreports.engine.fill.BaseReportFiller.setParameters(BaseReportFiller.java:457) net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:584) net.sf.jasperreports.engine.fill.BaseReportFiller.fill(BaseReportFiller.java:414) net.sf.jasperreports.engine.fill.JRFillSubreport.fillSubreport(JRFillSubreport.java:826) net.sf.jasperreports.engine.fill.JRSubreportRunnable.run(JRSubreportRunnable.java:61) net.sf.jasperreports.engine.fill.AbstractThreadSubreportRunner.run(AbstractThreadSubreportRunner.java:221) java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) java.lang.Thread.run(Unknown Source) Root Cause

java.sql.SQLSyntaxErrorException: ORA-00932: inconsistent datatypes: expected DATE got NUMBER

oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:509)
oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:461)
oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1104)
oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:550)
oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:268)
oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:655)
oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:270)
oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:91)
oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:807)
oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:983)
oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1168)
oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3666)
oracle.jdbc.driver.T4CPreparedStatement.executeInternal(T4CPreparedStatement.java:1426)
oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3713)
oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1167)
oracle.ucp.jdbc.proxy.oracle$1ucp$1jdbc$1proxy$1oracle$1StatementProxy$2oracle$1jdbc$1internal$1OraclePreparedStatement$$$Proxy.executeQuery(Unknown Source)
net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:312)
net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1257)
net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:726)
net.sf.jasperreports.engine.fill.BaseReportFiller.setParameters(BaseReportFiller.java:457)
net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:584)
net.sf.jasperreports.engine.fill.BaseReportFiller.fill(BaseReportFiller.java:414)
net.sf.jasperreports.engine.fill.JRFillSubreport.fillSubreport(JRFillSubreport.java:826)
net.sf.jasperreports.engine.fill.JRSubreportRunnable.run(JRSubreportRunnable.java:61)
net.sf.jasperreports.engine.fill.AbstractThreadSubreportRunner.run(AbstractThreadSubreportRunner.java:221)
java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
java.lang.Thread.run(Unknown Source)

Root Cause

Error : 932, Position : 1811, Sql = #SQL Query Source Code#

daust commented 2 years ago

This is a security-relevant request. With that, we would give an attacker even more information about the internals of the database.

It can be done and I will make the detailed error message available: image

try {
    conn = appConfig.getConnection(urlCallInterface.dataSource);
    JasperFillManager fillmanager = JasperFillManager.getInstance(ctx);
    jasperPrint = fillmanager.fill(reportFile.reportFile.getPath(), reportParams, conn);
    conn.close();
} catch (SQLException e) {
    Utils.throwRuntimeException(e.getMessage());
} catch (JRException e) {
    Utils.throwRuntimeException(e.getCause() + e.getMessage());
} finally {
    DBUtils.closeQuietly(conn);
}

Using e.getCause() I can display the sql error message, will help with troubleshooting the query. In another test (when the column name has changed in the database), I will get the following error: 

java.sql.SQLSyntaxErrorException: ORA-00904: "DEMO_VALUE2": invalid identifier

Not sure that we will actually need the full query for troubleshooting. This should already give you enough information.

In a future release, I will add a new switch like ORDS implements the debugging output in the browser. By default, it is disabled and you need to activate it deliberately for your development environment.

Would implement it as a new switch in the application.properties file: 

# Showing error messages on the webpage are disabled by default
printDebugToScreen=false
daust commented 2 years ago

I can display the query as well ... but only, if debug is enabled.

Samialfakih commented 2 years ago

Actually, the query is displayed. I want to disable this feature, as you said "we would give an attacker even more information about the internals of the database". @daust

daust commented 2 years ago

Good to know ... but you didn't describe properly what you wanted ;)

Samialfakih commented 2 years ago

Yes !! I didn't describe it properly, I dealt with it as security issue, but it can be useful for development environment.

As I understood, if I disable debug, the query won't be displayed. How can I disable it? @daust

daust commented 2 years ago

Not yet implemented, is scheduled for the next release, 2.9.0. I guess I will implement the restricted mode (suppressing the error details) as a default. The errors will still be shown in the logfile on the server but not in the browser. Only, when you ENABLE debugging, it will be shown. This is the current plan.

Samialfakih commented 2 years ago

That's good. Thank you.

daust commented 2 years ago

In 2.9.0 ALL error messages will be SUPPRESSED by default. You can enable them for development systems, see #94 for details.