fortify_ssc_report.java
/*********************************************************************
*
* Opentext Fortify SSC DB 기반 리포트를 CSV 또는 XLS로 출력
* (취약점의 경로 및 라인번호, SSC 링크까지 상세히 출력)
*
* 설명 : Fortify SSC DB에 접속하여 Application에 대한 취약점
* 리포트 작성
* 실행 전 ssc_info.conf 를 편집하여 설정 셋팅
* 빌드 방법: javac -encoding utf-8 -cp lib/* fortify_ssc_report.java
* 실행 방법: java -cp lib/* fortify_ssc_report -v -i <AppID>
* -i <AppID> :특정 애플리케이션 버전에 대한 취약점만 출력
* 생략 시 전 애플리케이션이 대상
* -v: 결과를 stdout으로 출력
* 작성자 : 이존석 (hasu0707@esvali.com)
*
********************************************************************/
import org.apache.poi.common.usermodel.Hyperlink;
import org.apache.poi.common.usermodel.HyperlinkType;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFHyperlink;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFHyperlink;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;
import java.util.Set;
import java.util.TreeMap;
public class fortify_ssc_report {
private static final String thisAppName = "fortify_ssc_report";
public static boolean verbose = false;
public static boolean isAllApplications = true;
public static String applicationID = "0";
public static HashMap < String, String > propMap = new HashMap < String, String > ();
/**
**********************************************************
* 사용법 출력
**********************************************************
*/
public static void usage() {
System.out.println(thisAppName);
System.out.println(" -i <SSC Application ID> : Application ID to report on.");
System.out.println(" -v : verbose");
}
/**
**********************************************************
* 설정 파일 읽기
**********************************************************
*/
public static int readPropertyFile(String fileName) {
Properties prop = new Properties();
try (FileInputStream fis = new FileInputStream(fileName)) {
prop.load(fis);
} catch (FileNotFoundException ex) {
ex.printStackTrace();
System.exit(1);
} catch (IOException ex) {
ex.printStackTrace();
System.exit(2);
}
// 설정 값 읽어오기
propMap.put("title", prop.getProperty("title"));
propMap.put("db_jdbc_addr", prop.getProperty("db_jdbc_addr"));
propMap.put("db_type", prop.getProperty("db_type"));
propMap.put("db_name", prop.getProperty("db_name"));
propMap.put("db_id", prop.getProperty("db_id"));
propMap.put("db_pw", prop.getProperty("db_pw"));
propMap.put("report_type", prop.getProperty("report_type"));
propMap.put("ssc_url", prop.getProperty("ssc_url"));
propMap.put("jdbc_class_name", prop.getProperty("jdbc_class_name"));
propMap.put("filterset", prop.getProperty("filterset"));
propMap.put("groupingtype", prop.getProperty("groupingtype"));
propMap.put("orderby", prop.getProperty("orderby"));
// 설정에 없는 경우 기본값 셋팅
if (propMap.get("title") == null)
propMap.put("title", "FORTIFY REPORT");
if (propMap.get("db_jdbc_addr") == null)
propMap.put("db_jdbc_addr", "jdbc:mysql://localhost:3306");
if (propMap.get("db_type") == null)
propMap.put("db_type", "mysql");
if (propMap.get("db_name") == null)
propMap.put("db_name", "/db_ssc_2320");
if (propMap.get("db_id") == null)
propMap.put("db_id", "root");
if (propMap.get("db_pw") == null)
propMap.put("db_pw", "root");
if (propMap.get("report_type") == null)
propMap.put("report_type", "xls");
if (propMap.get("ssc_url") == null)
propMap.put("ssc_url", "http://localhost:8080/ssc");
if (propMap.get("jdbc_class_name") == null)
propMap.put("jdbc_class_name", "com.mysql.cj.jdbc.Driver");
if (propMap.get("filterset") == null)
propMap.put("filterset", "32142c2d-3f7f-4863-a1bf-9b1e2f34d2ed");
if (propMap.get("groupingtype") == null)
propMap.put("groupingtype", "11111111-1111-1111-1111-111111111165");
if (propMap.get("orderby") == null)
propMap.put("orderby", "friority");
return 0;
}
/**
**********************************************************
* 명령행 파싱
* 반환값: 리포팅 할 ApplicationID
**********************************************************
*/
public static int parseCommandLine(String[] optionString) {
// -i <애플리케이션 번호> : 리포팅할 애플리케이션 ID
// -v : 리포팅 생성 과정 출력
for (int i = 0; i < optionString.length; i++) {
//debug
System.out.println("OPT:"+ optionString[i]);
if ("-v".equals(optionString[i]) || "--verbose".equals(optionString[i])) {
verbose = true;
} else if ("-i".equals(optionString[i]) || "--id".equals(optionString[i])) {
if (i + 1 < optionString.length) {
applicationID = optionString[++i];
isAllApplications = false;
} else {
System.err.println("Error: missing application ID");
usage();
System.exit(1);
}
} else {
System.err.println("Error: unrecognized argument: " + optionString[i]);
usage();
System.exit(1);
}
}
return Integer.parseInt(applicationID);
}
/**
**********************************************************
* SQL 쿼리 스트링 작성
**********************************************************
*/
public static String getSQLString(String db_type) {
// SQL 정의
// filterset.enabled = 'N'이면 QuickView 모드
String sqlString = null;
switch (db_type) {
case "mysql":
sqlString =
"SELECT projectversion.id AS '01',\n" +
" issue.id AS '02',\n" +
" issue.issueInstanceId AS '03',\n" +
" project.name AS '04',\n" +
" projectversion.name AS '05',\n" +
" folder.name AS '06',\n" +
" issue.kingdom AS '07',\n" +
" issue.issueType AS '08',\n" +
" issue.issueSubtype AS '09',\n" +
" issue.fileName AS '10',\n" +
" issue.packageName AS '11',\n" +
" issue.className AS '12',\n" +
" issue.functionName AS '13',\n" +
" issue.lineNumber AS '14'\n" +
"FROM issuecache\n" +
" LEFT JOIN issue\n" +
" ON issuecache.issue_id = issue.id\n" +
" LEFT JOIN projectversion\n" +
" ON issuecache.projectVersion_id = projectversion.id\n" +
" LEFT JOIN project\n" +
" ON projectversion.project_id = project.id AND projectversion.active = 'Y'\n" +
" LEFT JOIN folder\n" +
" ON issuecache.folder_id = folder.id\n" +
" LEFT JOIN filterset\n" +
" ON issuecache.filterSet_id = filterset.id\n" +
"WHERE filterset.enabled = 'N'\n" +
" AND issuecache.hidden = 'N'\n" +
" AND issue.suppressed = 'N'\n" +
" AND issue.scanStatus <> 'REMOVED'\n" +
" AND issue.friority <> 'Medium'\n" +
" AND issue.friority <> 'Low'\n" +
/*
"GROUP BY project.name,\n" +
" projectversion.name,\n" +
" folder.name,\n" +
" issue.kingdom,\n" +
" issue.issueType,\n" +
" issue.issueSubtype,\n" +
" issue.fileName,\n" +
" issue.packageName,\n" +
" issue.className,\n" +
" issue.functionName,\n" +
" issue.lineNumber\n" +
*/
"ORDER BY project.name ASC,\n" +
" projectversion.name ASC,\n" +
" folder.name ASC,\n" +
" issue.kingdom ASC,\n" +
" issue.fileName ASC,\n" +
" issue.issueType ASC,\n" +
" issue.issueSubtype ASC";
break;
case "mssql":
sqlString =
"SELECT projectversion.id AS '01',\n" +
" issue.id AS '02',\n" +
" issue.issueInstanceId AS '03',\n" +
" project.name AS '04',\n" +
" projectversion.name AS '05',\n" +
" folder.name AS '06',\n" +
" issue.kingdom AS '07',\n" +
" issue.issueType AS '08',\n" +
" issue.issueSubtype AS '09',\n" +
" issue.fileName AS '10',\n" +
" issue.packageName AS '11',\n" +
" issue.className AS '12',\n" +
" issue.functionName AS '13',\n" +
" issue.lineNumber AS '14'\n" +
"FROM issuecache\n" +
" LEFT JOIN issue\n" +
" ON issuecache.issue_id = issue.id\n" +
" LEFT JOIN projectversion\n" +
" ON issuecache.projectVersion_id = projectversion.id\n" +
" LEFT JOIN project\n" +
" ON projectversion.project_id = project.id AND projectversion.active = 'Y'\n" +
" LEFT JOIN folder\n" +
" ON issuecache.folder_id = folder.id\n" +
" LEFT JOIN filterset\n" +
" ON issuecache.filterSet_id = filterset.id\n" +
"WHERE filterset.enabled = 'N'\n" +
" AND issuecache.hidden = 'N'\n" +
" AND issue.suppressed = 'N'\n" +
" AND issue.scanStatus <> 'REMOVED'\n" +
" AND issue.friority <> 'Medium'\n" +
" AND issue.friority <> 'Low'\n" +
/*
"GROUP BY project.name,\n" +
" projectversion.name,\n" +
" folder.name,\n" +
" issue.kingdom,\n" +
" issue.issueType,\n" +
" issue.issueSubtype,\n" +
" issue.fileName,\n" +
" issue.packageName,\n" +
" issue.className,\n" +
" issue.functionName,\n" +
" issue.lineNumber\n" +
*/
"ORDER BY project.name ASC,\n" +
" projectversion.name ASC,\n" +
" folder.name ASC,\n" +
" issue.kingdom ASC,\n" +
" issue.fileName ASC,\n" +
" issue.issueType ASC,\n" +
" issue.issueSubtype ASC";
break;
case "oracle" :
sqlString =
"SELECT projectversion.id,\n" +
" issue.id,\n" +
" issue.issueInstanceId,\n" +
" project.name,\n" +
" projectversion.name,\n" +
" folder.name,\n" +
" issue.kingdom,\n" +
" issue.issueType,\n" +
" issue.issueSubtype,\n" +
" issue.fileName,\n" +
" issue.packageName,\n" +
" issue.className,\n" +
" issue.functionName,\n" +
" issue.lineNumber\n" +
"FROM issuecache\n" +
" LEFT JOIN issue\n" +
" ON issuecache.issue_id = issue.id\n" +
" LEFT JOIN projectversion\n" +
" ON issuecache.projectVersion_id = projectversion.id\n" +
" LEFT JOIN project\n" +
" ON projectversion.project_id = project.id AND projectversion.active = 'Y'\n" +
" LEFT JOIN folder\n" +
" ON issuecache.folder_id = folder.id\n" +
" LEFT JOIN filterset\n" +
" ON issuecache.filterSet_id = filterset.id\n" +
"WHERE filterset.enabled = 'N'\n" +
" AND issuecache.hidden = 'N'\n" +
" AND issue.suppressed = 'N'\n" +
" AND issue.scanStatus <> 'REMOVED'\n" +
" AND issue.friority <> 'Medium'\n" +
" AND issue.friority <> 'Low'\n" +
/*
"GROUP BY project.name,\n" +
" projectversion.name,\n" +
" folder.name,\n" +
" issue.kingdom,\n" +
" issue.issueType,\n" +
" issue.issueSubtype,\n" +
" issue.fileName,\n" +
" issue.packageName,\n" +
" issue.className,\n" +
" issue.functionName,\n" +
" issue.lineNumber\n" +
*/
"ORDER BY project.name ASC,\n" +
" projectversion.name ASC,\n" +
" folder.name ASC,\n" +
" issue.kingdom ASC,\n" +
" issue.fileName ASC,\n" +
" issue.issueType ASC,\n" +
" issue.issueSubtype ASC";
break;
default :
System.out.println("ERROR: DB Type is unknown-"+db_type);
System.exit(1);
}
return sqlString;
}
/**
**********************************************************
* CSV 파일에 타이틀 줄을 써 넣는다.
**********************************************************
*/
public static String getSSCHyperLink(String AppID, String issueID) {
String hyperLink = null;
hyperLink = propMap.get("ssc_url");
hyperLink = hyperLink + "/html/ssc/version/";
hyperLink = hyperLink + AppID + "/";
hyperLink = hyperLink + "audit?q=";
hyperLink = hyperLink + "%5Binstance%20id%5D%3A" + issueID + "%20";
hyperLink = hyperLink + "%5Banalysis%20type%5D%3ASCA&filterset=" + propMap.get("filterset") + "&";
hyperLink = hyperLink + "groupingtype=" + propMap.get("groupingtype") + "&";
hyperLink = hyperLink + "orderby=" + propMap.get("orderby") + "&";
hyperLink = hyperLink + "issue=" + issueID + "&";
hyperLink = hyperLink + "enginetype=SCA&viewTab=code";
return hyperLink;
}
/**
**********************************************************
* CSV 파일에 타이틀 줄을 써 넣는다.
**********************************************************
*/
public static int csvWriteTitle(FileWriter fileDesc) {
String csvTitle = "ID,APPLICATION,VERSION,FRIORITY,KINGDOM,ISSUE_TYPE,ISSUE_SUBTYPE,FILE,FUNCTION(PACKAGE|CLASS|METHOD),LINE,HYPER_LINK\n";
try {
fileDesc.write(csvTitle);
} catch (Exception e) {
System.err.println("ERROR: write - " + e.getMessage());
e.printStackTrace();
}
return 0;
}
/**
**********************************************************
* CSV 파일에 목록을 작성한다.
**********************************************************
*/
public static int csvWriteList(ResultSet resultQuery, FileWriter fileDesc) {
int listCount;
boolean isWrite = false;
String sinkString = null;
listCount = 0;
try {
while (resultQuery.next()) {
// 지정된 애플리케이션 번호가 있으면 그것만 출력
isWrite = false;
if (isAllApplications) {
isWrite = true;
} else {
if (applicationID.equals(resultQuery.getString(1))) {
isWrite = true;
}
}
if (isWrite) {
// 함수명 문자열 완성
sinkString = resultQuery.getString(11) + "|" + resultQuery.getString(12) + "|" + resultQuery.getString(13);
String csv_data = String.format("%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s\n",
resultQuery.getString(2),
resultQuery.getString(4),
resultQuery.getString(5),
resultQuery.getString(6),
resultQuery.getString(7),
resultQuery.getString(8),
resultQuery.getString(9),
resultQuery.getString(10),
sinkString,
resultQuery.getString(14),
getSSCHyperLink(resultQuery.getString(1), resultQuery.getString(3)));
try {
fileDesc.write(csv_data);
if (verbose) {
System.out.println(csv_data);
}
listCount += 1;
} catch (IOException e) {
System.err.println("ERROR: fileDesc.write - " + e.getMessage());
e.printStackTrace();
}
}
}
} catch (SQLException e) {
System.err.println("ERROR: resultQuery.getString - " + e.getMessage());
e.printStackTrace();
}
return listCount;
}
/**
**********************************************************
* SQL 쿼리 결과를 CSV 파일로 저장
**********************************************************
*/
public static int csvWrite(ResultSet resultQuery) {
FileWriter fileDesc = null;
int listCount;
try {
fileDesc = new FileWriter(thisAppName + ".csv");
} catch (IOException e) {
System.err.println("ERROR:" + e.getMessage());
e.printStackTrace();
}
csvWriteTitle(fileDesc);
listCount = csvWriteList(resultQuery, fileDesc);
try {
fileDesc.close();
} catch (IOException e) {
System.err.println("ERROR:" + e.getMessage());
e.printStackTrace();
}
return listCount;
}
/**
**********************************************************
* excel 파일에 타이틀 줄을 써 넣는다.
**********************************************************
*/
public static int xlsWriteTitle(XSSFWorkbook xlsWorkBook, XSSFSheet xlsSheet) {
int rowCount = 0;
CellStyle cellStyle1 = xlsWorkBook.createCellStyle();
CellStyle cellStyle2 = xlsWorkBook.createCellStyle();
CellStyle cellStyle3 = xlsWorkBook.createCellStyle();
XSSFFont cellFont1 = xlsWorkBook.createFont();
XSSFFont cellFont2 = xlsWorkBook.createFont();
XSSFFont cellFont3 = xlsWorkBook.createFont();
Map<String, Object[]> titleData = new TreeMap<>();
// 제목
cellFont1.setFontHeightInPoints((short) 12);
cellFont1.setFontName("Gulim");
cellFont1.setBold(true);
cellFont1.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
// 주의 사항
cellFont2.setFontHeightInPoints((short) 9);
cellFont2.setFontName("Gulim");
cellFont2.setBold(false);
cellFont2.setColor(HSSFColor.HSSFColorPredefined.RED.getIndex());
// 컬럼 명
cellFont3.setFontHeightInPoints((short) 9);
cellFont3.setFontName("Gulim");
cellFont3.setBold(true);
cellFont3.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
cellStyle1.setFont(cellFont1);
cellStyle1.setWrapText(false);
cellStyle2.setFont(cellFont2);
cellStyle2.setWrapText(false);
cellStyle3.setFont(cellFont3);
cellStyle3.setWrapText(false);
// 제목 추가
cellStyle1.setFont(cellFont1);
XSSFRow titleRow1 = xlsSheet.createRow(rowCount++);
Cell titleCell1 = titleRow1.createCell(0);
titleCell1.setCellStyle(cellStyle1);
titleCell1.setCellValue(propMap.get("title"));
// 주의 사항
XSSFRow titleRow2 = xlsSheet.createRow(rowCount++);
Cell titleCell2 = titleRow2.createCell(0);
titleCell2.setCellStyle(cellStyle2);
titleCell2.setCellValue("(**) " + propMap.get("ssc_url") + " 에 사번으로 로그인 후 HYPER_LINK를 클릭하면 상세 내용 확인이 가능 합니다.");
titleData.put("1", new Object[]{"ID", "APPLICATION", "VERSION", "FRIORITY", "KINGDOM", "ISSUE_TYPE", "ISSUE_SUBTYPE", "FILE", "FUNCTION(PACKAGE|CLASS|METHOD)", "LINE", "HYPER_LINK"});
// data에서 keySet를 가져온다. 이 Set 값들을 조회하면서 데이터들을 sheet에 입력한다.
Set<String> titleKeySet = titleData.keySet();
int rowNum = rowCount++;
// 알아야할 점은 TreeMap을 통해 생성된 keySet는 for를 조회 시, 키 값이 오름차순으로 조회된다.
for (String keyString : titleKeySet) {
Row rowValue = xlsSheet.createRow(rowNum++);
Object[] cellObjectArr = titleData.get(keyString);
int cellNum = 0;
for (Object cellObject : cellObjectArr) {
Cell cellVaule = rowValue.createCell(cellNum++);
if (cellObject instanceof String) {
cellVaule.setCellValue((String)cellObject);
} else if (cellObject instanceof Integer) {
cellVaule.setCellValue((Integer)cellObject);
}
cellVaule.setCellStyle(cellStyle3);
}
}
return 0;
}
/**
**********************************************************
* excel 파일에 목록을 작성한다.
**********************************************************
*/
public static int xlsWriteList(ResultSet resultQuery, XSSFWorkbook xlsWorkBook, XSSFSheet xlsSheet) {
int listCount = 0;
boolean isWrite = false;
String sinkString = null;
CreationHelper createHelper = xlsWorkBook.getCreationHelper();
CellStyle cellStyle = xlsWorkBook.createCellStyle();
XSSFFont cellFont = xlsWorkBook.createFont();
Map<String, Object[]> titleData = new TreeMap<>();
cellFont.setFontHeightInPoints((short) 9); // 폰트 크기
cellFont.setFontName("Gulim"); // 폰트
cellStyle.setFont(cellFont);
cellStyle.setWrapText(true);
try {
while (resultQuery.next()) {
// 지정된 애플리케이션 번호가 있으면 그것만 출력
isWrite = false;
if (isAllApplications) {
isWrite = true;
} else {
if (applicationID.equals(resultQuery.getString(1))) {
isWrite = true;
}
}
if (isWrite) {
// 함수명 문자열 완성
sinkString = resultQuery.getString(11) + "|" + resultQuery.getString(12) + "|" + resultQuery.getString(13);
// SSC 하이퍼 링크 생성
XSSFHyperlink sscHyperLink = (XSSFHyperlink)createHelper.createHyperlink(HyperlinkType.URL);
sscHyperLink.setAddress(getSSCHyperLink(resultQuery.getString(1), resultQuery.getString(3)));
titleData.put("1", new Object[]{
resultQuery.getString(2),
resultQuery.getString(4),
resultQuery.getString(5),
resultQuery.getString(6),
resultQuery.getString(7),
resultQuery.getString(8),
resultQuery.getString(9),
resultQuery.getString(10),
sinkString,
resultQuery.getString(14),
"View"
});
// data에서 keySet를 가져온다. 이 Set 값들을 조회하면서 데이터들을 sheet에 입력한다.
Set<String> titleKeySet = titleData.keySet();
int rowNum = listCount + 3;
// 알아야할 점은 TreeMap을 통해 생성된 keySet는 for를 조회 시, 키 값이 오름차순으로 조회된다.
for (String keyString : titleKeySet) {
Row rowValue = xlsSheet.createRow(rowNum++);
Object[] cellObjectArr = titleData.get(keyString);
int cellNum = 0;
if (verbose) {
System.out.printf("Writing excel row %d\n", listCount);
}
for (Object cellObject : cellObjectArr) {
Cell cellVaule = rowValue.createCell(cellNum++);
if (cellObject instanceof String) {
cellVaule.setCellValue((String)cellObject);
} else if (cellObject instanceof Integer) {
cellVaule.setCellValue((Integer)cellObject);
}
// 링크 적용
if (cellNum == 11) {
cellVaule.setHyperlink(sscHyperLink);
}
// 셀 스타일 적용
cellVaule.setCellStyle(cellStyle);
// 자동 컬럼넓이 적용
xlsSheet.autoSizeColumn(cellNum);
}
}
listCount += 1;
}
}
} catch (SQLException e) {
System.err.println("ERROR: resultQuery.getString - " + e.getMessage());
e.printStackTrace();
}
return listCount;
}
/**
**********************************************************
* SQL 쿼리 결과를 excel 파일로 저장
**********************************************************
*/
public static int xlsWrite(ResultSet resultQuery) {
int listCount;
// 빈 Workbook 생성
XSSFWorkbook xlsWorkBook = new XSSFWorkbook();
// 빈 Sheet를 생성
XSSFSheet xlsSheet = xlsWorkBook.createSheet("Fortify Report");
xlsWriteTitle(xlsWorkBook, xlsSheet);
listCount = xlsWriteList(resultQuery, xlsWorkBook, xlsSheet);
try {
FileOutputStream fileDesc = new FileOutputStream(thisAppName + ".xls");
xlsWorkBook.write(fileDesc);
fileDesc.close();
} catch (IOException e) {
System.err.println("ERROR:" + e.getMessage());
e.printStackTrace();
}
return listCount;
}
/**
**********************************************************
* DB에 접속하여 SQL문을 실행한다.
**********************************************************
*/
public static int runSQLQuery() {
Connection conn = null;
Statement stmt = null;
ResultSet resultQuery = null;
String sqlQueryString = null;
int resultCount = 0;
// DB 종류에 맞는 JDBC 동적 로딩
try {
Class.forName(propMap.get("jdbc_class_name"));
} catch (ClassNotFoundException e) {
System.err.println("ERROR: Class.forName - " + e.getMessage());
e.printStackTrace();
}
// DB 접속
try {
conn = DriverManager.getConnection(
propMap.get("db_jdbc_addr") + propMap.get("db_name"),
propMap.get("db_id"),
propMap.get("db_pw"));
} catch (SQLException e) {
System.err.println("ERROR: DriverManager.getConnection - " + e.getMessage());
e.printStackTrace();
}
// SQL 작성 및 실행
try {
stmt = conn.createStatement();
sqlQueryString = getSQLString("mysql");
System.out.println("-- SQL:\n" + sqlQueryString + "\n");
resultQuery = stmt.executeQuery(sqlQueryString);
switch (propMap.get("report_type")) {
case "csv":
resultCount = csvWrite(resultQuery);
break;
case "xls":
resultCount = xlsWrite(resultQuery);
break;
default:
System.err.println("ERROR: Unknown report type.");
}
resultQuery.close();
stmt.close();
conn.close();
} catch (SQLException e) {
System.err.println("ERROR: executeQuery - " + e.getMessage());
e.printStackTrace();
}
return resultCount;
}
/**
**********************************************************
* main
**********************************************************
*/
public static void main(String[] args) throws Exception {
int arg1, resultCount = 0;
String propFileName = "ssc_info.conf";
parseCommandLine(args);
readPropertyFile(propFileName);
if (applicationID != "0") {
System.out.printf("APPLICATION ID: %s\n", applicationID);
}
resultCount = runSQLQuery();
System.out.println("Issue total: " + resultCount + "\n");
}
}
ssc_info.conf
# MySQL title=FORTIFY REPORT db_type=mysql db_jdbc_addr=jdbc:mysql://10.10.10.149:3306 db_name=/db_ssc_2222 db_id=u_ssc db_pw=<Password> jdbc_class_name=com.mysql.cj.jdbc.Driver report_type=xls ssc_url=http://10.10.10.43:8080/ssc filterset=32142c2d-3f7f-4863-a1bf-9b1e2f34d2ed groupingtype=11111111-1111-1111-1111-111111111165 orderby=friority # Microsoft SQL #title=FORTIFY REPORT #db_type=mssql #db_jdbc_addr=jdbc:sqlserver://10.10.10.105; #db_name=DatabaseName=db_ssc_2020 #db_id=sa #db_pw=<Password> #jdbc_class_name=com.microsoft.sqlserver.jdbc.SQLServerDriver #report_type=csv #ssc_url=http://10.10.10.43:8080/ssc #filterset=32142c2d-3f7f-4863-a1bf-9b1e2f34d2ed #groupingtype=11111111-1111-1111-1111-111111111165 #orderby=friority # Oracle #title=FORTIFY REPORT #db_type=oracle #db_jdbc_addr=jdbc:oracle:thin:@10.10.10.105:1521 #db_name=/ORCLCDB #db_id=u_ssc_2310 #db_pw=<Password> #jdbc_class_name=oracle.jdbc.driver.OracleDriver #report_type=csv #ssc_url=http://10.10.10.43:8080/ssc #filterset=32142c2d-3f7f-4863-a1bf-9b1e2f34d2ed #groupingtype=11111111-1111-1111-1111-111111111165 #orderby=friority
RUN.BAT
@ECHO OFF REM ######################################################################## REM # REM # fortify_ssc_report의 윈도우용 실행 스크립트 REM # REM ######################################################################## REM ======================================= REM = CODEPAGE를 UTF-8로 변경 REM ======================================= CHCP 65001 1> NUL 2> NUL CLS REM ######################################################################## REM # REM # 환경 변수 설정 (첫 실행시 반드시 환경에 맞게 수정요망) REM # 윈도우 스케줄러 실행을 위해서는 WORK_PATH에 절대 경로 설정 REM # REM ######################################################################## SET CLASS_NAME1=fortify_ssc_report SET RUN_COMPILE=1 SET "WORK_PATH=%CD%" SET "CSV_RESULT_PATH=%WORK_PATH%\reports" SET "JAVA_HOME=C:\PortableApps\jdk-17.0.9" SET "CLASSPATH=.;%WORK_PATH%\lib\*" SET "CURRENT_DIR=%CD%" CD /D "%WORK_PATH%" IF NOT EXIST %CSV_RESULT_PATH% MKDIR %CSV_RESULT_PATH% REM ######################################################################## REM # REM # 날짜/시간 문자열 만들기 REM # REM ######################################################################## SET TIMESTAMP_VAL=%DATE:~0,4%%DATE:~5,2%%DATE:~8,2%_%TIME:~0,2%%TIME:~3,2%%TIME:~6,2% SET TIMESTAMP_VAL=%TIMESTAMP_VAL: =0% REM ######################################################################## REM # REM # JDK 경로 추가 REM # REM ######################################################################## SET PATH=%JAVA_HOME%\bin;%PATH% REM ######################################################################## REM # REM # 빌드 및 실행 REM # REM ######################################################################## IF %RUN_COMPILE% == 1 ( javac -encoding utf-8 -cp "%CLASSPATH%" "%CLASS_NAME1%.java" ) ECHO. ECHO Running %CLASS_NAME1%... ECHO. java -cp "%CLASSPATH%" "%CLASS_NAME1%" %1 %2 %3 %4 %5 REM ######################################################################## REM # REM # 출력된 CSV 파일을 리포트 디렉토리로 이동시킨다. REM # REM ######################################################################## MOVE /Y "%CLASS_NAME1%.xls" "%CSV_RESULT_PATH%\%TIMESTAMP_VAL%-%CLASS_NAME1%.xls"
run.sh
#!/bin/bash
########################################################################
#
# fortify_ssc_report의 리눅스용 실행 스크립트
#
########################################################################
########################################################################
#
# 환경 변수 설정 (첫 실행시 반드시 환경에 맞게 수정요망)
# crontab 실행을 위해서는 WORK_PATH에 절대 경로 설정
#
########################################################################
CLASS_NAME1="fortify_ssc_report"
RUN_COMPILE=1
WORK_PATH=`pwd -P`
CSV_RESULT_PATH="${WORK_PATH}/reports"
JAVA_HOME="/usr"
CLASSPATH=".:${JAVA_HOME}/lib/tools.jar:${WORK_PATH}/lib/*"
CURRENT_DIR=`pwd -P`
DATETIME_STR=`date -u +%Y%m%d_%H%M%S`
run_class ()
{
########################################################################
#
# 빌드 및 실행
#
########################################################################
if [ ${RUN_COMPILE} = 1 ]
then
javac -encoding utf-8 -cp "${CLASSPATH}" ${CLASS_NAME1}.java
fi
echo
echo "Running ${CLASS_NAME1}..."
echo
java -cp "${CLASSPATH}" ${CLASS_NAME1} $1 $2 $3 $4 $5
########################################################################
#
# 출력된 CSV 파일을 리포트 디렉토리로 이동시킨다.
#
########################################################################
OUTPUT_FILE="${CSV_RESULT_PATH}/${DATETIME_STR}-${CLASS_NAME1}.xls"
if [ -f "${CLASS_NAME1}.xls" ]
then
#iconv -f "UTF-8" -t "EUC-KR" "${CLASS_NAME1}.csv" > "${OUTPUT_FILE}"
#rm -f "${CLASS_NAME1}.csv"
mv -fv "${CLASS_NAME1}.xls" "${OUTPUT_FILE}"
fi
if [ -f "${OUTPUT_FILE}" ]
then
chmod 644 "${OUTPUT_FILE}"
fi
}
if [ ! -d "${WORK_PATH}" ]
then
mkdir -p "${WORK_PATH}"
fi
if [ ! -d "${CSV_RESULT_PATH}" ]
then
mkdir -p "${CSV_RESULT_PATH}"
fi
########################################################################
#
# 기능별로 하나씩 실행 (변수 정의를 주석처리 하면 기능 비 활성화)
#
########################################################################
cd "${WORK_PATH}"
PATH=${JAVA_HOME}/bin:${JAVA_HOME}/jre/bin:${PATH}
cd ${WORK_PATH}
run_class $1 $2 $3 $4 $5
cd ${CURRENT_DIR}
exit $?