Warning
The instructions below are for upgrading to version 2.8.5.1, not 2.8.5. If you downloaded the 2.8.5 artifact prior to January 7, 2022 (the existing ThreadFix-2.8.5.zip file now contains the 2.8.5.1 artifact), please re-download it and proceed with the instructions below. Contact support@threadfix.it if you need the download link or have questions.

📙 You will learn

How to upgrade from 2.8.3 to 2.8.5.1

Prerequisites

Audience: IT Professional and/or End User
Difficulty: Advanced
Time needed: Approximately 60 minutes
Tools required: Tomcat, MySQL or MS SQL Server

Note

ALWAYS PERFORM A FULL BACKUP OF YOUR DATABASE BEFORE ATTEMPTING ANY UPGRADE!

MySQL Server: You can run the mysqldump command from the MySQL server, e.g., mysqldump -u mydbuser -p mydatabase > path/to/backup.sql (we do not recommend using MySQL Workbench to perform the backup).

MS SQL Server: Using SSMS, right-click on the database, select Tasks → Back Up…, then choose the location & name of the backup file and click OK.

If you have any questions or concerns or if you wish to upgrade from an older version of ThreadFix, please reach out to our support team here: support@threadfix.it

ThreadFix Deployment Update

Follow these steps to deploy the updated version of ThreadFix:

Stop the Tomcat instance on which ThreadFix is running.
Move your current ThreadFix deployment directory from your Tomcat webapps folder into a backup directory.
1. webapps directory location: //TOMCAT_HOME/webapps
Copy the updated ThreadFix folder from your new artifact into your webapps directory.
1. webapps directory location: //TOMCAT_HOME/webapps
Copy the following files from your backed up ThreadFix directory to the newly updated ThreadFix directory:
(FROM: <previous-threadfix-deploy>/WEB-INF/classes/, TO: <new-threadfix-deploy>/WEB-INF/classes/, except as noted below)
1. custom.properties
2. ESAPI.properties (See REQUIRED update below)
  1. Be sure that Log4jfactory is no longer referenced as the ESAPI.Logger in your ESAPI.properties.
  2. Replace with ESAPI.Logger=com.denimgroup.threadfix.logging.esapi.slf4j.CustomESAPISlf4jLogFactory in your upgraded ThreadFix 2.8.5.1 instance.
