9.0.2 with external Oracle DB fails

[RobSHK]

I'm trying to update our Database using the maven goal update-only,
many like the following errors apears:
[ERROR] Failed to process CVE-2022-42344 org.owasp.dependencycheck.analyzer.exception.UnexpectedAnalysisException: java.sql.SQLException: Invalid column type: 16 at org.owasp.dependencycheck.data.nvdcve.CveDB.updateOrInsertVulnerability (CveDB.java:1058) at org.owasp.dependencycheck.data.nvdcve.CveDB.updateVulnerability (CveDB.java:866) at org.owasp.dependencycheck.data.update.nvd.api.NvdApiProcessor.call (NvdApiProcessor.java:87) at org.owasp.dependencycheck.data.update.nvd.api.NvdApiProcessor.call (NvdApiProcessor.java:33) at java.util.concurrent.FutureTask.run (FutureTask.java:264) at java.util.concurrent.ThreadPoolExecutor.runWorker (ThreadPoolExecutor.java:1128) at java.util.concurrent.ThreadPoolExecutor$Worker.run (ThreadPoolExecutor.java:628) at java.lang.Thread.run (Thread.java:829) Caused by: java.sql.SQLException: Invalid column type: 16 at oracle.jdbc.driver.OracleStatement.getInternalType (OracleStatement.java:4486) at oracle.jdbc.driver.OraclePreparedStatement.setNullCritical (OraclePreparedStatement.java:4390) at oracle.jdbc.driver.OraclePreparedStatement.setNull (OraclePreparedStatement.java:4374) at oracle.jdbc.driver.OraclePreparedStatementWrapper.setNull (OraclePreparedStatementWrapper.java:937) at org.apache.commons.dbcp2.DelegatingPreparedStatement.setNull (DelegatingPreparedStatement.java:521) at org.apache.commons.dbcp2.DelegatingPreparedStatement.setNull (DelegatingPreparedStatement.java:521) at org.owasp.dependencycheck.data.nvdcve.CveDB.updateOrInsertVulnerability (CveDB.java:981) at org.owasp.dependencycheck.data.nvdcve.CveDB.updateVulnerability (CveDB.java:866) at org.owasp.dependencycheck.data.update.nvd.api.NvdApiProcessor.call (NvdApiProcessor.java:87) at org.owasp.dependencycheck.data.update.nvd.api.NvdApiProcessor.call (NvdApiProcessor.java:33) at java.util.concurrent.FutureTask.run (FutureTask.java:264) at java.util.concurrent.ThreadPoolExecutor.runWorker (ThreadPoolExecutor.java:1128) at java.util.concurrent.ThreadPoolExecutor$Worker.run (ThreadPoolExecutor.java:628) at java.lang.Thread.run (Thread.java:829) [ERROR] Failed to process CVE-2023-38218 org.owasp.dependencycheck.analyzer.exception.UnexpectedAnalysisException: java.sql.SQLException: Invalid column type: 16 at org.owasp.dependencycheck.data.nvdcve.CveDB.updateOrInsertVulnerability (CveDB.java:1058) at org.owasp.dependencycheck.data.nvdcve.CveDB.updateVulnerability (CveDB.java:866) at org.owasp.dependencycheck.data.update.nvd.api.NvdApiProcessor.call (NvdApiProcessor.java:87) at org.owasp.dependencycheck.data.update.nvd.api.NvdApiProcessor.call (NvdApiProcessor.java:33) at java.util.concurrent.FutureTask.run (FutureTask.java:264) at java.util.concurrent.ThreadPoolExecutor.runWorker (ThreadPoolExecutor.java:1128) at java.util.concurrent.ThreadPoolExecutor$Worker.run (ThreadPoolExecutor.java:628) at java.lang.Thread.run (Thread.java:829) Caused by: java.sql.SQLException: Invalid column type: 16 at oracle.jdbc.driver.OracleStatement.getInternalType (OracleStatement.java:4486) at oracle.jdbc.driver.OraclePreparedStatement.setNullCritical (OraclePreparedStatement.java:4390) at oracle.jdbc.driver.OraclePreparedStatement.setNull (OraclePreparedStatement.java:4374) at oracle.jdbc.driver.OraclePreparedStatementWrapper.setNull (OraclePreparedStatementWrapper.java:937) at org.apache.commons.dbcp2.DelegatingPreparedStatement.setNull (DelegatingPreparedStatement.java:521) at org.apache.commons.dbcp2.DelegatingPreparedStatement.setNull (DelegatingPreparedStatement.java:521) at org.owasp.dependencycheck.data.nvdcve.CveDB.updateOrInsertVulnerability (CveDB.java:981) at org.owasp.dependencycheck.data.nvdcve.CveDB.updateVulnerability (CveDB.java:866) at org.owasp.dependencycheck.data.update.nvd.api.NvdApiProcessor.call (NvdApiProcessor.java:87) at org.owasp.dependencycheck.data.update.nvd.api.NvdApiProcessor.call (NvdApiProcessor.java:33) at java.util.concurrent.FutureTask.run (FutureTask.java:264) at java.util.concurrent.ThreadPoolExecutor.runWorker (ThreadPoolExecutor.java:1128) at java.util.concurrent.ThreadPoolExecutor$Worker.run (ThreadPoolExecutor.java:628) at java.lang.Thread.run (Thread.java:829)
Therefore I'm trying as suggested to purge the DB.

