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.Statement;
32 import java.sql.Types;
33 import java.time.LocalDate;
34 import java.util.HashMap;
37 import java.util.logging.Level;
43 import org.
sleuthkit.datamodel.CaseDbSchemaVersionNumber;
51 abstract class AbstractSqlEamDb
implements EamDb {
57 private int bulkArtifactsCount;
58 protected int bulkArtifactsThreshold;
59 private final Map<String, Collection<CorrelationAttribute>> bulkArtifacts;
63 static final int DEFAULT_BULK_THRESHHOLD = 1000;
71 bulkArtifactsCount = 0;
72 bulkArtifacts =
new HashMap<>();
75 defaultCorrelationTypes.forEach((type) -> {
76 bulkArtifacts.put(type.getDbTableName(),
new ArrayList<>());
94 public void newDbInfo(String name, String value)
throws EamDbException {
95 Connection conn = connect();
97 PreparedStatement preparedStatement = null;
98 String sql =
"INSERT INTO db_info (name, value) VALUES (?, ?) "
99 + getConflictClause();
101 preparedStatement = conn.prepareStatement(sql);
102 preparedStatement.setString(1, name);
103 preparedStatement.setString(2, value);
104 preparedStatement.executeUpdate();
105 }
catch (SQLException ex) {
106 throw new EamDbException(
"Error adding new name/value pair to db_info.", ex);
124 public String getDbInfo(String name)
throws EamDbException {
125 Connection conn = connect();
127 PreparedStatement preparedStatement = null;
128 ResultSet resultSet = null;
130 String sql =
"SELECT value FROM db_info WHERE name=?";
132 preparedStatement = conn.prepareStatement(sql);
133 preparedStatement.setString(1, name);
134 resultSet = preparedStatement.executeQuery();
135 if (resultSet.next()) {
136 value = resultSet.getString(
"value");
138 }
catch (SQLException ex) {
139 throw new EamDbException(
"Error getting value for name.", ex);
158 public void updateDbInfo(String name, String value)
throws EamDbException {
159 Connection conn = connect();
161 PreparedStatement preparedStatement = null;
162 String sql =
"UPDATE db_info SET value=? WHERE name=?";
164 preparedStatement = conn.prepareStatement(sql);
165 preparedStatement.setString(1, value);
166 preparedStatement.setString(2, name);
167 preparedStatement.executeUpdate();
168 }
catch (SQLException ex) {
169 throw new EamDbException(
"Error updating value for name.", ex);
190 if (cRCase != null) {
194 Connection conn = connect();
195 PreparedStatement preparedStatement = null;
197 String sql =
"INSERT INTO cases(case_uid, org_id, case_name, creation_date, case_number, "
198 +
"examiner_name, examiner_email, examiner_phone, notes) "
199 +
"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) "
200 + getConflictClause();
203 preparedStatement = conn.prepareStatement(sql);
205 preparedStatement.setString(1, eamCase.
getCaseUUID());
206 if (null == eamCase.
getOrg()) {
207 preparedStatement.setNull(2, Types.INTEGER);
214 preparedStatement.setNull(5, Types.INTEGER);
219 preparedStatement.setNull(6, Types.INTEGER);
224 preparedStatement.setNull(7, Types.INTEGER);
229 preparedStatement.setNull(8, Types.INTEGER);
233 if (
"".equals(eamCase.
getNotes())) {
234 preparedStatement.setNull(9, Types.INTEGER);
236 preparedStatement.setString(9, eamCase.
getNotes());
239 preparedStatement.executeUpdate();
240 }
catch (SQLException ex) {
241 throw new EamDbException(
"Error inserting new case.", ex);
258 if (autopsyCase == null) {
259 throw new EamDbException(
"Case is null");
273 return newCase(curCeCase);
278 return getCaseByUUID(autopsyCase.
getName());
287 public void updateCase(
CorrelationCase eamCase)
throws EamDbException {
288 if (eamCase == null) {
289 throw new EamDbException(
"Correlation case is null");
292 Connection conn = connect();
294 PreparedStatement preparedStatement = null;
295 String sql =
"UPDATE cases "
296 +
"SET org_id=?, case_name=?, creation_date=?, case_number=?, examiner_name=?, examiner_email=?, examiner_phone=?, notes=? "
297 +
"WHERE case_uid=?";
300 preparedStatement = conn.prepareStatement(sql);
302 if (null == eamCase.
getOrg()) {
303 preparedStatement.setNull(1, Types.INTEGER);
311 preparedStatement.setNull(4, Types.INTEGER);
316 preparedStatement.setNull(5, Types.INTEGER);
321 preparedStatement.setNull(6, Types.INTEGER);
326 preparedStatement.setNull(7, Types.INTEGER);
330 if (
"".equals(eamCase.
getNotes())) {
331 preparedStatement.setNull(8, Types.INTEGER);
333 preparedStatement.setString(8, eamCase.
getNotes());
336 preparedStatement.setString(9, eamCase.
getCaseUUID());
338 preparedStatement.executeUpdate();
339 }
catch (SQLException ex) {
340 throw new EamDbException(
"Error updating case.", ex);
355 public CorrelationCase getCaseByUUID(String caseUUID)
throws EamDbException {
358 Connection conn = connect();
361 PreparedStatement preparedStatement = null;
362 ResultSet resultSet = null;
364 String sql =
"SELECT cases.id as case_id, case_uid, case_name, creation_date, case_number, examiner_name, "
365 +
"examiner_email, examiner_phone, notes, organizations.id as org_id, org_name, poc_name, poc_email, poc_phone "
367 +
"LEFT JOIN organizations ON cases.org_id=organizations.id "
368 +
"WHERE case_uid=?";
371 preparedStatement = conn.prepareStatement(sql);
372 preparedStatement.setString(1, caseUUID);
373 resultSet = preparedStatement.executeQuery();
374 if (resultSet.next()) {
375 eamCaseResult = getEamCaseFromResultSet(resultSet);
377 }
catch (SQLException ex) {
378 throw new EamDbException(
"Error getting case details.", ex);
385 return eamCaseResult;
394 public List<CorrelationCase> getCases()
throws EamDbException {
395 Connection conn = connect();
397 List<CorrelationCase> cases =
new ArrayList<>();
399 PreparedStatement preparedStatement = null;
400 ResultSet resultSet = null;
402 String sql =
"SELECT cases.id as case_id, case_uid, case_name, creation_date, case_number, examiner_name, "
403 +
"examiner_email, examiner_phone, notes, organizations.id as org_id, org_name, poc_name, poc_email, poc_phone "
405 +
"LEFT JOIN organizations ON cases.org_id=organizations.id";
408 preparedStatement = conn.prepareStatement(sql);
409 resultSet = preparedStatement.executeQuery();
410 while (resultSet.next()) {
411 eamCaseResult = getEamCaseFromResultSet(resultSet);
412 cases.add(eamCaseResult);
414 }
catch (SQLException ex) {
415 throw new EamDbException(
"Error getting all cases.", ex);
433 throw new EamDbException(
"Case ID is -1");
434 }
else if (eamDataSource.getID() != -1) {
435 throw new EamDbException(
"Database ID is already set in object");
437 Connection conn = connect();
439 PreparedStatement preparedStatement = null;
441 String sql =
"INSERT INTO data_sources(device_id, case_id, name) VALUES (?, ?, ?) "
442 + getConflictClause();
445 preparedStatement = conn.prepareStatement(sql);
447 preparedStatement.setString(1, eamDataSource.
getDeviceID());
448 preparedStatement.setInt(2, eamDataSource.
getCaseID());
449 preparedStatement.setString(3, eamDataSource.
getName());
451 preparedStatement.executeUpdate();
452 }
catch (SQLException ex) {
453 throw new EamDbException(
"Error inserting new data source.", ex);
471 if (correlationCase == null) {
472 throw new EamDbException(
"Correlation case is null");
475 Connection conn = connect();
478 PreparedStatement preparedStatement = null;
479 ResultSet resultSet = null;
481 String sql =
"SELECT * FROM data_sources WHERE device_id=? AND case_id=?";
484 preparedStatement = conn.prepareStatement(sql);
485 preparedStatement.setString(1, dataSourceDeviceId);
486 preparedStatement.setInt(2, correlationCase.
getID());
487 resultSet = preparedStatement.executeQuery();
488 if (resultSet.next()) {
489 eamDataSourceResult = getEamDataSourceFromResultSet(resultSet);
491 }
catch (SQLException ex) {
492 throw new EamDbException(
"Error getting data source.", ex);
499 return eamDataSourceResult;
508 public List<CorrelationDataSource> getDataSources()
throws EamDbException {
509 Connection conn = connect();
511 List<CorrelationDataSource> dataSources =
new ArrayList<>();
513 PreparedStatement preparedStatement = null;
514 ResultSet resultSet = null;
516 String sql =
"SELECT * FROM data_sources";
519 preparedStatement = conn.prepareStatement(sql);
520 resultSet = preparedStatement.executeQuery();
521 while (resultSet.next()) {
522 eamDataSourceResult = getEamDataSourceFromResultSet(resultSet);
523 dataSources.add(eamDataSourceResult);
525 }
catch (SQLException ex) {
526 throw new EamDbException(
"Error getting all data sources.", ex);
544 if (eamArtifact == null) {
545 throw new EamDbException(
"CorrelationAttribute is null");
548 throw new EamDbException(
"Correlation type is null");
551 throw new EamDbException(
"Correlation value is null");
554 Connection conn = connect();
556 List<CorrelationAttributeInstance> eamInstances = eamArtifact.
getInstances();
557 PreparedStatement preparedStatement = null;
564 +
"(case_id, data_source_id, value, file_path, known_status, comment) "
565 +
"VALUES ((SELECT id FROM cases WHERE case_uid=? LIMIT 1), "
566 +
"(SELECT id FROM data_sources WHERE device_id=? AND case_id=? LIMIT 1), ?, ?, ?, ?) "
567 + getConflictClause();
570 preparedStatement = conn.prepareStatement(sql);
573 if (eamInstance.getCorrelationCase() == null) {
574 throw new EamDbException(
"CorrelationAttributeInstance case is null");
576 if (eamInstance.getCorrelationDataSource() == null) {
577 throw new EamDbException(
"CorrelationAttributeInstance data source is null");
579 if (eamInstance.getKnownStatus() == null) {
580 throw new EamDbException(
"CorrelationAttributeInstance known status is null");
583 preparedStatement.setString(1, eamInstance.getCorrelationCase().getCaseUUID());
584 preparedStatement.setString(2, eamInstance.getCorrelationDataSource().getDeviceID());
585 preparedStatement.setInt(3, eamInstance.getCorrelationDataSource().getCaseID());
587 preparedStatement.setString(5, eamInstance.getFilePath());
588 preparedStatement.setByte(6, eamInstance.getKnownStatus().getFileKnownValue());
589 if (
"".equals(eamInstance.getComment())) {
590 preparedStatement.setNull(7, Types.INTEGER);
592 preparedStatement.setString(7, eamInstance.getComment());
595 preparedStatement.executeUpdate();
598 }
catch (SQLException ex) {
599 throw new EamDbException(
"Error inserting new artifact into artifacts table.", ex);
618 public List<CorrelationAttributeInstance> getArtifactInstancesByTypeValue(
CorrelationAttribute.
Type aType, String value)
throws EamDbException {
620 throw new EamDbException(
"Correlation type is null");
622 Connection conn = connect();
624 List<CorrelationAttributeInstance> artifactInstances =
new ArrayList<>();
627 PreparedStatement preparedStatement = null;
628 ResultSet resultSet = null;
634 +
".id, cases.case_name, cases.case_uid, data_sources.id AS data_source_id, data_sources.name, device_id, file_path, known_status, comment, data_sources.case_id FROM "
636 +
" LEFT JOIN cases ON "
638 +
".case_id=cases.id"
639 +
" LEFT JOIN data_sources ON "
641 +
".data_source_id=data_sources.id"
645 preparedStatement = conn.prepareStatement(sql);
646 preparedStatement.setString(1, value);
647 resultSet = preparedStatement.executeQuery();
648 while (resultSet.next()) {
649 artifactInstance = getEamArtifactInstanceFromResultSet(resultSet);
650 artifactInstances.add(artifactInstance);
652 }
catch (SQLException ex) {
653 throw new EamDbException(
"Error getting artifact instances by artifactType and artifactValue.", ex);
660 return artifactInstances;
675 public List<CorrelationAttributeInstance> getArtifactInstancesByPath(
CorrelationAttribute.
Type aType, String filePath)
throws EamDbException {
677 throw new EamDbException(
"Correlation type is null");
679 if (filePath == null) {
680 throw new EamDbException(
"Correlation value is null");
682 Connection conn = connect();
684 List<CorrelationAttributeInstance> artifactInstances =
new ArrayList<>();
687 PreparedStatement preparedStatement = null;
688 ResultSet resultSet = null;
694 +
".id, cases.case_name, cases.case_uid, data_sources.id AS data_source_id, data_sources.name, device_id, file_path, known_status, comment, data_sources.case_id FROM "
696 +
" LEFT JOIN cases ON "
698 +
".case_id=cases.id"
699 +
" LEFT JOIN data_sources ON "
701 +
".data_source_id=data_sources.id"
702 +
" WHERE file_path=?";
705 preparedStatement = conn.prepareStatement(sql);
706 preparedStatement.setString(1, filePath.toLowerCase());
707 resultSet = preparedStatement.executeQuery();
708 while (resultSet.next()) {
709 artifactInstance = getEamArtifactInstanceFromResultSet(resultSet);
710 artifactInstances.add(artifactInstance);
712 }
catch (SQLException ex) {
713 throw new EamDbException(
"Error getting artifact instances by artifactType and artifactValue.", ex);
720 return artifactInstances;
734 public Long getCountArtifactInstancesByTypeValue(
CorrelationAttribute.
Type aType, String value)
throws EamDbException {
736 throw new EamDbException(
"Correlation type is null");
739 throw new EamDbException(
"Correlation value is null");
742 Connection conn = connect();
744 Long instanceCount = 0L;
745 PreparedStatement preparedStatement = null;
746 ResultSet resultSet = null;
750 =
"SELECT count(*) FROM "
755 preparedStatement = conn.prepareStatement(sql);
756 preparedStatement.setString(1, value.toLowerCase());
757 resultSet = preparedStatement.executeQuery();
759 instanceCount = resultSet.getLong(1);
760 }
catch (SQLException ex) {
761 throw new EamDbException(
"Error getting count of artifact instances by artifactType and artifactValue.", ex);
768 return instanceCount;
773 if (corAttr == null) {
774 throw new EamDbException(
"CorrelationAttribute is null");
777 Double uniqueCaseDataSourceTuples = getCountUniqueDataSources().doubleValue();
778 Double commonalityPercentage = uniqueTypeValueTuples / uniqueCaseDataSourceTuples * 100;
779 return commonalityPercentage.intValue();
793 public Long getCountUniqueCaseDataSourceTuplesHavingTypeValue(
CorrelationAttribute.
Type aType, String value)
throws EamDbException {
795 throw new EamDbException(
"Correlation type is null");
798 Connection conn = connect();
800 Long instanceCount = 0L;
801 PreparedStatement preparedStatement = null;
802 ResultSet resultSet = null;
806 =
"SELECT count(*) FROM (SELECT DISTINCT case_id, data_source_id FROM "
808 +
" WHERE value=?) AS "
810 +
"_distinct_case_data_source_tuple";
813 preparedStatement = conn.prepareStatement(sql);
814 preparedStatement.setString(1, value);
815 resultSet = preparedStatement.executeQuery();
817 instanceCount = resultSet.getLong(1);
818 }
catch (SQLException ex) {
819 throw new EamDbException(
"Error counting unique caseDisplayName/dataSource tuples having artifactType and artifactValue.", ex);
826 return instanceCount;
830 public Long getCountUniqueDataSources()
throws EamDbException {
831 Connection conn = connect();
833 Long instanceCount = 0L;
834 PreparedStatement preparedStatement = null;
835 ResultSet resultSet = null;
837 String stmt =
"SELECT count(*) FROM data_sources";
840 preparedStatement = conn.prepareStatement(stmt);
841 resultSet = preparedStatement.executeQuery();
843 instanceCount = resultSet.getLong(1);
844 }
catch (SQLException ex) {
845 throw new EamDbException(
"Error counting data sources.", ex);
852 return instanceCount;
867 public Long getCountArtifactInstancesByCaseDataSource(String caseUUID, String dataSourceID)
throws EamDbException {
868 Connection conn = connect();
870 Long instanceCount = 0L;
872 PreparedStatement preparedStatement = null;
873 ResultSet resultSet = null;
876 String sql =
"SELECT 0 ";
882 +=
"+ (SELECT count(*) FROM "
884 +
" WHERE case_id=(SELECT id FROM cases WHERE case_uid=?) and data_source_id=(SELECT id FROM data_sources WHERE device_id=?))";
888 preparedStatement = conn.prepareStatement(sql);
890 for (
int i = 0; i < artifactTypes.size(); ++i) {
891 preparedStatement.setString(2 * i + 1, caseUUID);
892 preparedStatement.setString(2 * i + 2, dataSourceID);
895 resultSet = preparedStatement.executeQuery();
897 instanceCount = resultSet.getLong(1);
898 }
catch (SQLException ex) {
899 throw new EamDbException(
"Error counting artifact instances by caseName/dataSource.", ex);
906 return instanceCount;
920 throw new EamDbException(
"Correlation type is null");
923 synchronized (bulkArtifacts) {
925 bulkArtifactsCount++;
927 if (bulkArtifactsCount >= bulkArtifactsThreshold) {
928 bulkInsertArtifacts();
938 protected abstract String getConflictClause();
945 public void bulkInsertArtifacts()
throws EamDbException {
948 Connection conn = connect();
949 PreparedStatement bulkPs = null;
952 synchronized (bulkArtifacts) {
953 if (bulkArtifactsCount == 0) {
963 +
" (case_id, data_source_id, value, file_path, known_status, comment) "
964 +
"VALUES ((SELECT id FROM cases WHERE case_uid=? LIMIT 1), "
965 +
"(SELECT id FROM data_sources WHERE device_id=? AND case_id=? LIMIT 1), ?, ?, ?, ?) "
966 + getConflictClause();
968 bulkPs = conn.prepareStatement(sql);
970 Collection<CorrelationAttribute> eamArtifacts = bulkArtifacts.get(type.getDbTableName());
972 List<CorrelationAttributeInstance> eamInstances = eamArtifact.
getInstances();
977 if (eamInstance.getCorrelationCase() == null) {
978 throw new EamDbException(
"CorrelationAttributeInstance case is null");
980 if (eamInstance.getCorrelationDataSource() == null) {
981 throw new EamDbException(
"CorrelationAttributeInstance data source is null");
983 if (eamInstance.getKnownStatus() == null) {
984 throw new EamDbException(
"CorrelationAttributeInstance known status is null");
987 bulkPs.setString(1, eamInstance.getCorrelationCase().getCaseUUID());
988 bulkPs.setString(2, eamInstance.getCorrelationDataSource().getDeviceID());
989 bulkPs.setInt(3, eamInstance.getCorrelationDataSource().getCaseID());
991 bulkPs.setString(5, eamInstance.getFilePath());
992 bulkPs.setByte(6, eamInstance.getKnownStatus().getFileKnownValue());
993 if (
"".equals(eamInstance.getComment())) {
994 bulkPs.setNull(7, Types.INTEGER);
996 bulkPs.setString(7, eamInstance.getComment());
1003 bulkPs.executeBatch();
1004 bulkArtifacts.get(type.getDbTableName()).clear();
1011 bulkArtifactsCount = 0;
1013 }
catch (SQLException ex) {
1014 throw new EamDbException(
"Error inserting bulk artifacts.", ex);
1025 public void bulkInsertCases(List<CorrelationCase> cases)
throws EamDbException {
1026 if (cases == null) {
1027 throw new EamDbException(
"cases argument is null");
1030 if (cases.isEmpty()) {
1034 Connection conn = connect();
1037 PreparedStatement bulkPs = null;
1039 String sql =
"INSERT INTO cases(case_uid, org_id, case_name, creation_date, case_number, "
1040 +
"examiner_name, examiner_email, examiner_phone, notes) "
1041 +
"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) "
1042 + getConflictClause();
1043 bulkPs = conn.prepareStatement(sql);
1047 if (null == eamCase.
getOrg()) {
1048 bulkPs.setNull(2, Types.INTEGER);
1056 bulkPs.setNull(5, Types.INTEGER);
1061 bulkPs.setNull(6, Types.INTEGER);
1066 bulkPs.setNull(7, Types.INTEGER);
1071 bulkPs.setNull(8, Types.INTEGER);
1075 if (
"".equals(eamCase.
getNotes())) {
1076 bulkPs.setNull(9, Types.INTEGER);
1078 bulkPs.setString(9, eamCase.
getNotes());
1086 if (counter >= bulkArtifactsThreshold) {
1087 bulkPs.executeBatch();
1092 bulkPs.executeBatch();
1093 }
catch (SQLException ex) {
1094 throw new EamDbException(
"Error inserting bulk cases.", ex);
1111 public void updateAttributeInstanceComment(
CorrelationAttribute eamArtifact)
throws EamDbException {
1112 if (eamArtifact == null) {
1113 throw new EamDbException(
"CorrelationAttribute is null");
1118 if (eamInstance == null) {
1119 throw new EamDbException(
"CorrelationAttributeInstance is null");
1122 throw new EamDbException(
"Correlation case is null");
1125 throw new EamDbException(
"Correlation data source is null");
1128 Connection conn = connect();
1129 PreparedStatement preparedQuery = null;
1136 +
"WHERE case_id=(SELECT id FROM cases WHERE case_uid=?) "
1137 +
"AND data_source_id=(SELECT id FROM data_sources WHERE device_id=?) "
1139 +
"AND file_path=?";
1142 preparedQuery = conn.prepareStatement(sqlUpdate);
1143 preparedQuery.setString(1, eamInstance.
getComment());
1147 preparedQuery.setString(5, eamInstance.
getFilePath());
1148 preparedQuery.executeUpdate();
1149 }
catch (SQLException ex) {
1150 throw new EamDbException(
"Error getting/setting artifact instance comment=" + eamInstance.
getComment(), ex);
1176 throw new EamDbException(
"Correlation type is null");
1178 if (correlationCase == null) {
1179 throw new EamDbException(
"Correlation case is null");
1181 if (correlationDataSource == null) {
1182 throw new EamDbException(
"Correlation data source is null");
1184 if (value == null) {
1185 throw new EamDbException(
"Correlation value is null");
1187 if (filePath == null) {
1188 throw new EamDbException(
"Correlation file path is null");
1191 Connection conn = connect();
1193 PreparedStatement preparedStatement = null;
1194 ResultSet resultSet = null;
1200 =
"SELECT id, known_status, comment FROM "
1202 +
" WHERE case_id=?"
1203 +
" AND data_source_id=?"
1205 +
" AND file_path=?";
1207 preparedStatement = conn.prepareStatement(sql);
1208 preparedStatement.setInt(1, correlationCase.
getID());
1209 preparedStatement.setInt(2, correlationDataSource.getID());
1210 preparedStatement.setString(3, value.toLowerCase());
1211 preparedStatement.setString(4, filePath.toLowerCase());
1212 resultSet = preparedStatement.executeQuery();
1213 if (resultSet.next()) {
1214 int instanceId = resultSet.getInt(1);
1215 int knownStatus = resultSet.getInt(2);
1216 String comment = resultSet.getString(3);
1220 instanceId, correlationCase, correlationDataSource, filePath, comment, TskData.FileKnown.valueOf((byte) knownStatus));
1221 correlationAttribute.
addInstance(artifactInstance);
1223 }
catch (SQLException ex) {
1224 throw new EamDbException(
"Error getting notable artifact instances.", ex);
1231 return correlationAttribute;
1245 public void setArtifactInstanceKnownStatus(
CorrelationAttribute eamArtifact, TskData.FileKnown knownStatus)
throws EamDbException {
1246 if (eamArtifact == null) {
1247 throw new EamDbException(
"CorrelationAttribute is null");
1249 if (knownStatus == null) {
1250 throw new EamDbException(
"Known status is null");
1253 throw new EamDbException(
"Error: Artifact must have exactly one (1) Artifact Instance to set as notable.");
1256 List<CorrelationAttributeInstance> eamInstances = eamArtifact.
getInstances();
1260 throw new EamDbException(
"Correlation case is null");
1263 throw new EamDbException(
"Correlation data source is null");
1266 Connection conn = connect();
1268 PreparedStatement preparedUpdate = null;
1269 PreparedStatement preparedQuery = null;
1270 ResultSet resultSet = null;
1277 +
" WHERE case_id=(SELECT id FROM cases WHERE case_uid=?) "
1278 +
"AND data_source_id=(SELECT id FROM data_sources WHERE device_id=?) "
1280 +
"AND file_path=?";
1285 +
" SET known_status=?, comment=? "
1289 preparedQuery = conn.prepareStatement(sqlQuery);
1293 preparedQuery.setString(4, eamInstance.
getFilePath());
1294 resultSet = preparedQuery.executeQuery();
1295 if (resultSet.next()) {
1296 int instance_id = resultSet.getInt(
"id");
1297 preparedUpdate = conn.prepareStatement(sqlUpdate);
1299 preparedUpdate.setByte(1, knownStatus.getFileKnownValue());
1304 preparedUpdate.setNull(2, Types.INTEGER);
1306 preparedUpdate.setString(2, eamInstance.
getComment());
1308 preparedUpdate.setInt(3, instance_id);
1310 preparedUpdate.executeUpdate();
1318 if (null == correlationCaseWithId) {
1325 eamArtifact.
getInstances().get(0).setKnownStatus(knownStatus);
1326 addArtifact(eamArtifact);
1329 }
catch (SQLException ex) {
1330 throw new EamDbException(
"Error getting/setting artifact instance knownStatus=" + knownStatus.getName(), ex);
1349 public List<CorrelationAttributeInstance> getArtifactInstancesKnownBad(
CorrelationAttribute.
Type aType, String value)
throws EamDbException {
1350 if (aType == null) {
1351 throw new EamDbException(
"Correlation type is null");
1354 Connection conn = connect();
1356 List<CorrelationAttributeInstance> artifactInstances =
new ArrayList<>();
1359 PreparedStatement preparedStatement = null;
1360 ResultSet resultSet = null;
1366 +
".id, cases.case_name, cases.case_uid, data_sources.id AS data_source_id, data_sources.name, device_id, file_path, known_status, comment, data_sources.case_id FROM "
1368 +
" LEFT JOIN cases ON "
1370 +
".case_id=cases.id"
1371 +
" LEFT JOIN data_sources ON "
1373 +
".data_source_id=data_sources.id"
1374 +
" WHERE value=? AND known_status=?";
1377 preparedStatement = conn.prepareStatement(sql);
1378 preparedStatement.setString(1, value);
1379 preparedStatement.setByte(2, TskData.FileKnown.BAD.getFileKnownValue());
1380 resultSet = preparedStatement.executeQuery();
1381 while (resultSet.next()) {
1382 artifactInstance = getEamArtifactInstanceFromResultSet(resultSet);
1383 artifactInstances.add(artifactInstance);
1385 }
catch (SQLException ex) {
1386 throw new EamDbException(
"Error getting notable artifact instances.", ex);
1393 return artifactInstances;
1408 public List<CorrelationAttributeInstance> getArtifactInstancesKnownBad(
CorrelationAttribute.
Type aType)
throws EamDbException {
1409 if (aType == null) {
1410 throw new EamDbException(
"Correlation type is null");
1413 Connection conn = connect();
1415 List<CorrelationAttributeInstance> artifactInstances =
new ArrayList<>();
1418 PreparedStatement preparedStatement = null;
1419 ResultSet resultSet = null;
1423 =
"SELECT cases.case_name, cases.case_uid, data_sources.name, device_id, file_path, known_status, comment, data_sources.case_id FROM "
1425 +
" LEFT JOIN cases ON "
1427 +
".case_id=cases.id"
1428 +
" LEFT JOIN data_sources ON "
1430 +
".data_source_id=data_sources.id"
1431 +
" WHERE known_status=?"
1437 preparedStatement = conn.prepareStatement(sql);
1438 preparedStatement.setByte(1, TskData.FileKnown.BAD.getFileKnownValue());
1439 resultSet = preparedStatement.executeQuery();
1440 while (resultSet.next()) {
1441 artifactInstance = getEamArtifactInstanceFromResultSet(resultSet);
1442 artifactInstances.add(artifactInstance);
1444 }
catch (SQLException ex) {
1445 throw new EamDbException(
"Error getting notable artifact instances.", ex);
1452 return artifactInstances;
1464 public Long getCountArtifactInstancesKnownBad(
CorrelationAttribute.
Type aType, String value)
throws EamDbException {
1465 if (aType == null) {
1466 throw new EamDbException(
"Correlation type is null");
1469 Connection conn = connect();
1471 Long badInstances = 0L;
1472 PreparedStatement preparedStatement = null;
1473 ResultSet resultSet = null;
1477 =
"SELECT count(*) FROM "
1479 +
" WHERE value=? AND known_status=?";
1482 preparedStatement = conn.prepareStatement(sql);
1483 preparedStatement.setString(1, value);
1484 preparedStatement.setByte(2, TskData.FileKnown.BAD.getFileKnownValue());
1485 resultSet = preparedStatement.executeQuery();
1487 badInstances = resultSet.getLong(1);
1488 }
catch (SQLException ex) {
1489 throw new EamDbException(
"Error getting count of notable artifact instances.", ex);
1496 return badInstances;
1512 public List<String> getListCasesHavingArtifactInstancesKnownBad(
CorrelationAttribute.
Type aType, String value)
throws EamDbException {
1513 if (aType == null) {
1514 throw new EamDbException(
"Correlation type is null");
1517 Connection conn = connect();
1519 Collection<String> caseNames =
new LinkedHashSet<>();
1521 PreparedStatement preparedStatement = null;
1522 ResultSet resultSet = null;
1526 =
"SELECT DISTINCT case_name FROM "
1528 +
" INNER JOIN cases ON "
1530 +
".case_id=cases.id WHERE "
1534 +
".known_status=?";
1537 preparedStatement = conn.prepareStatement(sql);
1538 preparedStatement.setString(1, value);
1539 preparedStatement.setByte(2, TskData.FileKnown.BAD.getFileKnownValue());
1540 resultSet = preparedStatement.executeQuery();
1541 while (resultSet.next()) {
1542 caseNames.add(resultSet.getString(
"case_name"));
1544 }
catch (SQLException ex) {
1545 throw new EamDbException(
"Error getting notable artifact instances.", ex);
1552 return caseNames.stream().collect(Collectors.toList());
1563 public void deleteReferenceSet(
int referenceSetID)
throws EamDbException {
1564 deleteReferenceSetEntries(referenceSetID);
1565 deleteReferenceSetEntry(referenceSetID);
1575 private void deleteReferenceSetEntry(
int referenceSetID)
throws EamDbException {
1576 Connection conn = connect();
1578 PreparedStatement preparedStatement = null;
1579 String sql =
"DELETE FROM reference_sets WHERE id=?";
1582 preparedStatement = conn.prepareStatement(sql);
1583 preparedStatement.setInt(1, referenceSetID);
1584 preparedStatement.executeUpdate();
1585 }
catch (SQLException ex) {
1586 throw new EamDbException(
"Error deleting reference set " + referenceSetID, ex);
1601 private void deleteReferenceSetEntries(
int referenceSetID)
throws EamDbException {
1602 Connection conn = connect();
1604 PreparedStatement preparedStatement = null;
1605 String sql =
"DELETE FROM %s WHERE reference_set_id=?";
1611 preparedStatement = conn.prepareStatement(String.format(sql, fileTableName));
1612 preparedStatement.setInt(1, referenceSetID);
1613 preparedStatement.executeUpdate();
1614 }
catch (SQLException ex) {
1615 throw new EamDbException(
"Error deleting files from reference set " + referenceSetID, ex);
1636 public boolean referenceSetIsValid(
int referenceSetID, String setName, String version)
throws EamDbException {
1637 EamGlobalSet refSet = this.getReferenceSetByID(referenceSetID);
1638 if (refSet == null) {
1657 public boolean isFileHashInReferenceSet(String hash,
int referenceSetID)
throws EamDbException {
1671 public boolean isValueInReferenceSet(String value,
int referenceSetID,
int correlationTypeID)
throws EamDbException {
1673 Connection conn = connect();
1675 Long matchingInstances = 0L;
1676 PreparedStatement preparedStatement = null;
1677 ResultSet resultSet = null;
1678 String sql =
"SELECT count(*) FROM %s WHERE value=? AND reference_set_id=?";
1683 preparedStatement = conn.prepareStatement(String.format(sql, fileTableName));
1684 preparedStatement.setString(1, value);
1685 preparedStatement.setInt(2, referenceSetID);
1686 resultSet = preparedStatement.executeQuery();
1688 matchingInstances = resultSet.getLong(1);
1689 }
catch (SQLException ex) {
1690 throw new EamDbException(
"Error determining if value (" + value +
") is in reference set " + referenceSetID, ex);
1697 return 0 < matchingInstances;
1709 public boolean isArtifactKnownBadByReference(
CorrelationAttribute.
Type aType, String value)
throws EamDbException {
1710 if (aType == null) {
1711 throw new EamDbException(
"Correlation type is null");
1719 Connection conn = connect();
1721 Long badInstances = 0L;
1722 PreparedStatement preparedStatement = null;
1723 ResultSet resultSet = null;
1724 String sql =
"SELECT count(*) FROM %s WHERE value=? AND known_status=?";
1728 preparedStatement.setString(1, value);
1729 preparedStatement.setByte(2, TskData.FileKnown.BAD.getFileKnownValue());
1730 resultSet = preparedStatement.executeQuery();
1732 badInstances = resultSet.getLong(1);
1733 }
catch (SQLException ex) {
1734 throw new EamDbException(
"Error determining if artifact is notable by reference.", ex);
1741 return 0 < badInstances;
1753 throw new EamDbException(
"Correlation type is null");
1756 if (instanceTableCallback == null) {
1757 throw new EamDbException(
"Callback interface is null");
1760 Connection conn = connect();
1761 PreparedStatement preparedStatement = null;
1762 ResultSet resultSet = null;
1764 StringBuilder sql =
new StringBuilder();
1765 sql.append(
"select * from ");
1766 sql.append(tableName);
1769 preparedStatement = conn.prepareStatement(sql.toString());
1770 resultSet = preparedStatement.executeQuery();
1771 instanceTableCallback.
process(resultSet);
1772 }
catch (SQLException ex) {
1773 throw new EamDbException(
"Error getting all artifact instances from instances table", ex);
1783 if (eamOrg == null) {
1784 throw new EamDbException(
"EamOrganization is null");
1785 }
else if (eamOrg.
getOrgID() != -1) {
1786 throw new EamDbException(
"EamOrganization already has an ID");
1789 Connection conn = connect();
1790 ResultSet generatedKeys = null;
1791 PreparedStatement preparedStatement = null;
1792 String sql =
"INSERT INTO organizations(org_name, poc_name, poc_email, poc_phone) VALUES (?, ?, ?, ?) "
1793 + getConflictClause();
1796 preparedStatement = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
1797 preparedStatement.setString(1, eamOrg.
getName());
1798 preparedStatement.setString(2, eamOrg.
getPocName());
1799 preparedStatement.setString(3, eamOrg.
getPocEmail());
1800 preparedStatement.setString(4, eamOrg.
getPocPhone());
1802 preparedStatement.executeUpdate();
1803 generatedKeys = preparedStatement.getGeneratedKeys();
1804 if (generatedKeys.next()) {
1805 eamOrg.setOrgID((
int) generatedKeys.getLong(1));
1808 throw new SQLException(
"Creating user failed, no ID obtained.");
1810 }
catch (SQLException ex) {
1811 throw new EamDbException(
"Error inserting new organization.", ex);
1827 public List<EamOrganization> getOrganizations()
throws EamDbException {
1828 Connection conn = connect();
1830 List<EamOrganization> orgs =
new ArrayList<>();
1831 PreparedStatement preparedStatement = null;
1832 ResultSet resultSet = null;
1833 String sql =
"SELECT * FROM organizations";
1836 preparedStatement = conn.prepareStatement(sql);
1837 resultSet = preparedStatement.executeQuery();
1838 while (resultSet.next()) {
1839 orgs.add(getEamOrganizationFromResultSet(resultSet));
1843 }
catch (SQLException ex) {
1844 throw new EamDbException(
"Error getting all organizations.", ex);
1862 public EamOrganization getOrganizationByID(
int orgID)
throws EamDbException {
1863 Connection conn = connect();
1865 PreparedStatement preparedStatement = null;
1866 ResultSet resultSet = null;
1867 String sql =
"SELECT * FROM organizations WHERE id=?";
1870 preparedStatement = conn.prepareStatement(sql);
1871 preparedStatement.setInt(1, orgID);
1872 resultSet = preparedStatement.executeQuery();
1874 return getEamOrganizationFromResultSet(resultSet);
1876 }
catch (SQLException ex) {
1877 throw new EamDbException(
"Error getting organization by id.", ex);
1895 public EamOrganization getReferenceSetOrganization(
int referenceSetID)
throws EamDbException {
1897 EamGlobalSet globalSet = getReferenceSetByID(referenceSetID);
1898 if (globalSet == null) {
1899 throw new EamDbException(
"Reference set with ID " + referenceSetID +
" not found");
1901 return (getOrganizationByID(globalSet.
getOrgID()));
1913 throw new EamDbException(
"EamOrganization is null");
1915 throw new EamDbException(
"Organization has -1 row ID");
1928 public void updateOrganization(
EamOrganization updatedOrganization)
throws EamDbException {
1929 testArgument(updatedOrganization);
1931 Connection conn = connect();
1932 PreparedStatement preparedStatement = null;
1933 String sql =
"UPDATE organizations SET org_name = ?, poc_name = ?, poc_email = ?, poc_phone = ? WHERE id = ?";
1935 preparedStatement = conn.prepareStatement(sql);
1936 preparedStatement.setString(1, updatedOrganization.
getName());
1937 preparedStatement.setString(2, updatedOrganization.
getPocName());
1938 preparedStatement.setString(3, updatedOrganization.
getPocEmail());
1939 preparedStatement.setString(4, updatedOrganization.
getPocPhone());
1940 preparedStatement.setInt(5, updatedOrganization.
getOrgID());
1941 preparedStatement.executeUpdate();
1942 }
catch (SQLException ex) {
1943 throw new EamDbException(
"Error updating organization.", ex);
1951 public void deleteOrganization(
EamOrganization organizationToDelete)
throws EamDbException {
1952 testArgument(organizationToDelete);
1954 Connection conn = connect();
1955 PreparedStatement checkIfUsedStatement = null;
1956 ResultSet resultSet = null;
1957 String checkIfUsedSql =
"SELECT (select count(*) FROM cases WHERE org_id=?) + (select count(*) FROM reference_sets WHERE org_id=?)";
1958 PreparedStatement deleteOrgStatement = null;
1959 String deleteOrgSql =
"DELETE FROM organizations WHERE id=?";
1961 checkIfUsedStatement = conn.prepareStatement(checkIfUsedSql);
1962 checkIfUsedStatement.setInt(1, organizationToDelete.
getOrgID());
1963 checkIfUsedStatement.setInt(2, organizationToDelete.
getOrgID());
1964 resultSet = checkIfUsedStatement.executeQuery();
1966 if (resultSet.getLong(1) > 0) {
1967 throw new EamDbException(
"Can not delete organization which is currently in use by a case or reference set in the central repository.");
1969 deleteOrgStatement = conn.prepareStatement(deleteOrgSql);
1970 deleteOrgStatement.setInt(1, organizationToDelete.
getOrgID());
1971 deleteOrgStatement.executeUpdate();
1972 }
catch (SQLException ex) {
1973 throw new EamDbException(
"Error executing query when attempting to delete organization by id.", ex);
1992 public int newReferenceSet(
EamGlobalSet eamGlobalSet)
throws EamDbException {
1993 if (eamGlobalSet == null) {
1994 throw new EamDbException(
"EamGlobalSet is null");
1998 throw new EamDbException(
"File known status on the EamGlobalSet is null");
2001 if (eamGlobalSet.
getType() == null) {
2002 throw new EamDbException(
"Type on the EamGlobalSet is null");
2005 Connection conn = connect();
2007 PreparedStatement preparedStatement1 = null;
2008 PreparedStatement preparedStatement2 = null;
2009 ResultSet resultSet = null;
2010 String sql1 =
"INSERT INTO reference_sets(org_id, set_name, version, known_status, read_only, type, import_date) VALUES (?, ?, ?, ?, ?, ?, ?) "
2011 + getConflictClause();
2012 String sql2 =
"SELECT id FROM reference_sets WHERE org_id=? AND set_name=? AND version=? AND import_date=? LIMIT 1";
2015 preparedStatement1 = conn.prepareStatement(sql1);
2016 preparedStatement1.setInt(1, eamGlobalSet.
getOrgID());
2017 preparedStatement1.setString(2, eamGlobalSet.
getSetName());
2018 preparedStatement1.setString(3, eamGlobalSet.
getVersion());
2020 preparedStatement1.setBoolean(5, eamGlobalSet.
isReadOnly());
2021 preparedStatement1.setInt(6, eamGlobalSet.
getType().getId());
2022 preparedStatement1.setString(7, eamGlobalSet.
getImportDate().toString());
2024 preparedStatement1.executeUpdate();
2026 preparedStatement2 = conn.prepareStatement(sql2);
2027 preparedStatement2.setInt(1, eamGlobalSet.
getOrgID());
2028 preparedStatement2.setString(2, eamGlobalSet.
getSetName());
2029 preparedStatement2.setString(3, eamGlobalSet.
getVersion());
2030 preparedStatement2.setString(4, eamGlobalSet.
getImportDate().toString());
2032 resultSet = preparedStatement2.executeQuery();
2034 return resultSet.getInt(
"id");
2036 }
catch (SQLException ex) {
2037 throw new EamDbException(
"Error inserting new global set.", ex);
2056 public EamGlobalSet getReferenceSetByID(
int referenceSetID)
throws EamDbException {
2057 Connection conn = connect();
2059 PreparedStatement preparedStatement1 = null;
2060 ResultSet resultSet = null;
2061 String sql1 =
"SELECT * FROM reference_sets WHERE id=?";
2064 preparedStatement1 = conn.prepareStatement(sql1);
2065 preparedStatement1.setInt(1, referenceSetID);
2066 resultSet = preparedStatement1.executeQuery();
2067 if (resultSet.next()) {
2068 return getEamGlobalSetFromResultSet(resultSet);
2073 }
catch (SQLException ex) {
2074 throw new EamDbException(
"Error getting reference set by id.", ex);
2094 if (correlationType == null) {
2095 throw new EamDbException(
"Correlation type is null");
2098 List<EamGlobalSet> results =
new ArrayList<>();
2099 Connection conn = connect();
2101 PreparedStatement preparedStatement1 = null;
2102 ResultSet resultSet = null;
2103 String sql1 =
"SELECT * FROM reference_sets WHERE type=" + correlationType.getId();
2106 preparedStatement1 = conn.prepareStatement(sql1);
2107 resultSet = preparedStatement1.executeQuery();
2108 while (resultSet.next()) {
2109 results.add(getEamGlobalSetFromResultSet(resultSet));
2112 }
catch (SQLException ex) {
2113 throw new EamDbException(
"Error getting reference sets.", ex);
2134 throw new EamDbException(
"Known status of EamGlobalFileInstance is null");
2136 if (correlationType == null) {
2137 throw new EamDbException(
"Correlation type is null");
2140 Connection conn = connect();
2142 PreparedStatement preparedStatement = null;
2144 String sql =
"INSERT INTO %s(reference_set_id, value, known_status, comment) VALUES (?, ?, ?, ?) "
2145 + getConflictClause();
2149 preparedStatement.setInt(1, eamGlobalFileInstance.
getGlobalSetID());
2150 preparedStatement.setString(2, eamGlobalFileInstance.
getMD5Hash());
2151 preparedStatement.setByte(3, eamGlobalFileInstance.
getKnownStatus().getFileKnownValue());
2152 preparedStatement.setString(4, eamGlobalFileInstance.
getComment());
2153 preparedStatement.executeUpdate();
2154 }
catch (SQLException ex) {
2155 throw new EamDbException(
"Error inserting new reference instance into reference_ table.", ex);
2175 public boolean referenceSetExists(String referenceSetName, String version)
throws EamDbException {
2176 Connection conn = connect();
2178 PreparedStatement preparedStatement1 = null;
2179 ResultSet resultSet = null;
2180 String sql1 =
"SELECT * FROM reference_sets WHERE set_name=? AND version=?";
2183 preparedStatement1 = conn.prepareStatement(sql1);
2184 preparedStatement1.setString(1, referenceSetName);
2185 preparedStatement1.setString(2, version);
2186 resultSet = preparedStatement1.executeQuery();
2187 return (resultSet.next());
2189 }
catch (SQLException ex) {
2190 throw new EamDbException(
"Error testing whether reference set exists (name: " + referenceSetName
2191 +
" version: " + version, ex);
2205 public void bulkInsertReferenceTypeEntries(Set<EamGlobalFileInstance> globalInstances,
CorrelationAttribute.
Type contentType)
throws EamDbException {
2206 if (contentType == null) {
2207 throw new EamDbException(
"Correlation type is null");
2209 if (globalInstances == null) {
2210 throw new EamDbException(
"Null set of EamGlobalFileInstance");
2213 Connection conn = connect();
2215 PreparedStatement bulkPs = null;
2217 conn.setAutoCommit(
false);
2220 String sql =
"INSERT INTO %s(reference_set_id, value, known_status, comment) VALUES (?, ?, ?, ?) "
2221 + getConflictClause();
2226 if (globalInstance.getKnownStatus() == null) {
2227 throw new EamDbException(
"EamGlobalFileInstance with value " + globalInstance.getMD5Hash() +
" has null known status");
2230 bulkPs.setInt(1, globalInstance.getGlobalSetID());
2231 bulkPs.setString(2, globalInstance.getMD5Hash());
2232 bulkPs.setByte(3, globalInstance.getKnownStatus().getFileKnownValue());
2233 bulkPs.setString(4, globalInstance.getComment());
2237 bulkPs.executeBatch();
2239 }
catch (SQLException | EamDbException ex) {
2242 }
catch (SQLException ex2) {
2245 throw new EamDbException(
"Error inserting bulk artifacts.", ex);
2263 public List<EamGlobalFileInstance> getReferenceInstancesByTypeValue(
CorrelationAttribute.
Type aType, String aValue)
throws EamDbException {
2264 if (aType == null) {
2265 throw new EamDbException(
"Correlation type is null");
2268 Connection conn = connect();
2270 List<EamGlobalFileInstance> globalFileInstances =
new ArrayList<>();
2271 PreparedStatement preparedStatement1 = null;
2272 ResultSet resultSet = null;
2273 String sql1 =
"SELECT * FROM %s WHERE value=?";
2277 preparedStatement1.setString(1, aValue);
2278 resultSet = preparedStatement1.executeQuery();
2279 while (resultSet.next()) {
2280 globalFileInstances.add(getEamGlobalFileInstanceFromResultSet(resultSet));
2282 return globalFileInstances;
2284 }
catch (SQLException ex) {
2285 throw new EamDbException(
"Error getting reference instances by type and value.", ex);
2304 if (newType == null) {
2305 throw new EamDbException(
"Correlation type is null");
2308 Connection conn = connect();
2310 PreparedStatement preparedStatement = null;
2311 PreparedStatement preparedStatementQuery = null;
2312 ResultSet resultSet = null;
2317 if (-1 == newType.getId()) {
2318 insertSql =
"INSERT INTO correlation_types(display_name, db_table_name, supported, enabled) VALUES (?, ?, ?, ?) " + getConflictClause();
2320 insertSql =
"INSERT INTO correlation_types(id, display_name, db_table_name, supported, enabled) VALUES (?, ?, ?, ?, ?) " + getConflictClause();
2322 querySql =
"SELECT * FROM correlation_types WHERE display_name=? AND db_table_name=?";
2325 preparedStatement = conn.prepareStatement(insertSql);
2327 if (-1 == newType.getId()) {
2328 preparedStatement.setString(1, newType.getDisplayName());
2329 preparedStatement.setString(2, newType.getDbTableName());
2330 preparedStatement.setInt(3, newType.isSupported() ? 1 : 0);
2331 preparedStatement.setInt(4, newType.isEnabled() ? 1 : 0);
2333 preparedStatement.setInt(1, newType.getId());
2334 preparedStatement.setString(2, newType.getDisplayName());
2335 preparedStatement.setString(3, newType.getDbTableName());
2336 preparedStatement.setInt(4, newType.isSupported() ? 1 : 0);
2337 preparedStatement.setInt(5, newType.isEnabled() ? 1 : 0);
2340 preparedStatement.executeUpdate();
2342 preparedStatementQuery = conn.prepareStatement(querySql);
2343 preparedStatementQuery.setString(1, newType.getDisplayName());
2344 preparedStatementQuery.setString(2, newType.getDbTableName());
2346 resultSet = preparedStatementQuery.executeQuery();
2347 if (resultSet.next()) {
2349 typeId = correlationType.getId();
2351 }
catch (SQLException ex) {
2352 throw new EamDbException(
"Error inserting new correlation type.", ex);
2364 Connection conn = connect();
2367 PreparedStatement preparedStatement = null;
2368 ResultSet resultSet = null;
2369 String sql =
"SELECT * FROM correlation_types";
2372 preparedStatement = conn.prepareStatement(sql);
2373 resultSet = preparedStatement.executeQuery();
2374 while (resultSet.next()) {
2375 aTypes.add(getCorrelationTypeFromResultSet(resultSet));
2379 }
catch (SQLException ex) {
2380 throw new EamDbException(
"Error getting all correlation types.", ex);
2399 Connection conn = connect();
2402 PreparedStatement preparedStatement = null;
2403 ResultSet resultSet = null;
2404 String sql =
"SELECT * FROM correlation_types WHERE enabled=1";
2407 preparedStatement = conn.prepareStatement(sql);
2408 resultSet = preparedStatement.executeQuery();
2409 while (resultSet.next()) {
2410 aTypes.add(getCorrelationTypeFromResultSet(resultSet));
2414 }
catch (SQLException ex) {
2415 throw new EamDbException(
"Error getting enabled correlation types.", ex);
2434 Connection conn = connect();
2437 PreparedStatement preparedStatement = null;
2438 ResultSet resultSet = null;
2439 String sql =
"SELECT * FROM correlation_types WHERE supported=1";
2442 preparedStatement = conn.prepareStatement(sql);
2443 resultSet = preparedStatement.executeQuery();
2444 while (resultSet.next()) {
2445 aTypes.add(getCorrelationTypeFromResultSet(resultSet));
2449 }
catch (SQLException ex) {
2450 throw new EamDbException(
"Error getting supported correlation types.", ex);
2467 Connection conn = connect();
2469 PreparedStatement preparedStatement = null;
2470 String sql =
"UPDATE correlation_types SET display_name=?, db_table_name=?, supported=?, enabled=? WHERE id=?";
2473 preparedStatement = conn.prepareStatement(sql);
2474 preparedStatement.setString(1, aType.getDisplayName());
2475 preparedStatement.setString(2, aType.getDbTableName());
2476 preparedStatement.setInt(3, aType.isSupported() ? 1 : 0);
2477 preparedStatement.setInt(4, aType.isEnabled() ? 1 : 0);
2478 preparedStatement.setInt(5, aType.getId());
2479 preparedStatement.executeUpdate();
2481 }
catch (SQLException ex) {
2482 throw new EamDbException(
"Error updating correlation type.", ex);
2501 Connection conn = connect();
2504 PreparedStatement preparedStatement = null;
2505 ResultSet resultSet = null;
2506 String sql =
"SELECT * FROM correlation_types WHERE id=?";
2509 preparedStatement = conn.prepareStatement(sql);
2510 preparedStatement.setInt(1, typeId);
2511 resultSet = preparedStatement.executeQuery();
2512 if (resultSet.next()) {
2513 aType = getCorrelationTypeFromResultSet(resultSet);
2516 throw new EamDbException(
"Failed to find entry for correlation type ID = " + typeId);
2519 }
catch (SQLException ex) {
2520 throw new EamDbException(
"Error getting correlation type by id.", ex);
2538 private CorrelationCase getEamCaseFromResultSet(ResultSet resultSet)
throws SQLException {
2539 if (null == resultSet) {
2545 resultSet.getInt(
"org_id");
2546 if (!resultSet.wasNull()) {
2549 resultSet.getString(
"org_name"),
2550 resultSet.getString(
"poc_name"),
2551 resultSet.getString(
"poc_email"),
2552 resultSet.getString(
"poc_phone"));
2562 eamCase.
setNotes(resultSet.getString(
"notes"));
2567 private CorrelationDataSource getEamDataSourceFromResultSet(ResultSet resultSet)
throws SQLException {
2568 if (null == resultSet) {
2573 resultSet.getInt(
"case_id"),
2574 resultSet.getInt(
"id"),
2575 resultSet.getString(
"device_id"),
2576 resultSet.getString(
"name")
2579 return eamDataSource;
2582 private CorrelationAttribute.
Type getCorrelationTypeFromResultSet(ResultSet resultSet)
throws EamDbException, SQLException {
2583 if (null == resultSet) {
2588 resultSet.getInt(
"id"),
2589 resultSet.getString(
"display_name"),
2590 resultSet.getString(
"db_table_name"),
2591 resultSet.getBoolean(
"supported"),
2592 resultSet.getBoolean(
"enabled")
2595 return eamArtifactType;
2609 if (null == resultSet) {
2614 resultSet.getInt(
"id"),
2615 new CorrelationCase(resultSet.getInt(
"case_id"), resultSet.getString(
"case_uid"), resultSet.getString(
"case_name")),
2616 new CorrelationDataSource(resultSet.getInt(
"case_id"), resultSet.getInt(
"data_source_id"), resultSet.getString(
"device_id"), resultSet.getString(
"name")),
2617 resultSet.getString(
"file_path"),
2618 resultSet.getString(
"comment"),
2619 TskData.FileKnown.valueOf(resultSet.getByte(
"known_status"))
2623 private EamOrganization getEamOrganizationFromResultSet(ResultSet resultSet)
throws SQLException {
2624 if (null == resultSet) {
2629 resultSet.getInt(
"id"),
2630 resultSet.getString(
"org_name"),
2631 resultSet.getString(
"poc_name"),
2632 resultSet.getString(
"poc_email"),
2633 resultSet.getString(
"poc_phone")
2637 private EamGlobalSet getEamGlobalSetFromResultSet(ResultSet resultSet)
throws SQLException, EamDbException {
2638 if (null == resultSet) {
2643 resultSet.getInt(
"id"),
2644 resultSet.getInt(
"org_id"),
2645 resultSet.getString(
"set_name"),
2646 resultSet.getString(
"version"),
2647 TskData.FileKnown.valueOf(resultSet.getByte(
"known_status")),
2648 resultSet.getBoolean(
"read_only"),
2650 LocalDate.parse(resultSet.getString(
"import_date"))
2654 private EamGlobalFileInstance getEamGlobalFileInstanceFromResultSet(ResultSet resultSet)
throws SQLException, EamDbException {
2655 if (null == resultSet) {
2660 resultSet.getInt(
"id"),
2661 resultSet.getInt(
"reference_set_id"),
2662 resultSet.getString(
"value"),
2663 TskData.FileKnown.valueOf(resultSet.getByte(
"known_status")),
2664 resultSet.getString(
"comment")
2674 public void upgradeSchema()
throws EamDbException, SQLException {
2676 ResultSet resultSet = null;
2677 Statement statement = null;
2678 Connection conn = null;
2682 conn.setAutoCommit(
false);
2683 statement = conn.createStatement();
2685 int minorVersion = 0;
2686 resultSet = statement.executeQuery(
"SELECT value FROM db_info WHERE name='SCHEMA_MINOR_VERSION'");
2687 if (resultSet.next()) {
2688 String minorVersionStr = resultSet.getString(
"value");
2690 minorVersion = Integer.parseInt(minorVersionStr);
2691 }
catch (NumberFormatException ex) {
2692 throw new EamDbException(
"Bad value for schema minor version (" + minorVersionStr +
") - database is corrupt", ex);
2696 int majorVersion = 0;
2697 resultSet = statement.executeQuery(
"SELECT value FROM db_info WHERE name='SCHEMA_VERSION'");
2698 if (resultSet.next()) {
2699 String majorVersionStr = resultSet.getString(
"value");
2701 majorVersion = Integer.parseInt(majorVersionStr);
2702 }
catch (NumberFormatException ex) {
2703 throw new EamDbException(
"Bad value for schema version (" + majorVersionStr +
") - database is corrupt", ex);
2707 CaseDbSchemaVersionNumber dbSchemaVersion =
new CaseDbSchemaVersionNumber(majorVersion, minorVersion);
2708 if (dbSchemaVersion.equals(CURRENT_DB_SCHEMA_VERSION)) {
2709 logger.log(Level.INFO,
"Central Repository is up to date");
2714 if (dbSchemaVersion.compareTo(
new CaseDbSchemaVersionNumber(1, 1)) < 0) {
2715 statement.execute(
"ALTER TABLE reference_sets ADD COLUMN known_status INTEGER;");
2716 statement.execute(
"ALTER TABLE reference_sets ADD COLUMN read_only BOOLEAN;");
2717 statement.execute(
"ALTER TABLE reference_sets ADD COLUMN type INTEGER;");
2722 EamDbUtil.insertDefaultOrganization(conn);
2725 if (!updateSchemaVersion(conn)) {
2726 throw new EamDbException(
"Error updating schema version");
2730 logger.log(Level.INFO,
"Central Repository upgraded to version " + CURRENT_DB_SCHEMA_VERSION);
2731 }
catch (SQLException | EamDbException ex) {
2736 }
catch (SQLException ex2) {
2737 logger.log(Level.SEVERE,
"Database rollback failed", ex2);
String getCorrelationValue()
CorrelationAttribute.Type getCorrelationTypeById(int typeId)
TskData.FileKnown getKnownStatus()
String getExaminerPhone()
static void submitTimingMetric(TimingMetric metric)
void setCreationDate(String creationDate)
Type getCorrelationType()
static final int FILES_TYPE_ID
static void closeResultSet(ResultSet resultSet)
static void closeStatement(Statement statement)
void setExaminerPhone(String examinerPhone)
void setExaminerName(String examinerName)
void setCaseNumber(String caseNumber)
void addInstance(CorrelationAttributeInstance artifactInstance)
void process(ResultSet resultSet)
static String correlationTypeToReferenceTableName(CorrelationAttribute.Type type)
void setOrg(EamOrganization org)
static EamDb getInstance()
void setNotes(String notes)
static void closeConnection(Connection conn)
static TimingMetric getTimingMetric(String name)
TskData.FileKnown getFileKnownStatus()
List< CorrelationAttributeInstance > getInstances()
CorrelationDataSource getCorrelationDataSource()
String getExaminerEmail()
static String correlationTypeToInstanceTableName(CorrelationAttribute.Type type)
LocalDate getImportDate()
String getExaminerPhone()
CorrelationAttribute.Type getType()
synchronized static Logger getLogger(String name)
static List< CorrelationAttribute.Type > getDefaultCorrelationTypes()
CorrelationCase getCorrelationCase()
static EamOrganization getDefault()
void setExaminerEmail(String examinerEmail)
static void closePreparedStatement(PreparedStatement preparedStatement)
String getExaminerEmail()