3. jdbc.properties
4. jms.properties
5. threadfix.license
6. If you've updated any of these in your previous deployment, copy them as well:
  1. <threadfix-deploy>/WEB-INF/security.xml (See REQUIRED update below)
    - Replace bean definition in security.xml file:
      <bean id="velocityEngine" class="org.springframework.security.saml.util.VelocityFactory" factory-method="getEngine"/>
    - With:
      <bean id="velocityEngine" class="com.denimgroup.threadfix.service.saml.CustomSamlVelocityFactory" factory-method="getEngine"/>
  2. <threadfix-deploy>/WEB-INF/classes/security/samlKeystore.jks (and/or any other Java keystore that you've saved in this directory)
  3. Note: Do not copy log4j.xml. As of version 2.8.5.1, log4j.xml was replaced with logback.xml, so there’s no need to copy log4j.xml from your previous artifact.
    - Any changes that you had made in log4j.xml will need to be made in logback.xml if you want them to persist.
Copy the velocityTemplates directory from your backed up ThreadFix directory to the newly-updated ThreadFix directory.
(FROM: <previous-threadfix-deploy> TO:<new-threadfix-deploy>)
In Linux environments, you may need to grant your tomcat user/group ownership and permissions to the newly-deployed artifact.
Examples:
Code Block
sudo chown -R tomcat:tomcat /opt/tomcat sudo chmod -R 775 /opt/tomcat
Cleanup - Delete the following:
1. The contents of the <tomcat-deploy>/work directory
2. Your web browser's cache/history

Before-Restart Database Updates

Database Updates

Note

Do Not Restart Tomcat If Scripts Do Not Complete

If you receive an error when running before-restart database update scripts, please report the issue to Denim Group (create a ticket in Service Desk), including a screenshot of the script & error message.

You should NOT proceed to start Tomcat until the issue is resolved. If you do, Hibernate may make unrecoverable changes that will require you to restore your database from backup.

Caution/warning messages are OK; just not errors.

Expand the MySQL or MS SQL Server code block to reveal the scripts:

Expand

title	MySQL:

Code Block

language	sql

DELETE FROM ScanCloseVulnerabilityMap
WHERE scanId IS NULL AND vulnerabilityId IS NULL;
 
DROP PROCEDURE IF EXISTS delete_duplicate_scan_close_maps;
DELIMITER $$
 
CREATE PROCEDURE delete_duplicate_scan_close_maps()
BEGIN
    DECLARE mappedVulnId INT;
    DECLARE mappedScanId INT;
    DECLARE mappedCount INT;
 
    DECLARE deleteCount INT DEFAULT 0;
    DECLARE deleteId INT;
 
    DECLARE finished INT DEFAULT FALSE;
    DECLARE scvm_count CURSOR FOR SELECT scvm.vulnerabilityId, scvm.scanId, COUNT(*) AS count
        FROM ScanCloseVulnerabilityMap scvm
        GROUP BY scvm.vulnerabilityId, scvm.scanId
        HAVING count > 1;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = TRUE;
 
    OPEN scvm_count;
 
    duplicate_loop : LOOP
        FETCH scvm_count INTO mappedVulnId, mappedScanId, mappedCount;
        IF(finished) THEN
            LEAVE duplicate_loop;
        END IF;
 
        SET deleteCount = 0;
        delete_loop : LOOP
            IF(deleteCount = mappedCount - 1) THEN
                LEAVE delete_loop;
            END IF;
 
            SELECT id INTO deleteId FROM ScanCloseVulnerabilityMap WHERE vulnerabilityId = mappedVulnId AND scanId = mappedScanId ORDER BY id DESC LIMIT 1;
            DELETE FROM ScanCloseVulnerabilityMap WHERE id = deleteId;
            SET deleteCount = deleteCount + 1;
 
        END LOOP delete_loop;
 
    END LOOP duplicate_loop;
 
    close scvm_count;
END$$
DELIMITER ;
CALL delete_duplicate_scan_close_maps();
DROP PROCEDURE IF EXISTS delete_duplicate_scan_close_maps;
 
DROP PROCEDURE IF EXISTS DUP_METADATA_KEY_CHECK;
DELIMITER $$
CREATE PROCEDURE DUP_METADATA_KEY_CHECK()
BEGIN
    DECLARE metadataKeyCount INT;
    SET metadataKeyCount = 0;
    
    SELECT COUNT(id) INTO metadataKeyCount
    FROM MetadataKey
    WHERE
        name IN ('Static Analysis Date', 'Dynamic Analysis Date', 'Dependency Analysis Date', 'Mobile Analysis Date')
        AND `type` = 0
    ;
              
    IF (metadataKeyCount > 0)
        THEN
            SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Metadata key conflicts found, please contact ThreadFix support before proceeding.';
    END IF;
          
END$$
DELIMITER ;
 
CALL DUP_METADATA_KEY_CHECK;
 
UPDATE Finding
SET nativeId = CONCAT(nativeId, '-QWlegacy')
WHERE scanId IN (SELECT s.id FROM Scan s
    JOIN ApplicationChannel ac ON ac.id = s.applicationChannelId
    JOIN ChannelType ct ON ct.id = ac.channelTypeId
    WHERE ct.name = 'QualysGuard WAS');
 
ALTER TABLE User MODIFY COLUMN department VARCHAR(128);
 
UPDATE RemoteProviderAuthenticationField rpaf JOIN RemoteProviderType rpt ON (rpt.id = rpaf.applicationId) set rpaf.applicationId = null WHERE rpt.name='Veracode' AND rpaf.name IN ('Auth Type','Username','Password');
 
DELETE FROM RemoteProviderAuthenticationEntity WHERE remoteProviderAuthenticationFieldId IN (SELECT id FROM RemoteProviderAuthenticationField WHERE applicationId IS NULL);
 
DELETE FROM RemoteProviderAuthenticationFieldOptionFilter WHERE remoteProviderAuthenticationFieldId IN (SELECT id FROM RemoteProviderAuthenticationField WHERE applicationId IS NULL);
 
UPDATE SelectOption set tip=null, value=null WHERE id IN (SELECT selectOptions_id FROM RemoteProviderAuthenticationField_SelectOption_Join WHERE remoteProviderAuthenticationField_id IN (SELECT id FROM RemoteProviderAuthenticationField WHERE applicationId IS NULL));
 
DELETE FROM RemoteProviderAuthenticationField_SelectOption_Join WHERE remoteProviderAuthenticationField_id IN (SELECT id FROM RemoteProviderAuthenticationField WHERE applicationId IS NULL);
 
DELETE FROM RemoteProviderAuthenticationField WHERE applicationId IS NULL;
 
DELETE FROM RemoteProviderAuthenticationFieldOptionFilter WHERE selectOptionId IN (SELECT id FROM SelectOption WHERE tip IS NULL AND value IS NULL);
 
DELETE FROM SelectOption WHERE tip IS NULL AND value IS NULL;
 
ALTER TABLE ApplicationChannel ADD COLUMN isStale bit(1);
UPDATE ApplicationChannel SET isStale = 0;
 
ALTER TABLE MetadataKey ADD COLUMN editable BIT(1);
UPDATE MetadataKey SET editable = TRUE;
 
UPDATE SeverityMap SET genericSeverityId = (SELECT id FROM GenericSeverity WHERE intValue=4)
WHERE channelSeverityId = (SELECT id FROM ChannelSeverity WHERE channelTypeId=(SELECT id FROM ChannelType WHERE name = 'Nessus') AND numericValue=4);
 
UPDATE SeverityMap SET genericSeverityId = (SELECT id FROM GenericSeverity WHERE intValue=4)
WHERE channelSeverityId = (SELECT id FROM ChannelSeverity WHERE channelTypeId=(SELECT id FROM ChannelType WHERE name = 'Nessus') AND numericValue=4);
  
DROP PROCEDURE IF EXISTS createEventVulnIdIndex;
DELIMITER $$
  
CREATE PROCEDURE createEventVulnIdIndex()
BEGIN
    IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_NAME='Event' and INDEX_NAME='idx_event_vuln') THEN
        CREATE INDEX idx_event_vuln ON `Event` (vulnerabilityId);
    END IF;
