19 package org.sleuthkit.autopsy.centralrepository.datamodel;
21 import java.net.UnknownHostException;
22 import java.util.ArrayList;
23 import java.util.List;
24 import java.util.Collection;
25 import java.util.LinkedHashSet;
26 import java.util.stream.Collectors;
27 import java.sql.Connection;
28 import java.sql.PreparedStatement;
29 import java.sql.ResultSet;
30 import java.sql.SQLException;
31 import java.sql.Types;
32 import java.time.LocalDate;
33 import java.util.HashMap;
54 private final Map<String, Collection<CorrelationAttribute>>
bulkArtifacts;
63 badTags =
new ArrayList<>();
64 bulkArtifactsCount = 0;
65 bulkArtifacts =
new HashMap<>();
68 DEFAULT_CORRELATION_TYPES.forEach((type) -> {
69 bulkArtifacts.put(type.getDbTableName(),
new ArrayList<>());
86 return new ArrayList<>(
badTags);
115 PreparedStatement preparedStatement = null;
116 String sql =
"INSERT INTO db_info (name, value) VALUES (?, ?)";
118 preparedStatement = conn.prepareStatement(sql);
119 preparedStatement.setString(1, name);
120 preparedStatement.setString(2, value);
121 preparedStatement.executeUpdate();
122 }
catch (SQLException ex) {
123 throw new EamDbException(
"Error adding new name/value pair to db_info.", ex);
144 PreparedStatement preparedStatement = null;
145 ResultSet resultSet = null;
147 String sql =
"SELECT value FROM db_info WHERE name=?";
149 preparedStatement = conn.prepareStatement(sql);
150 preparedStatement.setString(1, name);
151 resultSet = preparedStatement.executeQuery();
152 if (resultSet.next()) {
153 value = resultSet.getString(
"value");
155 }
catch (SQLException ex) {
178 PreparedStatement preparedStatement = null;
179 String sql =
"UPDATE db_info SET value=? WHERE name=?";
181 preparedStatement = conn.prepareStatement(sql);
182 preparedStatement.setString(1, value);
183 preparedStatement.setString(2, name);
184 preparedStatement.executeUpdate();
185 }
catch (SQLException ex) {
204 PreparedStatement preparedStatement = null;
206 String sql =
"INSERT INTO cases(case_uid, org_id, case_name, creation_date, case_number, "
207 +
"examiner_name, examiner_email, examiner_phone, notes) "
208 +
"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
211 preparedStatement = conn.prepareStatement(sql);
213 preparedStatement.setString(1, eamCase.getCaseUUID());
214 if (null == eamCase.getOrg()) {
215 preparedStatement.setNull(2, Types.INTEGER);
217 preparedStatement.setInt(2, eamCase.getOrg().getOrgID());
219 preparedStatement.setString(3, eamCase.getDisplayName());
220 preparedStatement.setString(4, eamCase.getCreationDate());
221 if (
"".equals(eamCase.getCaseNumber())) {
222 preparedStatement.setNull(5, Types.INTEGER);
224 preparedStatement.setString(5, eamCase.getCaseNumber());
226 if (
"".equals(eamCase.getExaminerName())) {
227 preparedStatement.setNull(6, Types.INTEGER);
229 preparedStatement.setString(6, eamCase.getExaminerName());
231 if (
"".equals(eamCase.getExaminerEmail())) {
232 preparedStatement.setNull(7, Types.INTEGER);
234 preparedStatement.setString(7, eamCase.getExaminerEmail());
236 if (
"".equals(eamCase.getExaminerPhone())) {
237 preparedStatement.setNull(8, Types.INTEGER);
239 preparedStatement.setString(8, eamCase.getExaminerPhone());
241 if (
"".equals(eamCase.getNotes())) {
242 preparedStatement.setNull(9, Types.INTEGER);
244 preparedStatement.setString(9, eamCase.getNotes());
247 preparedStatement.executeUpdate();
248 }
catch (SQLException ex) {
263 if(autopsyCase == null){
269 autopsyCase.getName(),
271 autopsyCase.getDisplayName(),
272 autopsyCase.getCreatedDate(),
273 autopsyCase.getNumber(),
274 autopsyCase.getExaminer(),
291 PreparedStatement preparedStatement = null;
292 String sql =
"UPDATE cases "
293 +
"SET org_id=?, case_name=?, creation_date=?, case_number=?, examiner_name=?, examiner_email=?, examiner_phone=?, notes=? "
294 +
"WHERE case_uid=?";
297 preparedStatement = conn.prepareStatement(sql);
299 if (null == eamCase.getOrg()) {
300 preparedStatement.setNull(1, Types.INTEGER);
302 preparedStatement.setInt(1, eamCase.getOrg().getOrgID());
304 preparedStatement.setString(2, eamCase.getDisplayName());
305 preparedStatement.setString(3, eamCase.getCreationDate());
307 if (
"".equals(eamCase.getCaseNumber())) {
308 preparedStatement.setNull(4, Types.INTEGER);
310 preparedStatement.setString(4, eamCase.getCaseNumber());
312 if (
"".equals(eamCase.getExaminerName())) {
313 preparedStatement.setNull(5, Types.INTEGER);
315 preparedStatement.setString(5, eamCase.getExaminerName());
317 if (
"".equals(eamCase.getExaminerEmail())) {
318 preparedStatement.setNull(6, Types.INTEGER);
320 preparedStatement.setString(6, eamCase.getExaminerEmail());
322 if (
"".equals(eamCase.getExaminerPhone())) {
323 preparedStatement.setNull(7, Types.INTEGER);
325 preparedStatement.setString(7, eamCase.getExaminerPhone());
327 if (
"".equals(eamCase.getNotes())) {
328 preparedStatement.setNull(8, Types.INTEGER);
330 preparedStatement.setString(8, eamCase.getNotes());
333 preparedStatement.setString(9, eamCase.getCaseUUID());
335 preparedStatement.executeUpdate();
336 }
catch (SQLException ex) {
358 PreparedStatement preparedStatement = null;
359 ResultSet resultSet = null;
361 String sql =
"SELECT cases.id as case_id, case_uid, case_name, creation_date, case_number, examiner_name, "
362 +
"examiner_email, examiner_phone, notes, organizations.id as org_id, org_name, poc_name, poc_email, poc_phone "
364 +
"LEFT JOIN organizations ON cases.org_id=organizations.id "
365 +
"WHERE case_uid=?";
368 preparedStatement = conn.prepareStatement(sql);
369 preparedStatement.setString(1, caseUUID);
370 resultSet = preparedStatement.executeQuery();
371 if (resultSet.next()) {
374 }
catch (SQLException ex) {
382 return eamCaseResult;
394 List<CorrelationCase> cases =
new ArrayList<>();
396 PreparedStatement preparedStatement = null;
397 ResultSet resultSet = null;
399 String sql =
"SELECT cases.id as case_id, case_uid, case_name, creation_date, case_number, examiner_name, "
400 +
"examiner_email, examiner_phone, notes, organizations.id as org_id, org_name, poc_name, poc_email, poc_phone "
402 +
"LEFT JOIN organizations ON cases.org_id=organizations.id";
405 preparedStatement = conn.prepareStatement(sql);
406 resultSet = preparedStatement.executeQuery();
407 while (resultSet.next()) {
409 cases.add(eamCaseResult);
411 }
catch (SQLException ex) {
412 throw new EamDbException(
"Error getting all cases.", ex);
431 PreparedStatement preparedStatement = null;
433 String sql =
"INSERT INTO data_sources(device_id, name) VALUES (?, ?)";
436 preparedStatement = conn.prepareStatement(sql);
438 preparedStatement.setString(1, eamDataSource.getDeviceID());
439 preparedStatement.setString(2, eamDataSource.getName());
441 preparedStatement.executeUpdate();
442 }
catch (SQLException ex) {
489 PreparedStatement preparedStatement = null;
490 ResultSet resultSet = null;
492 String sql =
"SELECT * FROM data_sources WHERE device_id=?";
495 preparedStatement = conn.prepareStatement(sql);
496 preparedStatement.setString(1, dataSourceDeviceId);
497 resultSet = preparedStatement.executeQuery();
498 if (resultSet.next()) {
501 }
catch (SQLException ex) {
509 return eamDataSourceResult;
521 List<CorrelationDataSource> dataSources =
new ArrayList<>();
523 PreparedStatement preparedStatement = null;
524 ResultSet resultSet = null;
526 String sql =
"SELECT * FROM data_sources";
529 preparedStatement = conn.prepareStatement(sql);
530 resultSet = preparedStatement.executeQuery();
531 while (resultSet.next()) {
533 dataSources.add(eamDataSourceResult);
535 }
catch (SQLException ex) {
536 throw new EamDbException(
"Error getting all data sources.", ex);
556 List<CorrelationAttributeInstance> eamInstances = eamArtifact.getInstances();
557 PreparedStatement preparedStatement = null;
562 StringBuilder sql =
new StringBuilder();
563 sql.append(
"INSERT INTO ");
564 sql.append(tableName);
565 sql.append(
"(case_id, data_source_id, value, file_path, known_status, comment) ");
566 sql.append(
"VALUES ((SELECT id FROM cases WHERE case_uid=? LIMIT 1), ");
567 sql.append(
"(SELECT id FROM data_sources WHERE device_id=? LIMIT 1), ?, ?, ?, ?)");
570 preparedStatement = conn.prepareStatement(sql.toString());
572 if(! eamArtifact.getCorrelationValue().isEmpty()){
573 preparedStatement.setString(1, eamInstance.getCorrelationCase().getCaseUUID());
574 preparedStatement.setString(2, eamInstance.getCorrelationDataSource().getDeviceID());
575 preparedStatement.setString(3, eamArtifact.getCorrelationValue());
576 preparedStatement.setString(4, eamInstance.getFilePath());
577 preparedStatement.setByte(5, eamInstance.getKnownStatus().getFileKnownValue());
578 if (
"".equals(eamInstance.getComment())) {
579 preparedStatement.setNull(6, Types.INTEGER);
581 preparedStatement.setString(6, eamInstance.getComment());
584 preparedStatement.executeUpdate();
587 }
catch (SQLException ex) {
588 throw new EamDbException(
"Error inserting new artifact into artifacts table.", ex);
607 List<CorrelationAttributeInstance> artifactInstances =
new ArrayList<>();
610 PreparedStatement preparedStatement = null;
611 ResultSet resultSet = null;
614 StringBuilder sql =
new StringBuilder();
615 sql.append(
"SELECT cases.case_name, cases.case_uid, data_sources.name, device_id, file_path, known_status, comment FROM ");
616 sql.append(tableName);
617 sql.append(
" LEFT JOIN cases ON ");
618 sql.append(tableName);
619 sql.append(
".case_id=cases.id");
620 sql.append(
" LEFT JOIN data_sources ON ");
621 sql.append(tableName);
622 sql.append(
".data_source_id=data_sources.id");
623 sql.append(
" WHERE value=?");
626 preparedStatement = conn.prepareStatement(sql.toString());
627 preparedStatement.setString(1, value);
628 resultSet = preparedStatement.executeQuery();
629 while (resultSet.next()) {
631 artifactInstances.add(artifactInstance);
633 }
catch (SQLException ex) {
634 throw new EamDbException(
"Error getting artifact instances by artifactType and artifactValue.", ex);
641 return artifactInstances;
659 List<CorrelationAttributeInstance> artifactInstances =
new ArrayList<>();
662 PreparedStatement preparedStatement = null;
663 ResultSet resultSet = null;
666 StringBuilder sql =
new StringBuilder();
667 sql.append(
"SELECT cases.case_name, cases.case_uid, data_sources.name, device_id, file_path, known_status, comment FROM ");
668 sql.append(tableName);
669 sql.append(
" LEFT JOIN cases ON ");
670 sql.append(tableName);
671 sql.append(
".case_id=cases.id");
672 sql.append(
" LEFT JOIN data_sources ON ");
673 sql.append(tableName);
674 sql.append(
".data_source_id=data_sources.id");
675 sql.append(
" WHERE file_path=?");
678 preparedStatement = conn.prepareStatement(sql.toString());
679 preparedStatement.setString(1, filePath);
680 resultSet = preparedStatement.executeQuery();
681 while (resultSet.next()) {
683 artifactInstances.add(artifactInstance);
685 }
catch (SQLException ex) {
686 throw new EamDbException(
"Error getting artifact instances by artifactType and artifactValue.", ex);
693 return artifactInstances;
710 Long instanceCount = 0L;
711 PreparedStatement preparedStatement = null;
712 ResultSet resultSet = null;
715 StringBuilder sql =
new StringBuilder();
716 sql.append(
"SELECT count(*) FROM ");
717 sql.append(tableName);
718 sql.append(
" WHERE value=?");
721 preparedStatement = conn.prepareStatement(sql.toString());
722 preparedStatement.setString(1, value);
723 resultSet = preparedStatement.executeQuery();
725 instanceCount = resultSet.getLong(1);
726 }
catch (SQLException ex) {
727 throw new EamDbException(
"Error getting count of artifact instances by artifactType and artifactValue.", ex);
734 return instanceCount;
742 Double commonalityPercentage = uniqueTypeValueTuples / uniqueCaseDataSourceTuples * 100;
743 return commonalityPercentage.intValue();
760 Long instanceCount = 0L;
761 PreparedStatement preparedStatement = null;
762 ResultSet resultSet = null;
765 StringBuilder sql =
new StringBuilder();
766 sql.append(
"SELECT count(*) FROM (SELECT DISTINCT case_id, data_source_id FROM ");
767 sql.append(tableName);
768 sql.append(
" WHERE value=?) AS ");
769 sql.append(tableName);
770 sql.append(
"_distinct_case_data_source_tuple");
773 preparedStatement = conn.prepareStatement(sql.toString());
774 preparedStatement.setString(1, value);
775 resultSet = preparedStatement.executeQuery();
777 instanceCount = resultSet.getLong(1);
778 }
catch (SQLException ex) {
779 throw new EamDbException(
"Error counting unique caseDisplayName/dataSource tuples having artifactType and artifactValue.", ex);
786 return instanceCount;
794 Long instanceCount = 0L;
795 PreparedStatement preparedStatement = null;
796 ResultSet resultSet = null;
798 String stmt =
"SELECT count(*) FROM data_sources";
801 preparedStatement = conn.prepareStatement(stmt);
802 resultSet = preparedStatement.executeQuery();
804 instanceCount = resultSet.getLong(1);
805 }
catch (SQLException ex) {
806 throw new EamDbException(
"Error counting data sources.", ex);
813 return instanceCount;
831 Long instanceCount = 0L;
833 PreparedStatement preparedStatement = null;
834 ResultSet resultSet = null;
837 StringBuilder sql =
new StringBuilder();
838 sql.append(
"SELECT 0 ");
843 sql.append(
"+ (SELECT count(*) FROM ");
844 sql.append(table_name);
845 sql.append(
" WHERE case_id=(SELECT id FROM cases WHERE case_uid=?) and data_source_id=(SELECT id FROM data_sources WHERE device_id=?))");
849 preparedStatement = conn.prepareStatement(sql.toString());
851 for (
int i = 0; i < artifactTypes.size(); ++i) {
852 preparedStatement.setString(2 * i + 1, caseUUID);
853 preparedStatement.setString(2 * i + 2, dataSourceID);
856 resultSet = preparedStatement.executeQuery();
858 instanceCount = resultSet.getLong(1);
859 }
catch (SQLException ex) {
860 throw new EamDbException(
"Error counting artifact instances by caseName/dataSource.", ex);
867 return instanceCount;
881 bulkArtifacts.get(eamArtifact.getCorrelationType().getDbTableName()).add(eamArtifact);
882 bulkArtifactsCount++;
884 if (bulkArtifactsCount >= bulkArtifactsThreshold) {
906 PreparedStatement bulkPs = null;
910 if (bulkArtifactsCount == 0) {
917 StringBuilder sql =
new StringBuilder();
918 sql.append(
"INSERT INTO ");
919 sql.append(tableName);
920 sql.append(
" (case_id, data_source_id, value, file_path, known_status, comment) ");
921 sql.append(
"VALUES ((SELECT id FROM cases WHERE case_uid=? LIMIT 1), ");
922 sql.append(
"(SELECT id FROM data_sources WHERE device_id=? LIMIT 1), ?, ?, ?, ?) ");
925 bulkPs = conn.prepareStatement(sql.toString());
927 Collection<CorrelationAttribute> eamArtifacts = bulkArtifacts.get(type.getDbTableName());
929 List<CorrelationAttributeInstance> eamInstances = eamArtifact.getInstances();
932 if(! eamArtifact.getCorrelationValue().isEmpty()){
933 bulkPs.setString(1, eamInstance.getCorrelationCase().getCaseUUID());
934 bulkPs.setString(2, eamInstance.getCorrelationDataSource().getDeviceID());
935 bulkPs.setString(3, eamArtifact.getCorrelationValue());
936 bulkPs.setString(4, eamInstance.getFilePath());
937 bulkPs.setByte(5, eamInstance.getKnownStatus().getFileKnownValue());
938 if (
"".equals(eamInstance.getComment())) {
939 bulkPs.setNull(6, Types.INTEGER);
941 bulkPs.setString(6, eamInstance.getComment());
948 bulkPs.executeBatch();
949 bulkArtifacts.get(type.getDbTableName()).clear();
953 bulkArtifactsCount = 0;
955 }
catch (SQLException ex) {
956 throw new EamDbException(
"Error inserting bulk artifacts.", ex);
970 if (cases.isEmpty()) {
975 PreparedStatement bulkPs = null;
977 String sql =
"INSERT INTO cases(case_uid, org_id, case_name, creation_date, case_number, "
978 +
"examiner_name, examiner_email, examiner_phone, notes) "
979 +
"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) "
981 bulkPs = conn.prepareStatement(sql);
984 bulkPs.setString(1, eamCase.getCaseUUID());
985 if (null == eamCase.getOrg()) {
986 bulkPs.setNull(2, Types.INTEGER);
988 bulkPs.setInt(2, eamCase.getOrg().getOrgID());
990 bulkPs.setString(3, eamCase.getDisplayName());
991 bulkPs.setString(4, eamCase.getCreationDate());
993 if (
"".equals(eamCase.getCaseNumber())) {
994 bulkPs.setNull(5, Types.INTEGER);
996 bulkPs.setString(5, eamCase.getCaseNumber());
998 if (
"".equals(eamCase.getExaminerName())) {
999 bulkPs.setNull(6, Types.INTEGER);
1001 bulkPs.setString(6, eamCase.getExaminerName());
1003 if (
"".equals(eamCase.getExaminerEmail())) {
1004 bulkPs.setNull(7, Types.INTEGER);
1006 bulkPs.setString(7, eamCase.getExaminerEmail());
1008 if (
"".equals(eamCase.getExaminerPhone())) {
1009 bulkPs.setNull(8, Types.INTEGER);
1011 bulkPs.setString(8, eamCase.getExaminerPhone());
1013 if (
"".equals(eamCase.getNotes())) {
1014 bulkPs.setNull(9, Types.INTEGER);
1016 bulkPs.setString(9, eamCase.getNotes());
1024 if (counter >= bulkArtifactsThreshold) {
1025 bulkPs.executeBatch();
1030 bulkPs.executeBatch();
1031 }
catch (SQLException ex) {
1052 throw new EamDbException(
"Error: Artifact must have exactly one (1) Artifact Instance to set as notable.");
1055 List<CorrelationAttributeInstance> eamInstances = eamArtifact.
getInstances();
1058 PreparedStatement preparedUpdate = null;
1059 PreparedStatement preparedQuery = null;
1060 ResultSet resultSet = null;
1064 StringBuilder sqlQuery =
new StringBuilder();
1065 sqlQuery.append(
"SELECT id FROM ");
1066 sqlQuery.append(tableName);
1067 sqlQuery.append(
" WHERE case_id=(SELECT id FROM cases WHERE case_uid=?) ");
1068 sqlQuery.append(
"AND data_source_id=(SELECT id FROM data_sources WHERE device_id=?) ");
1069 sqlQuery.append(
"AND value=? ");
1070 sqlQuery.append(
"AND file_path=?");
1072 StringBuilder sqlUpdate =
new StringBuilder();
1073 sqlUpdate.append(
"UPDATE ");
1074 sqlUpdate.append(tableName);
1075 sqlUpdate.append(
" SET known_status=?, comment=? ");
1076 sqlUpdate.append(
"WHERE id=?");
1079 preparedQuery = conn.prepareStatement(sqlQuery.toString());
1083 preparedQuery.setString(4, eamInstance.
getFilePath());
1084 resultSet = preparedQuery.executeQuery();
1085 if (resultSet.next()) {
1086 int instance_id = resultSet.getInt(
"id");
1087 preparedUpdate = conn.prepareStatement(sqlUpdate.toString());
1089 preparedUpdate.setByte(1, knownStatus.getFileKnownValue());
1094 preparedUpdate.setNull(2, Types.INTEGER);
1096 preparedUpdate.setString(2, eamInstance.
getComment());
1098 preparedUpdate.setInt(3, instance_id);
1100 preparedUpdate.executeUpdate();
1116 eamArtifact.
getInstances().get(0).setKnownStatus(knownStatus);
1120 }
catch (SQLException ex) {
1121 throw new EamDbException(
"Error getting/setting artifact instance knownStatus=" + knownStatus.getName(), ex);
1143 List<CorrelationAttributeInstance> artifactInstances =
new ArrayList<>();
1146 PreparedStatement preparedStatement = null;
1147 ResultSet resultSet = null;
1150 StringBuilder sql =
new StringBuilder();
1151 sql.append(
"SELECT cases.case_name, cases.case_uid, data_sources.name, device_id, file_path, known_status, comment FROM ");
1152 sql.append(tableName);
1153 sql.append(
" LEFT JOIN cases ON ");
1154 sql.append(tableName);
1155 sql.append(
".case_id=cases.id");
1156 sql.append(
" LEFT JOIN data_sources ON ");
1157 sql.append(tableName);
1158 sql.append(
".data_source_id=data_sources.id");
1159 sql.append(
" WHERE value=? AND known_status=?");
1162 preparedStatement = conn.prepareStatement(sql.toString());
1163 preparedStatement.setString(1, value);
1164 preparedStatement.setByte(2, TskData.FileKnown.BAD.getFileKnownValue());
1165 resultSet = preparedStatement.executeQuery();
1166 while (resultSet.next()) {
1168 artifactInstances.add(artifactInstance);
1170 }
catch (SQLException ex) {
1171 throw new EamDbException(
"Error getting notable artifact instances.", ex);
1178 return artifactInstances;
1193 Long badInstances = 0L;
1194 PreparedStatement preparedStatement = null;
1195 ResultSet resultSet = null;
1198 StringBuilder sql =
new StringBuilder();
1199 sql.append(
"SELECT count(*) FROM ");
1200 sql.append(tableName);
1201 sql.append(
" WHERE value=? AND known_status=?");
1204 preparedStatement = conn.prepareStatement(sql.toString());
1205 preparedStatement.setString(1, value);
1206 preparedStatement.setByte(2, TskData.FileKnown.BAD.getFileKnownValue());
1207 resultSet = preparedStatement.executeQuery();
1209 badInstances = resultSet.getLong(1);
1210 }
catch (SQLException ex) {
1211 throw new EamDbException(
"Error getting count of notable artifact instances.", ex);
1218 return badInstances;
1237 Collection<String> caseNames =
new LinkedHashSet<>();
1239 PreparedStatement preparedStatement = null;
1240 ResultSet resultSet = null;
1243 StringBuilder sql =
new StringBuilder();
1244 sql.append(
"SELECT DISTINCT case_name FROM ");
1245 sql.append(tableName);
1246 sql.append(
" INNER JOIN cases ON ");
1247 sql.append(tableName);
1248 sql.append(
".case_id=cases.id WHERE ");
1249 sql.append(tableName);
1250 sql.append(
".value=? AND ");
1251 sql.append(tableName);
1252 sql.append(
".known_status=?");
1255 preparedStatement = conn.prepareStatement(sql.toString());
1256 preparedStatement.setString(1, value);
1257 preparedStatement.setByte(2, TskData.FileKnown.BAD.getFileKnownValue());
1258 resultSet = preparedStatement.executeQuery();
1259 while (resultSet.next()) {
1260 caseNames.add(resultSet.getString(
"case_name"));
1262 }
catch (SQLException ex) {
1263 throw new EamDbException(
"Error getting notable artifact instances.", ex);
1270 return caseNames.stream().collect(Collectors.toList());
1291 Long badInstances = 0L;
1292 PreparedStatement preparedStatement = null;
1293 ResultSet resultSet = null;
1294 String sql =
"SELECT count(*) FROM %s WHERE value=? AND known_status=?";
1298 preparedStatement.setString(1, value);
1299 preparedStatement.setByte(2, TskData.FileKnown.BAD.getFileKnownValue());
1300 resultSet = preparedStatement.executeQuery();
1302 badInstances = resultSet.getLong(1);
1303 }
catch (SQLException ex) {
1304 throw new EamDbException(
"Error determining if artifact is notable by reference.", ex);
1311 return 0 < badInstances;
1325 PreparedStatement preparedStatement = null;
1326 String sql =
"INSERT INTO organizations(org_name, poc_name, poc_email, poc_phone) VALUES (?, ?, ?, ?)";
1329 preparedStatement = conn.prepareStatement(sql);
1330 preparedStatement.setString(1, eamOrg.getName());
1331 preparedStatement.setString(2, eamOrg.getPocName());
1332 preparedStatement.setString(3, eamOrg.getPocEmail());
1333 preparedStatement.setString(4, eamOrg.getPocPhone());
1335 preparedStatement.executeUpdate();
1336 }
catch (SQLException ex) {
1337 throw new EamDbException(
"Error inserting new organization.", ex);
1355 List<EamOrganization> orgs =
new ArrayList<>();
1356 PreparedStatement preparedStatement = null;
1357 ResultSet resultSet = null;
1358 String sql =
"SELECT * FROM organizations";
1361 preparedStatement = conn.prepareStatement(sql);
1362 resultSet = preparedStatement.executeQuery();
1363 while (resultSet.next()) {
1368 }
catch (SQLException ex) {
1369 throw new EamDbException(
"Error getting all organizations.", ex);
1390 PreparedStatement preparedStatement = null;
1391 ResultSet resultSet = null;
1392 String sql =
"SELECT * FROM organizations WHERE id=?";
1395 preparedStatement = conn.prepareStatement(sql);
1396 preparedStatement.setInt(1, orgID);
1397 resultSet = preparedStatement.executeQuery();
1401 }
catch (SQLException ex) {
1402 throw new EamDbException(
"Error getting organization by id.", ex);
1423 PreparedStatement preparedStatement1 = null;
1424 PreparedStatement preparedStatement2 = null;
1425 ResultSet resultSet = null;
1426 String sql1 =
"INSERT INTO reference_sets(org_id, set_name, version, import_date) VALUES (?, ?, ?, ?)";
1427 String sql2 =
"SELECT id FROM reference_sets WHERE org_id=? AND set_name=? AND version=? AND import_date=? LIMIT 1";
1430 preparedStatement1 = conn.prepareStatement(sql1);
1431 preparedStatement1.setInt(1, eamGlobalSet.getOrgID());
1432 preparedStatement1.setString(2, eamGlobalSet.getSetName());
1433 preparedStatement1.setString(3, eamGlobalSet.getVersion());
1434 preparedStatement1.setString(4, eamGlobalSet.getImportDate().toString());
1436 preparedStatement1.executeUpdate();
1438 preparedStatement2 = conn.prepareStatement(sql2);
1439 preparedStatement2.setInt(1, eamGlobalSet.getOrgID());
1440 preparedStatement2.setString(2, eamGlobalSet.getSetName());
1441 preparedStatement2.setString(3, eamGlobalSet.getVersion());
1442 preparedStatement2.setString(4, eamGlobalSet.getImportDate().toString());
1444 resultSet = preparedStatement2.executeQuery();
1446 return resultSet.getInt(
"id");
1448 }
catch (SQLException ex) {
1471 PreparedStatement preparedStatement1 = null;
1472 ResultSet resultSet = null;
1473 String sql1 =
"SELECT * FROM reference_sets WHERE id=?";
1476 preparedStatement1 = conn.prepareStatement(sql1);
1477 preparedStatement1.setInt(1, referenceSetID);
1478 resultSet = preparedStatement1.executeQuery();
1482 }
catch (SQLException ex) {
1483 throw new EamDbException(
"Error getting reference set by id.", ex);
1504 PreparedStatement preparedStatement = null;
1506 String sql =
"INSERT INTO %s(reference_set_id, value, known_status, comment) VALUES (?, ?, ?, ?)";
1510 preparedStatement.setInt(1, eamGlobalFileInstance.
getGlobalSetID());
1511 preparedStatement.setString(2, eamGlobalFileInstance.
getMD5Hash());
1512 preparedStatement.setByte(3, eamGlobalFileInstance.
getKnownStatus().getFileKnownValue());
1513 preparedStatement.setString(4, eamGlobalFileInstance.
getComment());
1514 preparedStatement.executeUpdate();
1515 }
catch (SQLException ex) {
1516 throw new EamDbException(
"Error inserting new reference instance into reference_ table.", ex);
1532 PreparedStatement bulkPs = null;
1534 conn.setAutoCommit(
false);
1537 String sql =
"INSERT INTO %s(reference_set_id, value, known_status, comment) VALUES (?, ?, ?, ?) "
1543 bulkPs.setInt(1, globalInstance.getGlobalSetID());
1544 bulkPs.setString(2, globalInstance.getMD5Hash());
1545 bulkPs.setByte(3, globalInstance.getKnownStatus().getFileKnownValue());
1546 bulkPs.setString(4, globalInstance.getComment());
1550 bulkPs.executeBatch();
1552 }
catch (SQLException ex) {
1555 }
catch (SQLException ex2){
1558 throw new EamDbException(
"Error inserting bulk artifacts.", ex);
1579 List<EamGlobalFileInstance> globalFileInstances =
new ArrayList<>();
1580 PreparedStatement preparedStatement1 = null;
1581 ResultSet resultSet = null;
1582 String sql1 =
"SELECT * FROM %s WHERE value=?";
1586 preparedStatement1.setString(1, aValue);
1587 resultSet = preparedStatement1.executeQuery();
1588 while (resultSet.next()) {
1591 return globalFileInstances;
1593 }
catch (SQLException ex) {
1594 throw new EamDbException(
"Error getting reference instances by type and value.", ex);
1615 PreparedStatement preparedStatement = null;
1616 PreparedStatement preparedStatementQuery = null;
1617 ResultSet resultSet = null;
1622 if (-1 == newType.getId()) {
1623 insertSql =
"INSERT INTO correlation_types(display_name, db_table_name, supported, enabled) VALUES (?, ?, ?, ?)";
1625 insertSql =
"INSERT INTO correlation_types(id, display_name, db_table_name, supported, enabled) VALUES (?, ?, ?, ?, ?)";
1627 querySql =
"SELECT id FROM correlation_types WHERE display_name=? AND db_table_name=?";
1630 preparedStatement = conn.prepareStatement(insertSql);
1632 if (-1 == newType.getId()) {
1633 preparedStatement.setString(1, newType.getDisplayName());
1634 preparedStatement.setString(2, newType.getDbTableName());
1635 preparedStatement.setInt(3, newType.isSupported() ? 1 : 0);
1636 preparedStatement.setInt(4, newType.isEnabled() ? 1 : 0);
1638 preparedStatement.setInt(1, newType.getId());
1639 preparedStatement.setString(2, newType.getDisplayName());
1640 preparedStatement.setString(3, newType.getDbTableName());
1641 preparedStatement.setInt(4, newType.isSupported() ? 1 : 0);
1642 preparedStatement.setInt(5, newType.isEnabled() ? 1 : 0);
1645 preparedStatement.executeUpdate();
1647 preparedStatementQuery = conn.prepareStatement(querySql);
1648 preparedStatementQuery.setString(1, newType.getDisplayName());
1649 preparedStatementQuery.setString(2, newType.getDbTableName());
1651 resultSet = preparedStatementQuery.executeQuery();
1652 if (resultSet.next()) {
1654 typeId = correlationType.getId();
1656 }
catch (SQLException ex) {
1657 throw new EamDbException(
"Error inserting new correlation type.", ex);
1673 PreparedStatement preparedStatement = null;
1674 ResultSet resultSet = null;
1675 String sql =
"SELECT * FROM correlation_types";
1678 preparedStatement = conn.prepareStatement(sql);
1679 resultSet = preparedStatement.executeQuery();
1680 while (resultSet.next()) {
1685 }
catch (SQLException ex) {
1686 throw new EamDbException(
"Error getting all correlation types.", ex);
1708 PreparedStatement preparedStatement = null;
1709 ResultSet resultSet = null;
1710 String sql =
"SELECT * FROM correlation_types WHERE enabled=1";
1713 preparedStatement = conn.prepareStatement(sql);
1714 resultSet = preparedStatement.executeQuery();
1715 while (resultSet.next()) {
1720 }
catch (SQLException ex) {
1721 throw new EamDbException(
"Error getting enabled correlation types.", ex);
1743 PreparedStatement preparedStatement = null;
1744 ResultSet resultSet = null;
1745 String sql =
"SELECT * FROM correlation_types WHERE supported=1";
1748 preparedStatement = conn.prepareStatement(sql);
1749 resultSet = preparedStatement.executeQuery();
1750 while (resultSet.next()) {
1755 }
catch (SQLException ex) {
1756 throw new EamDbException(
"Error getting supported correlation types.", ex);
1775 PreparedStatement preparedStatement = null;
1776 String sql =
"UPDATE correlation_types SET display_name=?, db_table_name=?, supported=?, enabled=? WHERE id=?";
1779 preparedStatement = conn.prepareStatement(sql);
1780 preparedStatement.setString(1, aType.getDisplayName());
1781 preparedStatement.setString(2, aType.getDbTableName());
1782 preparedStatement.setInt(3, aType.isSupported() ? 1 : 0);
1783 preparedStatement.setInt(4, aType.isEnabled() ? 1 : 0);
1784 preparedStatement.setInt(5, aType.getId());
1785 preparedStatement.executeUpdate();
1787 }
catch (SQLException ex) {
1788 throw new EamDbException(
"Error updating correlation type.", ex);
1810 PreparedStatement preparedStatement = null;
1811 ResultSet resultSet = null;
1812 String sql =
"SELECT * FROM correlation_types WHERE id=?";
1815 preparedStatement = conn.prepareStatement(sql);
1816 preparedStatement.setInt(1, typeId);
1817 resultSet = preparedStatement.executeQuery();
1822 }
catch (SQLException ex) {
1823 throw new EamDbException(
"Error getting correlation type by id.", ex);
1842 if (null == resultSet) {
1848 resultSet.getInt(
"org_id");
1849 if (!resultSet.wasNull()) {
1852 resultSet.getString(
"org_name"),
1853 resultSet.getString(
"poc_name"),
1854 resultSet.getString(
"poc_email"),
1855 resultSet.getString(
"poc_phone"));
1865 eamCase.
setNotes(resultSet.getString(
"notes"));
1871 if (null == resultSet) {
1876 resultSet.getInt(
"id"),
1877 resultSet.getString(
"device_id"),
1878 resultSet.getString(
"name")
1881 return eamDataSource;
1885 if (null == resultSet) {
1890 resultSet.getInt(
"id"),
1891 resultSet.getString(
"display_name"),
1892 resultSet.getString(
"db_table_name"),
1893 resultSet.getBoolean(
"supported"),
1894 resultSet.getBoolean(
"enabled")
1897 return eamArtifactType;
1911 if (null == resultSet) {
1915 new CorrelationCase(resultSet.getString(
"case_uid"), resultSet.getString(
"case_name")),
1917 resultSet.getString(
"file_path"),
1918 resultSet.getString(
"comment"),
1919 TskData.FileKnown.valueOf(resultSet.getByte(
"known_status")),
1923 return eamArtifactInstance;
1927 if (null == resultSet) {
1932 resultSet.getInt(
"id"),
1933 resultSet.getString(
"org_name"),
1934 resultSet.getString(
"poc_name"),
1935 resultSet.getString(
"poc_email"),
1936 resultSet.getString(
"poc_phone")
1939 return eamOrganization;
1943 if (null == resultSet) {
1948 resultSet.getInt(
"id"),
1949 resultSet.getInt(
"org_id"),
1950 resultSet.getString(
"set_name"),
1951 resultSet.getString(
"version"),
1952 LocalDate.parse(resultSet.getString(
"import_date"))
1955 return eamGlobalSet;
1959 if (null == resultSet) {
1964 resultSet.getInt(
"id"),
1965 resultSet.getInt(
"reference_set_id"),
1966 resultSet.getString(
"value"),
1967 TskData.FileKnown.valueOf(resultSet.getByte(
"known_status")),
1968 resultSet.getString(
"comment")
1971 return eamGlobalFileInstance;
String getCorrelationValue()
final List< String > badTags
void bulkInsertCases(List< CorrelationCase > cases)
TskData.FileKnown getKnownStatus()
int getFrequencyPercentage(CorrelationAttribute corAttr)
void newCase(CorrelationCase eamCase)
List< CorrelationAttributeInstance > getArtifactInstancesByTypeValue(CorrelationAttribute.Type aType, String value)
CorrelationCase newCase(Case autopsyCase)
void addArtifact(CorrelationAttribute eamArtifact)
CorrelationCase getEamCaseFromResultSet(ResultSet resultSet)
void setCreationDate(String creationDate)
List< EamOrganization > getOrganizations()
Type getCorrelationType()
static final int FILES_TYPE_ID
abstract Connection connect()
void updateCorrelationType(CorrelationAttribute.Type aType)
CorrelationDataSource getDataSourceDetails(String dataSourceDeviceId)
int newReferencelSet(EamGlobalSet eamGlobalSet)
List< EamGlobalFileInstance > getReferenceInstancesByTypeValue(CorrelationAttribute.Type aType, String aValue)
void prepareBulkArtifact(CorrelationAttribute eamArtifact)
static void closeResultSet(ResultSet resultSet)
Long getCountArtifactInstancesByCaseDataSource(String caseUUID, String dataSourceID)
Long getCountArtifactInstancesKnownBad(CorrelationAttribute.Type aType, String value)
List< String > getListCasesHavingArtifactInstancesKnownBad(CorrelationAttribute.Type aType, String value)
String getDbInfo(String name)
Long getCountUniqueDataSources()
List< CorrelationDataSource > getDataSources()
boolean isArtifactlKnownBadByReference(CorrelationAttribute.Type aType, String value)
int bulkArtifactsThreshold
void setExaminerPhone(String examinerPhone)
void setExaminerName(String examinerName)
void setCaseNumber(String caseNumber)
final List< CorrelationAttribute.Type > DEFAULT_CORRELATION_TYPES
abstract String getConflictClause()
CorrelationAttribute.Type getCorrelationTypeById(int typeId)
static String correlationTypeToReferenceTableName(CorrelationAttribute.Type type)
EamOrganization getOrganizationByID(int orgID)
void setOrg(EamOrganization org)
List< CorrelationAttributeInstance > getArtifactInstancesKnownBad(CorrelationAttribute.Type aType, String value)
List< CorrelationAttributeInstance > getArtifactInstancesByPath(CorrelationAttribute.Type aType, String filePath)
void setNotes(String notes)
List< CorrelationAttribute.Type > getDefinedCorrelationTypes()
static void closeConnection(Connection conn)
EamGlobalSet getReferenceSetByID(int referenceSetID)
void addReferenceInstance(EamGlobalFileInstance eamGlobalFileInstance, CorrelationAttribute.Type correlationType)
void setBadTags(List< String > tags)
void setArtifactInstanceKnownStatus(CorrelationAttribute eamArtifact, TskData.FileKnown knownStatus)
CorrelationAttribute.Type getCorrelationTypeFromResultSet(ResultSet resultSet)
void newOrganization(EamOrganization eamOrg)
List< CorrelationAttributeInstance > getInstances()
CorrelationAttributeInstance getEamArtifactInstanceFromResultSet(ResultSet resultSet)
List< String > getBadTags()
void updateCase(CorrelationCase eamCase)
EamGlobalSet getEamGlobalSetFromResultSet(ResultSet resultSet)
EamOrganization getEamOrganizationFromResultSet(ResultSet resultSet)
CorrelationDataSource getCorrelationDataSource()
void bulkInsertReferenceTypeEntries(Set< EamGlobalFileInstance > globalInstances, CorrelationAttribute.Type contentType)
List< CorrelationAttribute.Type > getSupportedCorrelationTypes()
Long getCountArtifactInstancesByTypeValue(CorrelationAttribute.Type aType, String value)
CorrelationCase getCaseByUUID(String caseUUID)
static String correlationTypeToInstanceTableName(CorrelationAttribute.Type type)
Long getCountUniqueCaseDataSourceTuplesHavingTypeValue(CorrelationAttribute.Type aType, String value)
CorrelationDataSource getEamDataSourceFromResultSet(ResultSet resultSet)
void bulkInsertArtifacts()
List< CorrelationAttribute.Type > getEnabledCorrelationTypes()
List< CorrelationCase > getCases()
void newDataSource(CorrelationDataSource eamDataSource)
synchronized static Logger getLogger(String name)
static List< CorrelationAttribute.Type > getDefaultCorrelationTypes()
int newCorrelationType(CorrelationAttribute.Type newType)
EamGlobalFileInstance getEamGlobalFileInstanceFromResultSet(ResultSet resultSet)
CorrelationCase getCorrelationCase()
void newDbInfo(String name, String value)
static EamOrganization getDefault()
static final Logger LOGGER
void setExaminerEmail(String examinerEmail)
final Map< String, Collection< CorrelationAttribute > > bulkArtifacts
static void closePreparedStatement(PreparedStatement preparedStatement)
void updateDbInfo(String name, String value)