Describe the bug
Trying to purge the DB as per https://github.com/jeremylong/DependencyCheck#breaking-changes

Version of dependency-check used
The problem occurs using version 9.0.2 of the maven plugin

Log file

Executing Maven:  -B -f JOB_NAME/workspace/pom.xml -s /home/jenkins/.m2/settings.xml -U org.owasp:dependency-check-maven:purge
[INFO] Scanning for projects...
[INFO] 
[INFO] ------------------< de.shgruppe.sccm-admin:owasp-pom >------------------
[INFO] Building SCCM-Admin owasp DB 1.0.0
[INFO] --------------------------------[ jar ]---------------------------------
[INFO] 
[INFO] --- dependency-check-maven:9.0.2:purge (default-cli) @ owasp-pom ---
[INFO] Unable to purge database; the database file does not exist: /var/lib/jenkins/local-maven-repo/org/owasp/dependency-check-utils/9.0.2/../../dependency-check-data/9.0/odc.mv.db
[INFO] RetireJS repo removed successfully
[INFO] Hosted suppression file removed successfully
[INFO] Cache directory purged
[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS

pom.xml
<configuration> <databaseDriverName>oracle.jdbc.OracleDriver</databaseDriverName> <connectionString>jdbc:oracle:thin:@owasp-db:1521:owasp</connectionString> <serverId>owasp-db</serverId> <nvdApiKey>${nvdApiKey}</nvdApiKey> </configuration>
To Reproduce
Steps to reproduce the behavior:

1. Go to '...'
2. Click on '....'
3. Scroll down to '....'
4. See error

Expected behavior
purge goal should support also external DB.

Additional context
While the goal update-only used it works on the external DB.
Executing Maven: -B -f JOB-NAME/workspace/pom.xml -s /home/jenkins/.m2/settings.xml -U org.owasp:dependency-check-maven:update-only [INFO] Scanning for projects... [INFO] [INFO] ------------------< de.shgruppe.sccm-admin:owasp-pom >------------------ [INFO] Building SCCM-Admin owasp DB 1.0.0 [INFO] --------------------------------[ jar ]--------------------------------- [INFO] [INFO] --- dependency-check-maven:9.0.2:update-only (default-cli) @ owasp-pom --- [INFO] Instance is null, returning unconfigured instance [INFO] Setting default auxiliaries to "ODC" [INFO] setting defaultCompositeCacheAttributes to [ useLateral = true, useRemote = true, useDisk = true, maxObjs = 0, maxSpoolPerRun = -1, diskUsagePattern = UPDATE, spoolChunkSize = 2 ] [INFO] setting defaultElementAttributes to [ IS_LATERAL = false, IS_SPOOL = true, IS_REMOTE = false, IS_ETERNAL = false, MaxLifeSeconds = 86400, IdleTime = 1800, CreateTime = 1701774616804, LastAccessTime = 1701774616804, getTimeToLiveSeconds() = 86399, createTime = 1701774616804 ] [INFO] initialized MemoryCache for CENTRAL [INFO] Constructed cache with name [CENTRAL] and cache attributes [ useLateral = true, useRemote = true, useDisk = true, maxObjs = 0, maxSpoolPerRun = -1, diskUsagePattern = UPDATE, spoolChunkSize = 2 ] ...

[jeremylong]

The purge goal does not work with external databases. That should be more clearly documented.

[RobSHK]

In this case how should I solve the update-only errors?
such as:

[ERROR] Failed to process CVE-2022-42344
org.owasp.dependencycheck.analyzer.exception.UnexpectedAnalysisException: java.sql.SQLException: Invalid column type: 16
    at org.owasp.dependencycheck.data.nvdcve.CveDB.updateOrInsertVulnerability (CveDB.java:1058)
    at org.owasp.dependencycheck.data.nvdcve.CveDB.updateVulnerability (CveDB.java:866)
    at org.owasp.dependencycheck.data.update.nvd.api.NvdApiProcessor.call (NvdApiProcessor.java:87)
    at org.owasp.dependencycheck.data.update.nvd.api.NvdApiProcessor.call (NvdApiProcessor.java:33)
    at java.util.concurrent.FutureTask.run (FutureTask.java:264)
    at java.util.concurrent.ThreadPoolExecutor.runWorker (ThreadPoolExecutor.java:1128)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run (ThreadPoolExecutor.java:628)
    at java.lang.Thread.run (Thread.java:829)
Caused by: java.sql.SQLException: Invalid column type: 16
    at oracle.jdbc.driver.OracleStatement.getInternalType (OracleStatement.java:4486)
    at oracle.jdbc.driver.OraclePreparedStatement.setNullCritical (OraclePreparedStatement.java:4390)
    at oracle.jdbc.driver.OraclePreparedStatement.setNull (OraclePreparedStatement.java:4374)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.setNull (OraclePreparedStatementWrapper.java:937)
    at org.apache.commons.dbcp2.DelegatingPreparedStatement.setNull (DelegatingPreparedStatement.java:521)
    at org.apache.commons.dbcp2.DelegatingPreparedStatement.setNull (DelegatingPreparedStatement.java:521)
    at org.owasp.dependencycheck.data.nvdcve.CveDB.updateOrInsertVulnerability (CveDB.java:981)
    at org.owasp.dependencycheck.data.nvdcve.CveDB.updateVulnerability (CveDB.java:866)
    at org.owasp.dependencycheck.data.update.nvd.api.NvdApiProcessor.call (NvdApiProcessor.java:87)
    at org.owasp.dependencycheck.data.update.nvd.api.NvdApiProcessor.call (NvdApiProcessor.java:33)
    at java.util.concurrent.FutureTask.run (FutureTask.java:264)
    at java.util.concurrent.ThreadPoolExecutor.runWorker (ThreadPoolExecutor.java:1128)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run (ThreadPoolExecutor.java:628)
    at java.lang.Thread.run (Thread.java:829)

[jeremylong]

Recreate the database per https://jeremylong.github.io/DependencyCheck/data/database.html ?

[RobSHK]

@jeremylong now I'm realy confused, you suggested not to recreate the database #6116 (comment)

[aikebah]

From the error message it appears like Oracle does not support setting a Boolean columns to NULL (16 is java.sql.Types.BOOLEAN) or the column in the oracle db schema is not a boolean type where it is boolean for h2

[jeremylong]

@aikebah I blame you: #5978 (comment)

kidding I don't have an oracle db handy to test with and I'm a bit time constrained atm.

[aikebah]

:D can't promise anything, but will try to take a look into the code tomorrow to try and uncover which field the stacktrace is complaining about and why that would be... need to see if I still have a docker image with an oracle DB at hand... otherwise it'll take some time to rebuild such a multi-gigabyte beast...
You guessed perfectly right on how I was able to quickly digest from the stacktrace what the likely root of the issue is :D

[aikebah]

@jeremylong one solution would be to only support Oracle 23c and above for a central database... as that has finally introduced support for the SQL Boolean datatype

https://oracle-base.com/articles/23c/boolean-data-type-23c

the issue is rooted in the fact that the Booleans are stored as NUMBER(1) in the Oracle schema due to lack of Boolean support in the database.

[aikebah]

MS SQL Server is likely to suffer from the same issue as the datatype used for booleans in that schema (and the stored procedure update_vulnerability) is BIT. So unless the mssql jdbc driver ignores the type on setNull (like h2 does) it will likely also signal a type mismatch between the Types.BOOLEAN specified and the Types.BIT derived from the prepared statement)