END $$
DELIMITER ;
  
CALL createEventVulnIdIndex();
  
DROP PROCEDURE createEventVulnIdIndex;
  
ALTER TABLE Organization
ADD CONSTRAINT unqOrgName UNIQUE (name);
  
ALTER TABLE Application
ADD CONSTRAINT unqAppNameOrgId UNIQUE (name, organizationId);

Expand

title	MS SQL Server:

Code Block

language	sql

DELETE FROM ScanCloseVulnerabilityMap
WHERE scanId IS NULL AND vulnerabilityId IS NULL;
GO
 
IF OBJECT_ID('delete_duplicate_scan_close_maps','P') IS NOT NULL
    DROP PROCEDURE delete_duplicate_scan_close_maps
GO
 
CREATE PROCEDURE delete_duplicate_scan_close_maps AS
BEGIN
    DECLARE @mappedVulnId INT;
    DECLARE @mappedScanId INT;
    DECLARE @mappedCount INT;
 
    DECLARE @deleteCount INT = 0;
    DECLARE @deleteId INT;
 
    DECLARE scvm_count CURSOR FOR SELECT scvm.vulnerabilityId, scvm.scanId, COUNT(*)
        FROM ScanCloseVulnerabilityMap scvm
        GROUP BY scvm.vulnerabilityId, scvm.scanId
        HAVING COUNT(*) > 1;
 
    OPEN scvm_count;
    FETCH NEXT FROM scvm_count INTO @mappedVulnId, @mappedScanId, @mappedCount;
 
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @deleteCount = 0;
 
        WHILE (@deleteCount < @mappedCount - 1)
        BEGIN
            SELECT TOP 1 @deleteId = id FROM ScanCloseVulnerabilityMap WHERE vulnerabilityId = @mappedVulnId AND scanId = @mappedScanId ORDER BY id DESC;
            DELETE FROM ScanCloseVulnerabilityMap WHERE id = @deleteId;
            SET @deleteCount = @deleteCount + 1;
        END
 
        FETCH NEXT FROM scvm_count INTO @mappedVulnId, @mappedScanId, @mappedCount;
    END
close scvm_count;
END
GO
 
EXEC delete_duplicate_scan_close_maps;
GO
 
IF OBJECT_ID('delete_duplicate_scan_close_maps','P') IS NOT NULL
    DROP PROCEDURE delete_duplicate_scan_close_maps
GO
 
SET NOEXEC OFF
DECLARE @metadataKeyCount INT;
              
SELECT @metadataKeyCount = COUNT(id)
FROM MetadataKey
WHERE
    name IN ('Static Analysis Date', 'Dynamic Analysis Date', 'Dependency Analysis Date', 'Mobile Analysis Date')
    AND [type] = 0
;
                  
IF (@metadataKeyCount > 0)
BEGIN   
    print 'Metadata key conflicts found, please contact ThreadFix support before proceeding.'
    SET NOEXEC ON       
END
GO
 
UPDATE Finding
SET nativeId = CONCAT(nativeId, '-QWlegacy')
WHERE scanId IN (SELECT s.id FROM Scan s
    JOIN ApplicationChannel ac ON ac.id = s.applicationChannelId
    JOIN ChannelType ct ON ct.id = ac.channelTypeId
    WHERE ct.name = 'QualysGuard WAS');
GO
 
ALTER TABLE [User] ALTER COLUMN department VARCHAR(128)
GO
 
UPDATE rpaf set rpaf.applicationId = null FROM RemoteProviderAuthenticationField rpaf INNER JOIN RemoteProviderType rpt ON (rpt.id = rpaf.applicationId) WHERE rpt.name='Veracode' AND rpaf.name IN ('Auth Type','Username','Password')
GO
 
DELETE FROM RemoteProviderAuthenticationEntity WHERE remoteProviderAuthenticationFieldId IN (SELECT id FROM RemoteProviderAuthenticationField WHERE applicationId IS NULL)
GO
 
DELETE FROM RemoteProviderAuthenticationFieldOptionFilter WHERE remoteProviderAuthenticationFieldId IN (SELECT id FROM RemoteProviderAuthenticationField WHERE applicationId IS NULL)
GO
 
UPDATE SelectOption set tip=null, value=null WHERE id IN (SELECT selectOptions_id FROM RemoteProviderAuthenticationField_SelectOption_Join WHERE remoteProviderAuthenticationField_id IN (SELECT id FROM RemoteProviderAuthenticationField WHERE applicationId IS NULL))
GO
 
