-- 포티파이 중복 취약점 Suppress 처리 쿼리 (MySQL 전용)
-- 사용방법: (65535)를 제거를 원하는 애플리케이션 번호로 변경 후 사용
UPDATE
ISSUE AS L1
INNER JOIN ISSUE L2 ON L1.ID <> L2.ID
AND L1.ID < L2.ID
AND L2.SUPPRESSED = 'N'
AND L2.PROJECTVERSION_ID IN (65535)
SET
L2.REVISION = 1,
L2.AUDITED = 'N',
L2.AUDITEDTIME = NULL,
L2.SUPPRESSED = 'Y',
L2.ISSUESTATUS = 'Under Review',
L2.ISSUESTATE = 'Open Issue',
L2.USERNAME = NULL
WHERE
L1.KINGDOM = L2.KINGDOM
AND L1.ISSUETYPE = L2.ISSUETYPE
AND L1.ISSUESUBTYPE = L2.ISSUESUBTYPE
AND L1.FRIORITY = L2.FRIORITY
AND L1.FILENAME = L2.FILENAME
AND L1.LINENUMBER = L2.LINENUMBER
AND L1.PACKAGENAME = L2.PACKAGENAME
AND L1.FUNCTIONNAME = L2.FUNCTIONNAME
AND L1.CLASSNAME = L2.CLASSNAME
AND L1.CATEGORY = L2.CATEGORY;
--------------------------------------------------------
-- 아래는 모두 테스트 쿼리
--------------------------------------------------------
-- Low 취약점을 모두 Suppress 처리
UPDATE ISSUE SET SUPPRESSED='Y', ISSUESTATUS='Under Review', REVISION=1 WHERE PROJECTVERSION_ID IN (1) AND FRIORITY='Low';
-- Low 취약점 Suppress 원상복구
UPDATE ISSUE SET SUPPRESSED='N', ISSUESTATUS='Unreviewed', REVISION=0 WHERE PROJECTVERSION_ID IN (1);
SELECT * FROM ISSUE WHERE SUPPRESSED='Y';
-- 모니터링
SELECT ID, ISSUETYPE, ISSUESUBTYPE, FRIORITY, FILENAME, LINENUMBER, FUNCTIONNAME, SUPPRESSED FROM ISSUE ORDER BY FILENAME,ISSUESUBTYPE, ID ASC;
-- TEST#2
SELECT
L2.ID,
L2.PROJECTVERSION_ID,
L2.KINGDOM,
L2.ISSUETYPE,
L2.ISSUESUBTYPE,
L2.FRIORITY,
L2.FILENAME,
L1.LINENUMBER,
L2.LINENUMBER
FROM
ISSUE AS L1
INNER JOIN ISSUE L2 ON L1.ID <> L2.ID AND L1.ID < L2.ID AND L2.SUPPRESSED = 'N' AND L2.PROJECTVERSION_ID IN (1)
WHERE
L1.KINGDOM = L2.KINGDOM
AND L1.ISSUETYPE = L2.ISSUETYPE
AND L1.ISSUESUBTYPE = L2.ISSUESUBTYPE
AND L1.FRIORITY = L2.FRIORITY
AND L1.FILENAME = L2.FILENAME
AND L1.LINENUMBER = L2.LINENUMBER
AND L1.PACKAGENAME = L2.PACKAGENAME
AND L1.FUNCTIONNAME = L2.FUNCTIONNAME
AND L1.CLASSNAME = L2.CLASSNAME
AND L1.CATEGORY = L2.CATEGORY;
-------------------------------------
SELECT FILENAME FROM (SELECT MIN(ID) FROM ISSUE GROUP BY FILENAME HAVING COUNT(*) > 1) AS S;
UPDATE ISSUE SET SUPPRESSED='Y' WHERE PROJECTVERSION_ID IN (1) AND FILENAME='JavaSource/org/owasp/webgoat/lessons/SQLInjection/Login.java';
SELECT ID, FILENAME, SUPPRESSED FROM ISSUE WHERE FILENAME='JavaSource/org/owasp/webgoat/lessons/SQLInjection/Login.java';
SELECT ID, FILENAME, SUPPRESSED FROM ISSUE WHERE SUPPRESSED='Y';
SELECT ID, ISSUETYPE, ISSUESUBTYPE, FRIORITY, SUPPRESSED FROM ISSUE WHERE PROJECTVERSION_ID IN (1) AND FILENAME='JavaSource/org/owasp/webgoat/lessons/HtmlClues.java' AND LINENUMBER=63;
SELECT * FROM ISSUE WHERE FILENAME = 'JavaSource/org/owasp/webgoat/lessons/HtmlClues.java' AND LINENUMBER = 83;
SELECT * FROM ISSUE WHERE PROJECTVERSION_ID IN (1) AND id=82149;
file:JavaSource/org/owasp/webgoat/lessons/HtmlClues.java
suppressed:true
KINGDOM, ISSUETYPE, ISSUESUBTYPE, LINENUMBER, FRIORITY
-- 테스트 ROW 검색
SELECT ID, FILENAME, SUPPRESSED FROM ISSUE WHERE filename = "WebContent/main1.jsp";
SELECT ID, FILENAME, SUPPRESSED FROM ISSUE WHERE SUPPRESSED='Y';
-- ROW 복사본 만들기
INSERT INTO issue
(remediationconstant,
projectversion_id,
issueinstanceid,
filename,
shortfilename,
severity,
ruleguid,
confidence,
kingdom,
issuetype,
issuesubtype,
analyzer,
linenumber,
taintflag,
packagename,
functionname,
classname,
issueabstract,
friority,
enginetype,
scanstatus,
audienceset,
lastscan_id,
replacestore,
snippetid,
url,
category,
source,
sourcecontext,
sourcefile,
sink,
sinkcontext,
username,
objectversion,
revision,
audited,
auditedtime,
suppressed,
issuestatus,
issuestate,
findingguid,
dynamicconfidence,
hidden,
likelihood,
impact,
accuracy,
rtacovered,
probability,
folder_id,
founddate,
removeddate,
requestidentifier,
requestheader,
requestparameter,
requestbody,
requestmethod,
cookie,
httpversion,
attackpayload,
attacktype,
response,
triggerdefinition,
triggerstring,
triggerdisplaytext,
secondaryrequest,
sourceline,
mappedcategory,
issuerecommendation,
correlated,
correlationsetguid,
enginepriority,
contextid,
bug_id,
attacktriggerdefinition,
vulnerableparameter,
reprostepdefinition,
stacktrace,
stacktracetriggerdisplaytext,
manual,
minvirtualcallconfidence)
SELECT remediationconstant,
projectversion_id,
'07DC81F1724D3DD1F052EE4103000003',
filename,
shortfilename,
severity,
ruleguid,
confidence,
kingdom,
issuetype,
issuesubtype,
analyzer,
linenumber,
taintflag,
packagename,
functionname,
classname,
issueabstract,
friority,
enginetype,
scanstatus,
audienceset,
lastscan_id,
replacestore,
snippetid,
url,
category,
source,
sourcecontext,
sourcefile,
sink,
sinkcontext,
username,
objectversion,
revision,
audited,
auditedtime,
suppressed,
issuestatus,
issuestate,
findingguid,
dynamicconfidence,
hidden,
likelihood,
impact,
accuracy,
rtacovered,
probability,
folder_id,
founddate,
removeddate,
requestidentifier,
requestheader,
requestparameter,
requestbody,
requestmethod,
cookie,
httpversion,
attackpayload,
attacktype,
response,
triggerdefinition,
triggerstring,
triggerdisplaytext,
secondaryrequest,
sourceline,
mappedcategory,
issuerecommendation,
correlated,
correlationsetguid,
enginepriority,
contextid,
bug_id,
attacktriggerdefinition,
vulnerableparameter,
reprostepdefinition,
stacktrace,
stacktracetriggerdisplaytext,
manual,
minvirtualcallconfidence
FROM issue
WHERE issueinstanceid = "0857E967144A3C48AF21E136E28DE988";