H2, mysql and postgress have support for the Boolean datatype, which is also used in their schema definitions

[SMUnlimited]

As oracle wasn't working, just to help confirm the above supported db's, switched to postgres 15.5 and that does work fine.

[RobSHK]

As oracle wasn't working, just to help confirm the above supported db's, switched to postgres 15.5 and that does work fine.

@SMUnlimited, Which Oracle version have you tried on?

[aikebah]

@RobSHK all version of Oracle are affected... there is a type-mismatch between the stored-procedure argument (NUMBER(1)) and the type (java.sql.Types.BOOLEAN) argument of the setNull for boolean fields in Oracle. Same might apply for MS SQL depending on whether or not the Microsoft jdbc driver actually performs a type-check on the PreparedStatemen.setNull (and whether or not it would in that case also accept java.sql.Types.BOOLEAN for an argument whose actual type is java.sql.Types.BIT).

Oracle JDBC driver does perform the type-check and does not accept a Boolean-typed NULL for a Number(1) stored procedure variable.

[RobSHK]

@aikebah I understand, but you suggested that Oracle 23c should be a solution, I wondered if I should upgrade our Oracle DB to this version as theother DB such as mysql and postgress are not supported in our Org, H2 is also not recommended as a Prod DB.
I'm not sure how to proceed.