DELETE FROM RemoteProviderAuthenticationField_SelectOption_Join WHERE remoteProviderAuthenticationField_id IN (SELECT id FROM RemoteProviderAuthenticationField WHERE applicationId IS NULL)
GO
 
DELETE FROM RemoteProviderAuthenticationField WHERE applicationId IS NULL
GO
 
DELETE FROM RemoteProviderAuthenticationFieldOptionFilter WHERE selectOptionId IN (SELECT id FROM SelectOption WHERE tip IS NULL AND value IS NULL)
GO
 
DELETE FROM SelectOption WHERE tip IS NULL AND value IS NULL
GO
 
ALTER TABLE ApplicationChannel ADD [isStale] bit;
GO
UPDATE ApplicationChannel set [isStale] = 0;
GO
 
ALTER TABLE MetadataKey ADD editable BIT;
GO
UPDATE MetadataKey SET editable = 1;
GO
 
UPDATE SeverityMap SET genericSeverityId = (SELECT id FROM GenericSeverity WHERE intValue=4)
WHERE channelSeverityId = (SELECT id FROM ChannelSeverity WHERE channelTypeId=(SELECT id FROM ChannelType WHERE name = 'Nessus') AND numericValue=4);
GO
 
UPDATE SeverityMap SET genericSeverityId = (SELECT id FROM GenericSeverity WHERE intValue=4)
WHERE channelSeverityId = (SELECT id FROM ChannelSeverity WHERE channelTypeId=(SELECT id FROM ChannelType WHERE name = 'Nessus') AND numericValue=4);
GO
  
CREATE PROCEDURE createEventVulnIdIndex AS
BEGIN
IF NOT EXISTS(SELECT * FROM sys.indexes WHERE OBJECT_ID = OBJECT_ID('Event') AND NAME = 'idx_event_vuln')
CREATE INDEX idx_event_vuln ON Event (vulnerabilityId);
END
GO
  
exec createEventVulnIdIndex;
GO
  
DROP PROCEDURE createEventVulnIdIndex;
GO
  
ALTER TABLE Organization
ADD CONSTRAINT unqOrgName UNIQUE (name);
GO
  
ALTER TABLE Application
ADD CONSTRAINT unqAppNameOrgId UNIQUE (name, organizationId);
GO

Tip

For large scripts, it may be most efficient to save them to a .sql file and either drag & drop them into your UI-based database manager (e.g., SSMS or MySQL Workbench), or if using a CLI to access your MySQL server, you can use the following example command to execute the script:
mysql -u [username] -p [dbname] < [updatescript.sql] -v -v

Start Tomcat/ThreadFix

Restart Tomcat/ThreadFix.
Navigate to the login page to ensure that it loads as expected, no need to log in at this time; wait until after completing the next section.

Note

When launching the new deployment for the first time after deploying the update, ThreadFix may take a few minutes to make necessary schema updates, so there may be a delay in accessing the login page.

Monitor the <tomcat-deploy>/logs/threadfix.log file to determine when it has finished...look for the second instance of the following event: Finished updating Enterprise Tags.

Please do not interrupt this process by stopping Tomcat.

After-Restart Database Updates

Note

REQUIRED - Database User Account Permissions

Ensure that the database user account configured in your jdbc.properties for ThreadFix has appropriate permissions to:

ALTER and CREATE database tables and columns
CREATE and RUN stored procedures

Between versions, ThreadFix may need updates to your schema, and most of those will be handled seamlessly by Hibernate when you first startup the new version. For the remainder, explicit update scripts are detailed below.

Info

Restart Tomcat Before Running Database Update Scripts

Only run the following scripts after you have successfully loaded the ThreadFix login page. If it fails to load, check the catalina and threadfix log files.

For troubleshooting assistance, email support@threadfix.it and provide these log files.

Database Updates

Expand

title	MySQL:

Code Block

language	sql

DROP PROCEDURE IF EXISTS deleteDataFlowElement;
 
DELIMITER $$
CREATE PROCEDURE deleteDataFlowElement(IN findingIdToDelete INT(11))
BEGIN
    DELETE FROM DataFlowElement WHERE findingId = findingIdToDelete;
END$$
DELIMITER ;
 
CREATE TABLE TempTable SELECT f.nativeId AS findingNativeId,rpa.nativeId AS rpaNativeId,f.vulnerabilityId AS findingVulnId,f.id AS findingId
FROM Finding f
INNER JOIN Scan s ON f.scanId = s.id
INNER JOIN RemoteProviderApplication rpa ON (s.remoteProviderEntityId=rpa.remoteProviderEntityId AND s.applicationChannelId = rpa.applicationChannel_id AND s.applicationId=rpa.applicationId)
WHERE f.nativeId LIKE '%-legacy';
 
