Current Behavior After updating to 4.11, the new global audit view

<a class="user-mention notranslate" data-hovercard-type="user" data-hovercard-url="/us

Global vulnerability audit view broken for MSSQL about dependency-track HOT 14 CLOSED

schnieders commented on June 23, 2024

Global vulnerability audit view broken for MSSQL

from dependency-track.

Comments (14)

rbt-mm commented on June 23, 2024 1

I'm wondering if we need DISTINCT at all here. The query doesn't look like it would produce duplicate rows, as it operates on the concept of findings (Component <-> Vulnerability pairs). Am I missing something?

AFAIK DISTINCT was necessary because the query could otherwise produce duplicate rows if ACL is enabled.
Without it if a user is a part of multiple teams and two of those different teams include the same project, the query would produce a duplicate entry for both project teams.

Maybe there's a fix to this problem that does not include DISTINCT in the query, but unfortunately I'm not the biggest SQL expert 😅.

from dependency-track.

rbt-mm commented on June 23, 2024 1

I currently don't have MSSQL installed so I can't try to reproduce the issue but I'm 100% sure that I successfully tested this with MSSQL 2022 when I created the PR.

from dependency-track.

nscuro commented on June 23, 2024 1

I'll try to reproduce later today.

from dependency-track.

rkg-mm commented on June 23, 2024

Seems like DTrack uses TEXT datatype, but MSSQL doesn't support DISTINCT on it.
According to https://stackoverflow.com/questions/8056339/ef-the-text-data-type-cannot-be-selected-as-distinct-because-it-is-not-comparab
the question should be: Why do we use TEXT as datatype instead VARCHAR(MAX)?

from dependency-track.

nscuro commented on June 23, 2024

@rkg-mm Limitation of the ORM we use. It maps the CLOB JDBC type (required for "string of unlimited size") to TEXT for SQL Server: https://github.com/datanucleus/datanucleus-rdbms/blob/ae929d4a97961ebc53a21a4d0bf4c845ebdf4705/src/main/java/org/datanucleus/store/rdbms/adapter/SQLServerAdapter.java#L173-L175

It's not a decision we actively made to choose TEXT over VARCHAR(MAX)...

from dependency-track.

rkg-mm commented on June 23, 2024

Which fields would be affected by this? Since you drop support for MSSQL anyway with 5.x, maybe as quick fix those fields can be excluded...

from dependency-track.

nscuro commented on June 23, 2024

Based on the query in the exception, it seems to be these:

dependency-track/src/main/java/org/dependencytrack/model/Vulnerability.java

Lines 175 to 179 in cf4f2d4

 @Persistent 

 @Column(name = "DESCRIPTION", jdbcType = "CLOB") 

 @JsonDeserialize(using = TrimmedStringDeserializer.class) 

 @Pattern(regexp = RegexSequence.Definition.PRINTABLE_CHARS_PLUS, message = "The description may only contain printable characters") 

 private String description;

dependency-track/src/main/java/org/dependencytrack/model/Vulnerability.java

Lines 187 to 191 in cf4f2d4

 @Persistent 

 @Column(name = "RECOMMENDATION", jdbcType = "CLOB") 

 @JsonDeserialize(using = TrimmedStringDeserializer.class) 

 @Pattern(regexp = RegexSequence.Definition.PRINTABLE_CHARS_PLUS, message = "The recommendation may only contain printable characters") 

 private String recommendation;

from dependency-track.

rkg-mm commented on June 23, 2024

For the moment those are not used in the UI, but could be in future once we add expanding of those table rows.

Could also cast these 2 in the query
CAST(field AS VARCHAR(MAX))
Just not sure how that impacts performance of the query.

from dependency-track.

nscuro commented on June 23, 2024

I'm wondering if we need DISTINCT at all here. The query doesn't look like it would produce duplicate rows, as it operates on the concept of findings (Component <-> Vulnerability pairs). Am I missing something?

CAST(field AS VARCHAR(MAX))

I'd expect this to slow down the query, but not sure by how much. Also this would need to be a specific cast for SQL Server, since the same will not work in H2, MySQL, or PostgreSQL.

from dependency-track.

nscuro commented on June 23, 2024

Perhaps there is a way to solve this in a "two-stage" CTE, where we:

Select only the unique IDs of all applicable findings (this can use DISTINCT)
Based on the CTE holding the IDs, fetch the actual fields we want to display (doesn't need DISTINCT anymore)

Separately, are you seeing the same issue in your instance @rbt-mm @rkg-mm?

I'm 99% sure I tested this feature with the MSSQL Compose setup, so I am a bit baffled why it may have worked before but then somehow broke. Does the MSSQL version play a role? The Compose setup uses the 2022 version.

from dependency-track.

rkg-mm commented on June 23, 2024

I haven't found the time to update our instance yet. But I also think @rbt-mm tested this with MSSQL...

from dependency-track.

schnieders commented on June 23, 2024

Thank you for your immediate reactions. I would like to share some additional logs with you, which shows the database migration done by DT API server. Maybe it will help you.

2024-05-08 08:18:45,250 INFO [RequirementsVerifier] Initializing requirements verifier
2024-05-08 08:18:45,251 INFO [UpgradeInitializer] Initializing upgrade framework
2024-05-08 08:18:48,009 INFO [UpgradeExecutor] Upgrade class org.dependencytrack.upgrade.v4110.v4110Updater about to run.
2024-05-08 08:18:48,009 INFO [v4110Updater] Dropping foreign key constraint from "VULNERABILITY"."CWE"
2024-05-08 08:18:48,011 INFO [v4110Updater] Dropping index "VULNERABILITY"."VULNERABILITY_CWE_IDX"
2024-05-08 08:18:48,011 INFO [v4110Updater] Dropping column "VULNERABILITY"."CWE"
2024-05-08 08:18:48,013 INFO [v4110Updater] Dropping table "CWE"
2024-05-08 08:18:48,020 INFO [v4110Updater] Computing severities for vulnerabilities where severity is currently NULL
2024-05-08 08:18:48,778 INFO [v4110Updater] Updated 12753 vulnerabilities in 26 batches
2024-05-08 08:18:48,780 INFO [v4110Updater] Extending length of PURL and PURLCOORDINATES columns from 255 to 786
2024-05-08 08:18:48,806 INFO [UpgradeExecutor] Completed running upgrade class org.dependencytrack.upgrade.v4110.v4110Updater in 778 ms.
2024-05-08 08:18:48,809 INFO [PersistenceManagerFactory] Initializing persistence framework
2024-05-08 08:18:48,810 INFO [PersistenceManagerFactory] Creating transactional connection pool
2024-05-08 08:18:48,844 INFO [PersistenceManagerFactory] Creating non-transactional connection pool
2024-05-08 08:18:49,844 INFO [HealthCheckInitializer] Registering health checks
2024-05-08 08:18:49,846 INFO [DefaultObjectGenerator] Initializing default object generator
2024-05-08 08:18:49,847 INFO [DefaultObjectGenerator] Synchronizing permissions to datastore
2024-05-08 08:18:50,036 INFO [DefaultObjectGenerator] Synchronizing SPDX license definitions to datastore
2024-05-08 08:18:57,408 INFO [DefaultObjectGenerator] Synchronizing default repositories to datastore
2024-05-08 08:18:57,462 INFO [DefaultObjectGenerator] Synchronizing config properties to datastore
2024-05-08 08:18:57,685 INFO [DefaultObjectGenerator] Synchronizing notification publishers to datastore
2024-05-08 08:18:58,115 INFO [EventSubsystemInitializer] Initializing asynchronous event subsystem
2024-05-08 08:18:58,152 INFO [NotificationSubsystemInitializer] Initializing notification service
2024-05-08 08:18:58,154 INFO [IndexSubsystemInitializer] Building lucene indexes if required
2024-05-08 08:18:58,165 INFO [IndexManager] Checking the health of index PROJECT
2024-05-08 08:18:58,331 INFO [IndexManager] The index PROJECT is healthy
2024-05-08 08:18:58,332 INFO [IndexManager] Checking the health of index COMPONENT
2024-05-08 08:18:58,917 INFO [IndexManager] The index COMPONENT is healthy
2024-05-08 08:18:58,918 INFO [IndexManager] Checking the health of index SERVICECOMPONENT
2024-05-08 08:18:58,919 INFO [IndexManager] The index SERVICECOMPONENT is healthy
2024-05-08 08:18:58,919 INFO [IndexManager] Checking the health of index VULNERABILITY
2024-05-08 08:19:04,172 INFO [IndexManager] The index VULNERABILITY is healthy
2024-05-08 08:19:04,173 INFO [IndexManager] Checking the health of index LICENSE
2024-05-08 08:19:04,179 INFO [IndexManager] The index LICENSE is healthy
2024-05-08 08:19:04,179 INFO [IndexManager] Checking the health of index VULNERABLESOFTWARE
2024-05-08 08:19:05,575 INFO [IndexManager] The index VULNERABLESOFTWARE is healthy
2024-05-08 08:19:05,590 INFO [AlpineServlet] Starting Dependency-Track
2024-05-08 08:19:07,914 INFO [AlpineServlet] Dependency-Track is ready

There are no error messages beside this issue.

from dependency-track.

nscuro commented on June 23, 2024

Confirmed to be broken for MSSQL.

from dependency-track.

nscuro commented on June 23, 2024

Working around this now by using a subquery instead of a LEFT JOIN to perform the ACL check: #3700.

from dependency-track.

Global vulnerability audit view broken for MSSQL about dependency-track HOT 14 CLOSED

Comments (14)

Related Issues (20)

Recommend Projects

React

Vue.js

Typescript

TensorFlow

Django

Laravel

D3

Recommend Topics

javascript

web

server

Machine learning

Visualization

Game

Recommend Org

Facebook

Microsoft

Google

Alibaba

D3

Tencent

	@Persistent
	@Column(name = "DESCRIPTION", jdbcType = "CLOB")
	@JsonDeserialize(using = TrimmedStringDeserializer.class)
	@Pattern(regexp = RegexSequence.Definition.PRINTABLE_CHARS_PLUS, message = "The description may only contain printable characters")
	private String description;

	@Persistent
	@Column(name = "RECOMMENDATION", jdbcType = "CLOB")
	@JsonDeserialize(using = TrimmedStringDeserializer.class)
	@Pattern(regexp = RegexSequence.Definition.PRINTABLE_CHARS_PLUS, message = "The recommendation may only contain printable characters")
	private String recommendation;