[SMUnlimited]

As oracle wasn't working, just to help confirm the above supported db's, switched to postgres 15.5 and that does work fine.

@SMUnlimited, Which Oracle version have you tried on?

@RobSHK v19

[jeremylong]

@aikebah I'm good with only supporting Oracle 23c and above. We would likely just need to update the schema correct?

alterntively, we could just wrap an isOracle check on the offending lines:

if (isOracle) {
  callUpdate.setNull(7, java.sql.Types.BIT);
  callUpdate.setNull(8, java.sql.Types.BIT);
  callUpdate.setNull(9, java.sql.Types.BIT);
  callUpdate.setNull(10, java.sql.Types.BIT);
} else {
  callUpdate.setNull(7, java.sql.Types.BOOLEAN);
  callUpdate.setNull(8, java.sql.Types.BOOLEAN);
  callUpdate.setNull(9, java.sql.Types.BOOLEAN);
  callUpdate.setNull(10, java.sql.Types.BOOLEAN);
}

[aikebah]

Looks like BIT should work for Oracle indeed (it's one of the datatypes for a NUMBER column).
As indicated most likely we'll need the same for MS SQL server (where the database datatype is BIT)

Unfortunately the Oracle container build scripts fail to run for me (podman/buildah), otherwise I could test if any of the numeric types would still give errors, but the most fitting numeric datatype candidate is indeed BIT.

I think your alternate approach would be more enterprise-friendly for the Oracle case (as I suspect many large enterprises are still on an older version of Oracle, as far as I know mine is still at 19)

An additional benefit of the BIT type is that we can reuse the same for MS SQL-server, which is likely to trigger similar issues (when their jdbc driver validates the given target field-type of the setNull), though none have been reported yet.

So the conditional should check for both oracle and mssql (not sure whether we already have a boolean field for detection of the mssql case)

[jeremylong]

we do not have a check for MS SQL server yet - that would need to be added.