DROP PROCEDURE IF EXISTS updateLegacyNativeIdVeracode;
DELIMITER $$
CREATE PROCEDURE updateLegacyNativeIdVeracode()
BEGIN
    DECLARE finished INTEGER DEFAULT 0;
    DECLARE findingNativeId varchar(256) DEFAULT "";
    DECLARE rpaNativeId varchar(1024) DEFAULT "";
    DECLARE findingVulnId INT(11) DEFAULT -1;
    DECLARE findingId INT(11) DEFAULT -1;
    DECLARE findingToDelete INT(11) DEFAULT -1;
    DECLARE correctNativeId varchar(256) DEFAULT "";
    DECLARE findingActive BIT(1) DEFAULT 1;
 
    DECLARE cursorNativeId
        CURSOR FOR
            SELECT * FROM TempTable;
 
    DECLARE CONTINUE HANDLER
        FOR NOT FOUND SET finished = 1;
 
    OPEN cursorNativeId;
 
    getNext: LOOP
        FETCH cursorNativeId INTO findingNativeId, rpaNativeId, findingVulnId, findingId;
        IF finished = 1 THEN
            LEAVE getNext;
        END IF;
         
        SET correctNativeId = concat(substring_index(findingNativeId,'-',1),'-',rpaNativeId);
        IF exists (SELECT id FROM Finding WHERE vulnerabilityId = findingVulnId AND nativeId = correctNativeId) THEN
            SELECT id,active INTO findingToDelete,findingActive FROM Finding WHERE nativeId = correctNativeId AND vulnerabilityId = findingVulnId AND id != findingId;
            CALL deleteDataFlowElement(findingToDelete);
            UPDATE Finding f1 SET f1.nativeId=correctNativeId , f1.active=findingActive WHERE f1.id = findingId;
            DELETE FROM finding WHERE id = findingToDelete;
        END IF;
    END LOOP getNext;
    commit;
    CLOSE cursorNativeId;
END$$
DELIMITER ;
 
CALL updateLegacyNativeIdVeracode();
DROP PROCEDURE updateLegacyNativeIdVeracode;
 
DROP TABLE TempTable;
CREATE TABLE TempTable
SELECT f.nativeId AS findingNativeId,rpa.nativeId AS rpaNativeId,f.vulnerabilityId AS findingVulnId,f.id AS findingId ,substr(substring_index(substr(f.rawFinding, position("SimilarityId" IN f.rawFinding),30),'\"',2),position('\"' IN substring_index(substr(f.rawFinding, position("SimilarityId" IN f.rawFinding)-1,30),'\"',2))) AS similarityId, substr(substring_index(substr(f.rawFinding, position("SimilarityId" IN f.rawFinding)-10,30),'\"',2),position('\"' IN substring_index(substr(f.rawFinding, position("SimilarityId" IN f.rawFinding)-11,30),'\"',2))) AS pathId
FROM Finding f
INNER JOIN Scan s ON f.scanId = s.id
INNER JOIN RemoteProviderApplication rpa ON (s.remoteProviderEntityId=rpa.remoteProviderEntityId AND s.applicationChannelId = rpa.applicationChannel_id AND s.applicationId=rpa.applicationId)
WHERE f.nativeId LIKE '%-CMlegacy';
 
DROP PROCEDURE IF EXISTS updateLegacyNativeIdCheckmarx;
DELIMITER $$
CREATE PROCEDURE updateLegacyNativeIdCheckmarx()
BEGIN
    DECLARE finished INTEGER DEFAULT 0;
    DECLARE findingNativeId varchar(256) DEFAULT "";
    DECLARE rpaNativeId varchar(1024) DEFAULT "";
    DECLARE findingVulnId INT(11) DEFAULT -1;
    DECLARE findingId INT(11) DEFAULT -1;
    DECLARE pathId varchar(256) DEFAULT "";
    DECLARE similarityId varchar(256) DEFAULT "";
    DECLARE findingIdToDelete INT(11) DEFAULT -1;
    DECLARE correctNativeId varchar(256) DEFAULT "";
    DECLARE findingActive BIT(1) DEFAULT 1;
     
    DECLARE cursorNativeId
        CURSOR FOR
            SELECT * FROM TempTable;
 
    DECLARE CONTINUE HANDLER
        FOR NOT FOUND SET finished = 1;
 
    OPEN cursorNativeId;
 
    getNext: LOOP
        FETCH cursorNativeId INTO findingNativeId, rpaNativeId, findingVulnId, findingId, similarityId, pathId;
        IF finished = 1 THEN
            LEAVE getNext;
        END IF;
         
        SET correctNativeId = md5(concat('SimilarityId: ', similarityId, ', PathId: ', pathId));
        IF exists (SELECT id FROM Finding WHERE vulnerabilityId = findingVulnId AND nativeId = correctNativeId) THEN
            SELECT id,active INTO findingIdToDelete,findingActive FROM finding WHERE nativeId = correctNativeId AND vulnerabilityId = findingVulnId AND id != findingId;
            CALL deleteDataFlowElement(findingIdToDelete);
            UPDATE Finding f1 SET f1.nativeId=correctNativeId , f1.active=findingActive WHERE f1.id = findingId;
            DELETE FROM finding WHERE id = findingIdToDelete;
        END IF;
         
    END LOOP getNext;
    commit;
    CLOSE cursorNativeId;
