search

jeremylong / DependencyCheck

OWASP dependency-check is a software composition analysis utility that detects publicly disclosed vulnerabilities in application dependencies.
https://owasp.org/www-project-dependency-check/
Apache License 2.0
6.13k stars 1.23k forks source link

9.0.2 with external Oracle DB fails #6227

Open RobSHK opened 7 months ago

RobSHK commented 7 months ago

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 `

oracle.jdbc.OracleDriver 
                <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 commented 7 months ago

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

RobSHK commented 7 months ago

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 commented 7 months ago

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

RobSHK commented 7 months ago

@jeremylong now I'm realy confused, you suggested not to recreate the database https://github.com/jeremylong/DependencyCheck/issues/6116#issuecomment-1829571097

aikebah commented 7 months ago

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 commented 7 months ago

@aikebah I blame you: https://github.com/jeremylong/DependencyCheck/pull/5978#discussion_r1385746530

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

aikebah commented 7 months ago

: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 commented 7 months ago

@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 commented 7 months ago

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 commented 7 months ago

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 commented 7 months ago

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 commented 7 months ago

@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 commented 7 months ago

@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 commented 7 months ago

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 commented 7 months ago

@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 commented 7 months ago

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 commented 7 months ago

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