END$$
DELIMITER ;
 
CALL updateLegacyNativeIdCheckmarx();
DROP PROCEDURE updateLegacyNativeIdCheckmarx;
 
DROP TABLE TempTable;
CREATE TABLE TempTable SELECT f.nativeId AS findingNativeId,rpa.nativeId AS rpaNativeId,f.vulnerabilityId AS findingVulnId,f.id AS findingId, f.channelVulnerabilityId AS findingChannelId, f.surfaceLocationId AS findingSurfaceId
FROM Finding f
INNER JOIN Scan s ON f.scanId = s.id
INNER JOIN RemoteProviderApplication rpa ON (s.remoteProviderEntityId=rpa.remoteProviderEntityId AND s.applicationChannelId = rpa.applicationChannel_id AND s.applicationId=rpa.applicationId)
WHERE f.nativeId LIKE '%-QWlegacy';
 
DROP PROCEDURE IF EXISTS updateLegacyNativeIdQualys;
DELIMITER $$
CREATE PROCEDURE updateLegacyNativeIdQualys ()
BEGIN
    DECLARE finished INTEGER DEFAULT 0;
    DECLARE findingNativeId varchar(256) DEFAULT "";
    DECLARE rpaNativeId varchar(1024) DEFAULT "";
    DECLARE findingVulnId INT(11) DEFAULT -1;
    DECLARE findingId INT(11) DEFAULT -1;
    DECLARE findingToDelete INT(11) DEFAULT -1;
    DECLARE findingChannelId INT(11) DEFAULT -1;
    DECLARE findingSurfaceId INT(11) DEFAULT -1;
    DECLARE channelVulnName varchar(256) DEFAULT "";
    DECLARE url varchar(256) DEFAULT "";
    DECLARE parameter varchar(256) DEFAULT "";
    DECLARE correctNativeId varchar(256) DEFAULT "";
    DECLARE findingActive BIT(1) DEFAULT 1;
     
    DECLARE cursorNativeId
        CURSOR FOR
            select * from TempTable;
 
    DECLARE CONTINUE HANDLER
        FOR NOT FOUND SET finished = 1;
 
    OPEN cursorNativeId;
 
    getNext: LOOP
        FETCH cursorNativeId INTO findingNativeId, rpaNativeId, findingVulnId, findingId, findingChannelId, findingSurfaceId;
        IF finished = 1 THEN
            LEAVE getNext;
        END IF;
         
        SELECT cv.name INTO channelVulnName FROM Finding f INNER JOIN ChannelVulnerability cv ON f.channelVulnerabilityId = cv.id  WHERE f.id = findingId;
        SELECT concat(sl.protocol, '://', sl.host, ':', sl.port, sl.path) INTO url FROM Finding f INNER JOIN SurfaceLocation sl ON f.surfaceLocationId = sl.id WHERE f.id = findingId;
        SELECT sl.parameter INTO parameter FROM Finding f INNER JOIN SurfaceLocation sl ON (f.surfaceLocationId = sl.id) WHERE f.id = findingId;
         
        IF parameter IS NOT NULL THEN
            SET correctNativeId = md5(lower(concat(channelVulnName,url,parameter)));
        ELSE
            SET correctNativeId = md5(lower(concat(channelVulnName,url)));
        END IF;
         
        IF exists (SELECT id FROM Finding WHERE vulnerabilityId = findingVulnId AND nativeId = correctNativeId) THEN
            SELECT id,active INTO findingToDelete,findingActive FROM Finding WHERE nativeId = correctNativeId AND vulnerabilityId = findingVulnId AND id != findingId;
            CALL deleteDataFlowElement(findingToDelete);
            UPDATE Finding f1 SET f1.nativeId=correctNativeId , f1.active=findingActive WHERE f1.id = findingId;
            DELETE FROM finding WHERE id = findingToDelete;
        END IF;
         
    END LOOP getNext;
    commit;
    CLOSE cursorNativeId;
 
END$$
DELIMITER ;
 
CALL updateLegacyNativeIdQualys();
DROP PROCEDURE deleteDataFlowElement;
DROP PROCEDURE updateLegacyNativeIdQualys;
 
DROP TABLE TempTable;

Expand

title	MS SQL Server:

Code Block

language	sql

IF OBJECT_ID('deleteDataFlowElement','P') IS NOT NULL
DROP PROCEDURE deleteDataFlowElement
GO
 
CREATE PROCEDURE deleteDataFlowElement @findingIdToDelete INT
AS
    DELETE FROM DataFlowElement WHERE findingId = @findingIdToDELETE;
GO
 
SELECT f.nativeId AS findingNativeId,rpa.nativeId AS rpaNativeId,f.vulnerabilityId AS findingVulnId,f.id AS findingId into TempTable
FROM Finding f
INNER JOIN Scan s ON f.scanId = s.id
INNER JOIN RemoteProviderApplication rpa ON (s.remoteProviderEntityId=rpa.remoteProviderEntityId AND s.applicationChannelId = rpa.applicationChannel_id AND s.applicationId=rpa.applicationId)
WHERE f.nativeId LIKE '%-legacy';
 
IF OBJECT_ID('updateLegacyNativeIdVeracode','P') IS NOT NULL
DROP PROCEDURE updateLegacyNativeIdVeracode
GO
 
CREATE PROCEDURE updateLegacyNativeIdVeracode
AS
    DECLARE @findingNativeId varchar(256);
    DECLARE @rpaNativeId varchar(1024);
    DECLARE @findingVulnId INT;
    DECLARE @findingId INT;
    DECLARE @findingToDELETE INT;
    DECLARE @correctNativeId varchar(256);
    DECLARE @findingActive BIT;
     
    DECLARE cursorNativeId
        CURSOR FOR
            SELECT * FROM TempTable;
 
    OPEN cursorNativeId;
 
    FETCH cursorNativeId INTO @findingNativeId, @rpaNativeId, @findingVulnId, @findingId;
    WHILE @@FETCH_STATUS = 0
    BEGIN
         
        SET @correctNativeId = concat(substring(@findingNativeId,1,charindex('-',@findingNativeId)),@rpaNativeId);
        IF EXISTS (SELECT id FROM Finding WHERE vulnerabilityId = @findingVulnId AND nativeId = @correctNativeId)
        BEGIN
            SELECT @findingToDELETE = id, @findingActive = active FROM Finding WHERE nativeId = @correctNativeId AND vulnerabilityId = @findingVulnId AND id != @findingId;
            EXEC DELETEDataFlowElement @findingIdToDELETE = @findingToDELETE;
            UPDATE Finding SET nativeId=@correctNativeId , active=@findingActive  WHERE id = @findingId;
            DELETE FROM Finding WHERE id = @findingToDELETE;
        END
        FETCH cursorNativeId INTO @findingNativeId, @rpaNativeId, @findingVulnId, @findingId;
         
    END
    CLOSE cursorNativeId
    DEALLOCATE cursorNativeId
GO
 
EXEC updateLegacyNativeIdVeracode;
DROP PROCEDURE updateLegacyNativeIdVeracode;
 
DROP TABLE TempTable;
SELECT f.nativeId AS findingNativeId,rpa.nativeId AS rpaNativeId,f.vulnerabilityId AS findingVulnId,f.id AS findingId ,substring(f.rawFinding,PATINDEX('%SimilarityId%',f.rawFinding)+14,charindex('"',substring(f.rawFinding,PATINDEX('%SimilarityId%',f.rawFinding)+14,35))-1) AS similarityId,substring(substring(rawFinding,PATINDEX('%PathId%',rawFinding)+7,10),1,CHARINDEX('"',substring(rawFinding,PATINDEX('%PathId%',rawFinding)+7,10))-1) AS pathId into TempTable
FROM Finding AS f
INNER JOIN Scan AS s ON f.scanId = s.id
INNER JOIN RemoteProviderApplication rpa ON (s.remoteProviderEntityId=rpa.remoteProviderEntityId AND s.applicationChannelId = rpa.applicationChannel_id AND s.applicationId=rpa.applicationId)
WHERE f.nativeId LIKE '%-CMlegacy';
 
IF OBJECT_ID('updateLegacyNativeIdCheckmarx','P') IS NOT NULL
DROP PROCEDURE updateLegacyNativeIdCheckmarx
GO
 
CREATE PROCEDURE updateLegacyNativeIdCheckmarx
AS
    DECLARE @findingNativeId varchar(256);
    DECLARE @rpaNativeId varchar(1024);
    DECLARE @findingVulnId INT;
    DECLARE @findingId INT;
    DECLARE @pathId VARCHAR(256);
    DECLARE @similarityId VARCHAR(256);
    DECLARE @findingIdToDELETE INT;
    DECLARE @correctNativeId varchar(256);
    DECLARE @findingActive BIT;
     
    DECLARE cursorNativeId
        CURSOR FOR
            SELECT * FROM TempTable;
 
    OPEN cursorNativeId;
 
    FETCH cursorNativeId INTO @findingNativeId, @rpaNativeId, @findingVulnId, @findingId, @similarityId, @pathId;
    WHILE @@FETCH_STATUS = 0
    BEGIN   
        SET @correctNativeId = LOWER(CONVERT(VARCHAR(50),HASHBYTES('MD5', concat('SimilarityId: ', @similarityId, ', PathId: ', @pathId)),2));
        IF EXISTS (SELECT id FROM Finding WHERE vulnerabilityId = @findingVulnId AND nativeId = @correctNativeId)
        BEGIN
            SELECT @findingIdToDELETE = id, @findingActive = active FROM Finding WHERE nativeId = @correctNativeId AND vulnerabilityId = @findingVulnId AND id != @findingId;
            EXEC DELETEDataFlowElement @findingIdToDELETE = @findingIdToDELETE;
            UPDATE Finding SET nativeId=@correctNativeId , active=@findingActive  WHERE id = @findingId;
            DELETE FROM Finding WHERE id = @findingIdToDELETE;
        END
 
        FETCH cursorNativeId INTO @findingNativeId, @rpaNativeId, @findingVulnId, @findingId, @similarityId, @pathId;
 
    END
    CLOSE cursorNativeId;
    DEALLOCATE cursorNativeId;
GO
 
EXEC updateLegacyNativeIdCheckmarx;
DROP PROCEDURE updateLegacyNativeIdCheckmarx;
 
DROP TABLE TempTable;
SELECT f.nativeId AS findingNativeId,rpa.nativeId AS rpaNativeId,f.vulnerabilityId AS findingVulnId,f.id AS findingId, f.channelVulnerabilityId AS findingChannelId, f.surfaceLocationId AS findingSurfaceId into TempTable
FROM Finding f 
INNER JOIN Scan s ON (f.scanId = s.id)
INNER JOIN RemoteProviderApplication rpa ON (s.remoteProviderEntityId=rpa.remoteProviderEntityId AND s.applicationChannelId = rpa.applicationChannel_id AND s.applicationId=rpa.applicationId)
WHERE f.nativeId LIKE '%-QWlegacy';
 
IF OBJECT_ID('updateLegacyNativeIdQualys','P') IS NOT NULL
DROP PROCEDURE updateLegacyNativeIdQualys
GO
 
CREATE PROCEDURE updateLegacyNativeIdQualys
AS
    DECLARE @findingNativeId varchar(256);
    DECLARE @rpaNativeId varchar(1024);
    DECLARE @findingVulnId INT;
    DECLARE @findingId INT;
    DECLARE @findingToDELETE INT;
    DECLARE @findingChannelId INT;
    DECLARE @findingSurfaceId INT;
    DECLARE @channelVulnName varchar(256);
    DECLARE @url varchar(256);
    DECLARE @parameter varchar(256);
    DECLARE @correctNativeId varchar(256);
    DECLARE @findingActive BIT;
     
    DECLARE cursorNativeId
        CURSOR FOR
            SELECT * FROM TempTable;
 
    OPEN cursorNativeId;
 
    FETCH cursorNativeId INTO @findingNativeId, @rpaNativeId, @findingVulnId, @findingId, @findingChannelId, @findingSurfaceId;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SELECT @channelVulnName = cv.name FROM Finding f INNER JOIN ChannelVulnerability cv ON (f.channelVulnerabilityId = cv.id)  WHERE f.id = @findingId;
        SELECT @url = concat(sl.protocol, '://', sl.host, ':', sl.port, sl.path) FROM Finding f INNER JOIN SurfaceLocation sl ON (f.surfaceLocationId = sl.id) WHERE f.id = @findingId;
        SELECT @parameter = sl.parameter FROM Finding f INNER JOIN SurfaceLocation sl ON (f.surfaceLocationId = sl.id) WHERE f.id = @findingId;
         
        IF @parameter is not null
            SET @correctNativeId = LOWER(CONVERT(VARCHAR(50),HASHBYTES('MD5', lower(concat(@channelVulnName,@url,@parameter))),2));
        ELSE
            SET @correctNativeId = LOWER(CONVERT(VARCHAR(50),HASHBYTES('MD5', lower(concat(@channelVulnName,@url))),2));
 
        IF EXISTS (SELECT id FROM Finding WHERE vulnerabilityId = @findingVulnId AND nativeId = @correctNativeId)
        BEGIN
        SELECT @findingToDELETE = id, @findingActive = active FROM Finding WHERE nativeId = @correctNativeId AND vulnerabilityId = @findingVulnId AND id != @findingId;
        EXEC DELETEDataFlowElement @findingIdToDELETE = @findingToDELETE;
        UPDATE Finding SET nativeId=@correctNativeId , active=@findingActive  WHERE id = @findingId;
        DELETE FROM Finding WHERE id = @findingToDELETE;
        END
        FETCH cursorNativeId INTO @findingNativeId, @rpaNativeId, @findingVulnId, @findingId, @findingChannelId, @findingSurfaceId;
         
    END
    CLOSE cursorNativeId;
    DEALLOCATE cursorNativeId;
GO
 
EXEC updateLegacyNativeIdQualys;
DROP PROCEDURE updateLegacyNativeIdQualys;
 
DROP PROCEDURE DELETEDataFlowElement;
 
DROP TABLE TempTable;

You may now log in to ThreadFix and verify that the new version is installed; the version is included in the page footer after logging in.

Versions Compared

Old Version 4

New Version 5

Key

📙 You will learn

Prerequisites

ThreadFix Deployment Update

Before-Restart Database Updates

Database Updates

Start Tomcat/ThreadFix

After-Restart Database Updates

Database Updates

Table of Contents

Page Comparison

Versions Compared

Old Version 4

New Version 5

Key

📙 You will learn

Prerequisites

ThreadFix Deployment Update

Before-Restart Database Updates

Database Updates

Start Tomcat/ThreadFix

After-Restart Database Updates

Database Updates

Table of Contents