19 package org.sleuthkit.autopsy.centralrepository.datamodel;
21 import com.google.common.cache.Cache;
22 import com.google.common.cache.CacheBuilder;
23 import com.google.common.cache.CacheLoader;
24 import java.net.UnknownHostException;
25 import java.util.ArrayList;
26 import java.util.List;
27 import java.util.Collection;
28 import java.util.LinkedHashSet;
29 import java.util.stream.Collectors;
30 import java.sql.Connection;
31 import java.sql.PreparedStatement;
32 import java.sql.ResultSet;
33 import java.sql.SQLException;
34 import java.sql.Statement;
35 import java.sql.Types;
36 import java.time.LocalDate;
37 import java.util.Arrays;
38 import java.util.HashMap;
41 import java.util.concurrent.ExecutionException;
42 import java.util.concurrent.TimeUnit;
43 import java.util.logging.Level;
44 import org.openide.util.NbBundle.Messages;
50 import org.
sleuthkit.datamodel.CaseDbSchemaVersionNumber;
58 abstract class AbstractSqlEamDb
implements EamDb {
61 static final String SCHEMA_MAJOR_VERSION_KEY =
"SCHEMA_VERSION";
62 static final String SCHEMA_MINOR_VERSION_KEY =
"SCHEMA_MINOR_VERSION";
63 static final String CREATION_SCHEMA_MAJOR_VERSION_KEY =
"CREATION_SCHEMA_MAJOR_VERSION";
64 static final String CREATION_SCHEMA_MINOR_VERSION_KEY =
"CREATION_SCHEMA_MINOR_VERSION";
65 static final CaseDbSchemaVersionNumber SOFTWARE_CR_DB_SCHEMA_VERSION =
new CaseDbSchemaVersionNumber(1, 3);
69 private int bulkArtifactsCount;
70 protected int bulkArtifactsThreshold;
71 private final Map<String, Collection<CorrelationAttributeInstance>> bulkArtifacts;
72 private static final int CASE_CACHE_TIMEOUT = 5;
73 private static final int DATA_SOURCE_CACHE_TIMEOUT = 5;
75 private static final Cache<String, CorrelationCase> caseCacheByUUID = CacheBuilder.newBuilder()
76 .expireAfterWrite(CASE_CACHE_TIMEOUT, TimeUnit.MINUTES).
78 private static final Cache<Integer, CorrelationCase> caseCacheById = CacheBuilder.newBuilder()
79 .expireAfterWrite(CASE_CACHE_TIMEOUT, TimeUnit.MINUTES).
81 private static final Cache<String, CorrelationDataSource> dataSourceCacheByDsObjectId = CacheBuilder.newBuilder()
82 .expireAfterWrite(DATA_SOURCE_CACHE_TIMEOUT, TimeUnit.MINUTES).
84 private static final Cache<String, CorrelationDataSource> dataSourceCacheById = CacheBuilder.newBuilder()
85 .expireAfterWrite(DATA_SOURCE_CACHE_TIMEOUT, TimeUnit.MINUTES).
88 static final int MAX_VALUE_LENGTH = 256;
92 static final int DEFAULT_BULK_THRESHHOLD = 1000;
100 bulkArtifactsCount = 0;
101 bulkArtifacts =
new HashMap<>();
104 defaultCorrelationTypes.forEach((type) -> {
112 protected abstract Connection connect(
boolean foreignKeys)
throws EamDbException;
117 protected abstract Connection connect()
throws EamDbException;
128 public void newDbInfo(String name, String value)
throws EamDbException {
129 Connection conn = connect();
131 PreparedStatement preparedStatement = null;
132 String sql =
"INSERT INTO db_info (name, value) VALUES (?, ?) "
133 + getConflictClause();
135 preparedStatement = conn.prepareStatement(sql);
136 preparedStatement.setString(1, name);
137 preparedStatement.setString(2, value);
138 preparedStatement.executeUpdate();
139 }
catch (SQLException ex) {
140 throw new EamDbException(
"Error adding new name/value pair to db_info.", ex);
149 public void addDataSourceObjectId(
int rowId,
long dataSourceObjectId)
throws EamDbException {
150 Connection conn = connect();
151 PreparedStatement preparedStatement = null;
152 String sql =
"UPDATE data_sources SET datasource_obj_id=? WHERE id=?";
154 preparedStatement = conn.prepareStatement(sql);
155 preparedStatement.setLong(1, dataSourceObjectId);
156 preparedStatement.setInt(2, rowId);
157 preparedStatement.executeUpdate();
158 }
catch (SQLException ex) {
159 throw new EamDbException(
"Error updating data source object id for data_sources row " + rowId, ex);
176 public String getDbInfo(String name)
throws EamDbException {
177 Connection conn = connect();
179 PreparedStatement preparedStatement = null;
180 ResultSet resultSet = null;
182 String sql =
"SELECT value FROM db_info WHERE name=?";
184 preparedStatement = conn.prepareStatement(sql);
185 preparedStatement.setString(1, name);
186 resultSet = preparedStatement.executeQuery();
187 if (resultSet.next()) {
188 value = resultSet.getString(
"value");
190 }
catch (SQLException ex) {
191 throw new EamDbException(
"Error getting value for name.", ex);
204 protected final void clearCaches() {
205 typeCache.invalidateAll();
206 caseCacheByUUID.invalidateAll();
207 caseCacheById.invalidateAll();
208 dataSourceCacheByDsObjectId.invalidateAll();
209 dataSourceCacheById.invalidateAll();
221 public void updateDbInfo(String name, String value)
throws EamDbException {
222 Connection conn = connect();
224 PreparedStatement preparedStatement = null;
225 String sql =
"UPDATE db_info SET value=? WHERE name=?";
227 preparedStatement = conn.prepareStatement(sql);
228 preparedStatement.setString(1, value);
229 preparedStatement.setString(2, name);
230 preparedStatement.executeUpdate();
231 }
catch (SQLException ex) {
232 throw new EamDbException(
"Error updating value for name.", ex);
252 throw new EamDbException(
"Case UUID is null");
257 if (cRCase != null) {
261 Connection conn = connect();
262 PreparedStatement preparedStatement = null;
264 String sql =
"INSERT INTO cases(case_uid, org_id, case_name, creation_date, case_number, "
265 +
"examiner_name, examiner_email, examiner_phone, notes) "
266 +
"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) "
267 + getConflictClause();
268 ResultSet resultSet = null;
270 preparedStatement = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
272 preparedStatement.setString(1, eamCase.
getCaseUUID());
273 if (null == eamCase.
getOrg()) {
274 preparedStatement.setNull(2, Types.INTEGER);
281 preparedStatement.setNull(5, Types.INTEGER);
286 preparedStatement.setNull(6, Types.INTEGER);
291 preparedStatement.setNull(7, Types.INTEGER);
296 preparedStatement.setNull(8, Types.INTEGER);
300 if (
"".equals(eamCase.
getNotes())) {
301 preparedStatement.setNull(9, Types.INTEGER);
303 preparedStatement.setString(9, eamCase.
getNotes());
306 preparedStatement.executeUpdate();
308 resultSet = preparedStatement.getGeneratedKeys();
309 if (!resultSet.next()) {
310 throw new EamDbException(String.format(
"Failed to INSERT case %s in central repo", eamCase.
getCaseUUID()));
312 int caseID = resultSet.getInt(1);
316 caseCacheByUUID.put(eamCase.
getCaseUUID(), correlationCase);
317 caseCacheById.put(caseID, correlationCase);
318 }
catch (SQLException ex) {
319 throw new EamDbException(
"Error inserting new case.", ex);
337 if (autopsyCase == null) {
338 throw new EamDbException(
"Case is null");
352 return newCase(curCeCase);
357 return getCaseByUUID(autopsyCase.
getName());
366 public void updateCase(
CorrelationCase eamCase)
throws EamDbException {
367 if (eamCase == null) {
368 throw new EamDbException(
"Correlation case is null");
371 Connection conn = connect();
373 PreparedStatement preparedStatement = null;
374 String sql =
"UPDATE cases "
375 +
"SET org_id=?, case_name=?, creation_date=?, case_number=?, examiner_name=?, examiner_email=?, examiner_phone=?, notes=? "
376 +
"WHERE case_uid=?";
379 preparedStatement = conn.prepareStatement(sql);
381 if (null == eamCase.
getOrg()) {
382 preparedStatement.setNull(1, Types.INTEGER);
390 preparedStatement.setNull(4, Types.INTEGER);
395 preparedStatement.setNull(5, Types.INTEGER);
400 preparedStatement.setNull(6, Types.INTEGER);
405 preparedStatement.setNull(7, Types.INTEGER);
409 if (
"".equals(eamCase.
getNotes())) {
410 preparedStatement.setNull(8, Types.INTEGER);
412 preparedStatement.setString(8, eamCase.
getNotes());
415 preparedStatement.setString(9, eamCase.
getCaseUUID());
417 preparedStatement.executeUpdate();
419 caseCacheById.put(eamCase.
getID(), eamCase);
420 caseCacheByUUID.put(eamCase.
getCaseUUID(), eamCase);
421 }
catch (SQLException ex) {
422 throw new EamDbException(
"Error updating case.", ex);
437 public CorrelationCase getCaseByUUID(String caseUUID)
throws EamDbException {
439 return caseCacheByUUID.get(caseUUID, () -> getCaseByUUIDFromCr(caseUUID));
440 }
catch (CacheLoader.InvalidCacheLoadException ignored) {
443 }
catch (ExecutionException ex) {
444 throw new EamDbException(
"Error getting autopsy case from Central repo", ex);
455 private CorrelationCase getCaseByUUIDFromCr(String caseUUID)
throws EamDbException {
456 Connection conn = connect();
459 PreparedStatement preparedStatement = null;
460 ResultSet resultSet = null;
462 String sql =
"SELECT cases.id as case_id, case_uid, case_name, creation_date, case_number, examiner_name, "
463 +
"examiner_email, examiner_phone, notes, organizations.id as org_id, org_name, poc_name, poc_email, poc_phone "
465 +
"LEFT JOIN organizations ON cases.org_id=organizations.id "
466 +
"WHERE case_uid=?";
469 preparedStatement = conn.prepareStatement(sql);
470 preparedStatement.setString(1, caseUUID);
471 resultSet = preparedStatement.executeQuery();
472 if (resultSet.next()) {
473 eamCaseResult = getEamCaseFromResultSet(resultSet);
475 if (eamCaseResult != null) {
477 caseCacheById.put(eamCaseResult.
getID(), eamCaseResult);
479 }
catch (SQLException ex) {
480 throw new EamDbException(
"Error getting case details.", ex);
487 return eamCaseResult;
500 return caseCacheById.get(caseId, () -> getCaseByIdFromCr(caseId));
501 }
catch (CacheLoader.InvalidCacheLoadException ignored) {
504 }
catch (ExecutionException ex) {
505 throw new EamDbException(
"Error getting autopsy case from Central repo", ex);
516 private CorrelationCase getCaseByIdFromCr(
int caseId)
throws EamDbException {
517 Connection conn = connect();
520 PreparedStatement preparedStatement = null;
521 ResultSet resultSet = null;
523 String sql =
"SELECT cases.id as case_id, case_uid, case_name, creation_date, case_number, examiner_name, "
524 +
"examiner_email, examiner_phone, notes, organizations.id as org_id, org_name, poc_name, poc_email, poc_phone "
526 +
"LEFT JOIN organizations ON cases.org_id=organizations.id "
527 +
"WHERE cases.id=?";
529 preparedStatement = conn.prepareStatement(sql);
530 preparedStatement.setInt(1, caseId);
531 resultSet = preparedStatement.executeQuery();
532 if (resultSet.next()) {
533 eamCaseResult = getEamCaseFromResultSet(resultSet);
535 if (eamCaseResult != null) {
537 caseCacheByUUID.put(eamCaseResult.
getCaseUUID(), eamCaseResult);
539 }
catch (SQLException ex) {
540 throw new EamDbException(
"Error getting case details.", ex);
547 return eamCaseResult;
556 public List<CorrelationCase> getCases()
throws EamDbException {
557 Connection conn = connect();
559 List<CorrelationCase> cases =
new ArrayList<>();
561 PreparedStatement preparedStatement = null;
562 ResultSet resultSet = null;
564 String sql =
"SELECT cases.id as case_id, case_uid, case_name, creation_date, case_number, examiner_name, "
565 +
"examiner_email, examiner_phone, notes, organizations.id as org_id, org_name, poc_name, poc_email, poc_phone "
567 +
"LEFT JOIN organizations ON cases.org_id=organizations.id";
570 preparedStatement = conn.prepareStatement(sql);
571 resultSet = preparedStatement.executeQuery();
572 while (resultSet.next()) {
573 eamCaseResult = getEamCaseFromResultSet(resultSet);
574 cases.add(eamCaseResult);
576 }
catch (SQLException ex) {
577 throw new EamDbException(
"Error getting all cases.", ex);
597 private static String getDataSourceByDSObjectIdCacheKey(
int caseId, Long dataSourceObjectId) {
598 return "Case" + caseId +
"DsObjectId" + dataSourceObjectId;
610 private static String getDataSourceByIdCacheKey(
int caseId,
int dataSourceId) {
611 return "Case" + caseId +
"Id" + dataSourceId;
622 throw new EamDbException(
"Case ID is -1");
625 throw new EamDbException(
"Device ID is null");
627 if (eamDataSource.
getName() == null) {
628 throw new EamDbException(
"Name is null");
630 if (eamDataSource.
getID() != -1) {
632 return eamDataSource;
635 Connection conn = connect();
637 PreparedStatement preparedStatement = null;
639 String sql =
"INSERT INTO data_sources(device_id, case_id, name, datasource_obj_id, md5, sha1, sha256) VALUES (?, ?, ?, ?, ?, ?, ?) "
640 + getConflictClause();
641 ResultSet resultSet = null;
643 preparedStatement = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
645 preparedStatement.setString(1, eamDataSource.
getDeviceID());
646 preparedStatement.setInt(2, eamDataSource.
getCaseID());
647 preparedStatement.setString(3, eamDataSource.
getName());
649 preparedStatement.setString(5, eamDataSource.
getMd5());
650 preparedStatement.setString(6, eamDataSource.
getSha1());
651 preparedStatement.setString(7, eamDataSource.
getSha256());
653 preparedStatement.executeUpdate();
654 resultSet = preparedStatement.getGeneratedKeys();
655 if (!resultSet.next()) {
664 return dataSourceCacheByDsObjectId.get(getDataSourceByDSObjectIdCacheKey(
667 }
catch (CacheLoader.InvalidCacheLoadException | ExecutionException getException) {
668 throw new EamDbException(String.format(
"Unable to to INSERT or get data source %s in central repo:", eamDataSource.
getName()), getException);
672 int dataSourceId = resultSet.getInt(1);
675 dataSourceCacheById.put(getDataSourceByIdCacheKey(dataSource.
getCaseID(), dataSource.
getID()), dataSource);
679 }
catch (SQLException insertException) {
689 return dataSourceCacheByDsObjectId.get(getDataSourceByDSObjectIdCacheKey(
692 }
catch (CacheLoader.InvalidCacheLoadException | ExecutionException getException) {
693 throw new EamDbException(String.format(
"Unable to to INSERT or get data source %s in central repo, insert failed due to Exception: %s", eamDataSource.
getName(), insertException.getMessage()), getException);
716 if (correlationCase == null) {
717 throw new EamDbException(
"Correlation case is null");
720 return dataSourceCacheByDsObjectId.get(getDataSourceByDSObjectIdCacheKey(correlationCase.
getID(), dataSourceObjectId), () -> getDataSourceFromCr(correlationCase.
getID(), dataSourceObjectId));
721 }
catch (CacheLoader.InvalidCacheLoadException ignored) {
724 }
catch (ExecutionException ex) {
725 throw new EamDbException(
"Error getting data source from central repository", ex);
741 private CorrelationDataSource getDataSourceFromCr(
int correlationCaseId, Long dataSourceObjectId)
throws EamDbException {
742 Connection conn = connect();
745 PreparedStatement preparedStatement = null;
746 ResultSet resultSet = null;
748 String sql =
"SELECT * FROM data_sources WHERE datasource_obj_id=? AND case_id=?";
751 preparedStatement = conn.prepareStatement(sql);
752 preparedStatement.setLong(1, dataSourceObjectId);
753 preparedStatement.setInt(2, correlationCaseId);
754 resultSet = preparedStatement.executeQuery();
755 if (resultSet.next()) {
756 eamDataSourceResult = getEamDataSourceFromResultSet(resultSet);
758 if (eamDataSourceResult != null) {
759 dataSourceCacheById.put(getDataSourceByIdCacheKey(correlationCaseId, eamDataSourceResult.
getID()), eamDataSourceResult);
761 }
catch (SQLException ex) {
762 throw new EamDbException(
"Error getting data source.", ex);
769 return eamDataSourceResult;
783 if (correlationCase == null) {
784 throw new EamDbException(
"Correlation case is null");
787 return dataSourceCacheById.get(getDataSourceByIdCacheKey(correlationCase.
getID(), dataSourceId), () -> getDataSourceByIdFromCr(correlationCase, dataSourceId));
788 }
catch (CacheLoader.InvalidCacheLoadException ignored) {
791 }
catch (ExecutionException ex) {
792 throw new EamDbException(
"Error getting data source from central repository", ex);
806 Connection conn = connect();
809 PreparedStatement preparedStatement = null;
810 ResultSet resultSet = null;
812 String sql =
"SELECT * FROM data_sources WHERE id=? AND case_id=?";
815 preparedStatement = conn.prepareStatement(sql);
816 preparedStatement.setInt(1, dataSourceId);
817 preparedStatement.setInt(2, correlationCase.
getID());
818 resultSet = preparedStatement.executeQuery();
819 if (resultSet.next()) {
820 eamDataSourceResult = getEamDataSourceFromResultSet(resultSet);
822 if (eamDataSourceResult != null) {
823 dataSourceCacheByDsObjectId.put(getDataSourceByDSObjectIdCacheKey(correlationCase.
getID(), eamDataSourceResult.
getDataSourceObjectID()), eamDataSourceResult);
825 }
catch (SQLException ex) {
826 throw new EamDbException(
"Error getting data source.", ex);
833 return eamDataSourceResult;
842 public List<CorrelationDataSource> getDataSources()
throws EamDbException {
843 Connection conn = connect();
845 List<CorrelationDataSource> dataSources =
new ArrayList<>();
847 PreparedStatement preparedStatement = null;
848 ResultSet resultSet = null;
850 String sql =
"SELECT * FROM data_sources";
853 preparedStatement = conn.prepareStatement(sql);
854 resultSet = preparedStatement.executeQuery();
855 while (resultSet.next()) {
856 eamDataSourceResult = getEamDataSourceFromResultSet(resultSet);
857 dataSources.add(eamDataSourceResult);
859 }
catch (SQLException ex) {
860 throw new EamDbException(
"Error getting all data sources.", ex);
877 updateDataSourceStringValue(eamDataSource,
"md5", eamDataSource.
getMd5());
887 updateDataSourceStringValue(eamDataSource,
"sha1", eamDataSource.
getSha1());
898 updateDataSourceStringValue(eamDataSource,
"sha256", eamDataSource.
getSha256());
908 private void updateDataSourceStringValue(
CorrelationDataSource eamDataSource, String column, String value)
throws EamDbException {
909 if (eamDataSource == null) {
910 throw new EamDbException(
"Correlation data source is null");
913 Connection conn = connect();
915 PreparedStatement preparedStatement = null;
916 String sql =
"UPDATE data_sources "
917 +
"SET " + column +
"=? "
921 preparedStatement = conn.prepareStatement(sql);
923 preparedStatement.setString(1, value);
924 preparedStatement.setInt(2, eamDataSource.
getID());
926 preparedStatement.executeUpdate();
928 dataSourceCacheByDsObjectId.put(getDataSourceByDSObjectIdCacheKey(eamDataSource.
getCaseID(), eamDataSource.
getDataSourceObjectID()), eamDataSource);
929 dataSourceCacheById.put(getDataSourceByIdCacheKey(eamDataSource.
getCaseID(), eamDataSource.
getID()), eamDataSource);
930 }
catch (SQLException ex) {
931 throw new EamDbException(String.format(
"Error updating data source (obj_id=%d).", eamDataSource.
getDataSourceObjectID()), ex);
947 public void updateDataSourceName(
CorrelationDataSource eamDataSource, String newName)
throws EamDbException {
949 Connection conn = connect();
951 PreparedStatement preparedStatement = null;
953 String sql =
"UPDATE data_sources SET name = ? WHERE id = ?";
956 preparedStatement = conn.prepareStatement(sql);
957 preparedStatement.setString(1, newName);
958 preparedStatement.setInt(2, eamDataSource.
getID());
959 preparedStatement.executeUpdate();
963 eamDataSource.
getID(),
971 dataSourceCacheByDsObjectId.put(getDataSourceByDSObjectIdCacheKey(updatedDataSource.
getCaseID(), updatedDataSource.
getDataSourceObjectID()), updatedDataSource);
972 dataSourceCacheById.put(getDataSourceByIdCacheKey(updatedDataSource.
getCaseID(), updatedDataSource.
getID()), updatedDataSource);
973 }
catch (SQLException ex) {
974 throw new EamDbException(
"Error updating name of data source with ID " + eamDataSource.
getDataSourceObjectID()
975 +
" to " + newName, ex);
990 checkAddArtifactInstanceNulls(eamArtifact);
992 Connection conn = connect();
994 PreparedStatement preparedStatement = null;
1001 +
"(case_id, data_source_id, value, file_path, known_status, comment, file_obj_id) "
1002 +
"VALUES (?, ?, ?, ?, ?, ?, ?) "
1003 + getConflictClause();
1006 preparedStatement = conn.prepareStatement(sql);
1012 preparedStatement.setString(4, eamArtifact.
getFilePath().toLowerCase());
1013 preparedStatement.setByte(5, eamArtifact.
getKnownStatus().getFileKnownValue());
1016 preparedStatement.setNull(6, Types.INTEGER);
1018 preparedStatement.setString(6, eamArtifact.
getComment());
1022 preparedStatement.executeUpdate();
1025 }
catch (SQLException ex) {
1026 throw new EamDbException(
"Error inserting new artifact into artifacts table.", ex);
1034 if (eamArtifact == null) {
1035 throw new EamDbException(
"CorrelationAttribute is null");
1038 throw new EamDbException(
"Correlation type is null");
1041 throw new EamDbException(
"Correlation value is null");
1044 throw new EamDbException(
"Artifact value too long for central repository."
1045 +
"\nCorrelationArtifact ID: " + eamArtifact.
getID()
1051 throw new EamDbException(
"CorrelationAttributeInstance case is null");
1054 throw new EamDbException(
"CorrelationAttributeInstance data source is null");
1057 throw new EamDbException(
"CorrelationAttributeInstance known status is null");
1063 if (value == null) {
1066 return getArtifactInstancesByTypeValues(aType, Arrays.asList(value));
1071 if (aType == null) {
1074 if (values == null || values.isEmpty()) {
1077 return getArtifactInstances(prepareGetInstancesSql(aType, values), aType);
1082 if (aType == null) {
1085 if (values == null || values.isEmpty()) {
1088 if (caseIds == null || caseIds.isEmpty()) {
1096 StringBuilder inValuesBuilder =
new StringBuilder(prepareGetInstancesSql(aType, values));
1097 inValuesBuilder.append(sql);
1098 inValuesBuilder.append(caseIds.stream().map(String::valueOf).collect(Collectors.joining(
"', '")));
1099 inValuesBuilder.append(
"')");
1100 return getArtifactInstances(inValuesBuilder.toString(), aType);
1125 +
" 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, data_sources.datasource_obj_id, data_sources.md5, data_sources.sha1, data_sources.sha256 FROM "
1127 +
" LEFT JOIN cases ON "
1129 +
".case_id=cases.id"
1130 +
" LEFT JOIN data_sources ON "
1132 +
".data_source_id=data_sources.id"
1133 +
" WHERE value IN (";
1134 StringBuilder inValuesBuilder =
new StringBuilder(sql);
1135 for (String value : values) {
1136 if (value != null) {
1137 inValuesBuilder.append(
"'");
1139 inValuesBuilder.append(
"',");
1142 inValuesBuilder.deleteCharAt(inValuesBuilder.length() - 1);
1143 inValuesBuilder.append(
")");
1144 return inValuesBuilder.toString();
1162 Connection conn = connect();
1163 List<CorrelationAttributeInstance> artifactInstances =
new ArrayList<>();
1165 PreparedStatement preparedStatement = null;
1166 ResultSet resultSet = null;
1168 preparedStatement = conn.prepareStatement(sql);
1169 resultSet = preparedStatement.executeQuery();
1170 while (resultSet.next()) {
1171 artifactInstance = getEamArtifactInstanceFromResultSet(resultSet, aType);
1172 artifactInstances.add(artifactInstance);
1174 }
catch (SQLException ex) {
1175 throw new EamDbException(
"Error getting artifact instances by artifactType and artifactValue.", ex);
1181 return artifactInstances;
1197 if (aType == null) {
1198 throw new EamDbException(
"Correlation type is null");
1200 if (filePath == null) {
1201 throw new EamDbException(
"Correlation value is null");
1203 Connection conn = connect();
1205 List<CorrelationAttributeInstance> artifactInstances =
new ArrayList<>();
1208 PreparedStatement preparedStatement = null;
1209 ResultSet resultSet = null;
1220 +
" 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, data_sources.datasource_obj_id, data_sources.md5, data_sources.sha1, data_sources.sha256 FROM "
1222 +
" LEFT JOIN cases ON "
1224 +
".case_id=cases.id"
1225 +
" LEFT JOIN data_sources ON "
1227 +
".data_source_id=data_sources.id"
1228 +
" WHERE file_path=?";
1231 preparedStatement = conn.prepareStatement(sql);
1232 preparedStatement.setString(1, filePath.toLowerCase());
1233 resultSet = preparedStatement.executeQuery();
1234 while (resultSet.next()) {
1236 artifactInstance = getEamArtifactInstanceFromResultSet(resultSet, aType);
1237 artifactInstances.add(artifactInstance);
1238 }
catch (CorrelationAttributeNormalizationException ex) {
1239 logger.log(Level.INFO,
"Unable to get artifact instance from resultset.", ex);
1242 }
catch (SQLException ex) {
1243 throw new EamDbException(
"Error getting artifact instances by artifactType and artifactValue.", ex);
1250 return artifactInstances;
1264 public Long getCountArtifactInstancesByTypeValue(
CorrelationAttributeInstance.
Type aType, String value)
throws EamDbException, CorrelationAttributeNormalizationException {
1267 Connection conn = connect();
1269 Long instanceCount = 0L;
1270 PreparedStatement preparedStatement = null;
1271 ResultSet resultSet = null;
1275 =
"SELECT count(*) FROM "
1280 preparedStatement = conn.prepareStatement(sql);
1281 preparedStatement.setString(1, normalizedValue);
1282 resultSet = preparedStatement.executeQuery();
1284 instanceCount = resultSet.getLong(1);
1285 }
catch (SQLException ex) {
1286 throw new EamDbException(
"Error getting count of artifact instances by artifactType and artifactValue.", ex);
1293 return instanceCount;
1297 public int getFrequencyPercentage(
CorrelationAttributeInstance corAttr)
throws EamDbException, CorrelationAttributeNormalizationException {
1298 if (corAttr == null) {
1299 throw new EamDbException(
"CorrelationAttribute is null");
1302 Double uniqueCaseDataSourceTuples = getCountUniqueDataSources().doubleValue();
1303 Double commonalityPercentage = uniqueTypeValueTuples / uniqueCaseDataSourceTuples * 100;
1304 return commonalityPercentage.intValue();
1318 public Long getCountUniqueCaseDataSourceTuplesHavingTypeValue(
CorrelationAttributeInstance.
Type aType, String value)
throws EamDbException, CorrelationAttributeNormalizationException {
1321 Connection conn = connect();
1323 Long instanceCount = 0L;
1324 PreparedStatement preparedStatement = null;
1325 ResultSet resultSet = null;
1329 =
"SELECT count(*) FROM (SELECT DISTINCT case_id, data_source_id FROM "
1331 +
" WHERE value=?) AS "
1333 +
"_distinct_case_data_source_tuple";
1336 preparedStatement = conn.prepareStatement(sql);
1337 preparedStatement.setString(1, normalizedValue);
1338 resultSet = preparedStatement.executeQuery();
1340 instanceCount = resultSet.getLong(1);
1341 }
catch (SQLException ex) {
1342 throw new EamDbException(
"Error counting unique caseDisplayName/dataSource tuples having artifactType and artifactValue.", ex);
1349 return instanceCount;
1353 public Long getCountUniqueDataSources()
throws EamDbException {
1354 Connection conn = connect();
1356 Long instanceCount = 0L;
1357 PreparedStatement preparedStatement = null;
1358 ResultSet resultSet = null;
1360 String stmt =
"SELECT count(*) FROM data_sources";
1363 preparedStatement = conn.prepareStatement(stmt);
1364 resultSet = preparedStatement.executeQuery();
1366 instanceCount = resultSet.getLong(1);
1367 }
catch (SQLException ex) {
1368 throw new EamDbException(
"Error counting data sources.", ex);
1375 return instanceCount;
1390 public Long getCountArtifactInstancesByCaseDataSource(
CorrelationDataSource correlationDataSource)
throws EamDbException {
1391 Connection conn = connect();
1393 Long instanceCount = 0L;
1395 PreparedStatement preparedStatement = null;
1396 ResultSet resultSet = null;
1399 String sql =
"SELECT 0 ";
1404 +=
"+ (SELECT count(*) FROM "
1406 +
" WHERE data_source_id=" + correlationDataSource.
getID() +
")";
1409 preparedStatement = conn.prepareStatement(sql);
1411 resultSet = preparedStatement.executeQuery();
1413 instanceCount = resultSet.getLong(1);
1414 }
catch (SQLException ex) {
1415 throw new EamDbException(
"Error counting artifact instances by caseName/dataSource.", ex);
1422 return instanceCount;
1436 throw new EamDbException(
"Correlation type is null");
1439 synchronized (bulkArtifacts) {
1441 bulkArtifactsCount++;
1443 if (bulkArtifactsCount >= bulkArtifactsThreshold) {
1444 commitAttributeInstancesBulk();
1454 protected abstract String getConflictClause();
1461 public void commitAttributeInstancesBulk()
throws EamDbException {
1464 Connection conn = connect();
1465 PreparedStatement bulkPs = null;
1468 synchronized (bulkArtifacts) {
1469 if (bulkArtifactsCount == 0) {
1473 for (String tableName : bulkArtifacts.keySet()) {
1478 +
" (case_id, data_source_id, value, file_path, known_status, comment, file_obj_id) "
1479 +
"VALUES ((SELECT id FROM cases WHERE case_uid=? LIMIT 1), "
1480 +
"(SELECT id FROM data_sources WHERE datasource_obj_id=? AND case_id=? LIMIT 1), ?, ?, ?, ?, ?) "
1481 + getConflictClause();
1483 bulkPs = conn.prepareStatement(sql);
1485 Collection<CorrelationAttributeInstance> eamArtifacts = bulkArtifacts.get(tableName);
1491 throw new EamDbException(
"CorrelationAttributeInstance case is null for: "
1492 +
"\n\tCorrelationArtifact ID: " + eamArtifact.
getID()
1497 throw new EamDbException(
"CorrelationAttributeInstance data source is null for: "
1498 +
"\n\tCorrelationArtifact ID: " + eamArtifact.
getID()
1503 throw new EamDbException(
"CorrelationAttributeInstance known status is null for: "
1504 +
"\n\tCorrelationArtifact ID: " + eamArtifact.
getID()
1507 +
"\n\tEam Instance: "
1518 bulkPs.setByte(6, eamArtifact.
getKnownStatus().getFileKnownValue());
1520 bulkPs.setNull(7, Types.INTEGER);
1522 bulkPs.setString(7, eamArtifact.
getComment());
1527 logger.log(Level.WARNING, (
"Artifact value too long for central repository."
1528 +
"\n\tCorrelationArtifact ID: " + eamArtifact.
getID()
1531 +
"\n\tEam Instance: "
1534 +
"\n\t\tFilePath: " + eamArtifact.
getFilePath());
1540 bulkPs.executeBatch();
1541 bulkArtifacts.get(tableName).clear();
1548 bulkArtifactsCount = 0;
1550 }
catch (SQLException ex) {
1551 throw new EamDbException(
"Error inserting bulk artifacts.", ex);
1562 public void bulkInsertCases(List<CorrelationCase> cases)
throws EamDbException {
1563 if (cases == null) {
1564 throw new EamDbException(
"cases argument is null");
1567 if (cases.isEmpty()) {
1571 Connection conn = connect();
1574 PreparedStatement bulkPs = null;
1576 String sql =
"INSERT INTO cases(case_uid, org_id, case_name, creation_date, case_number, "
1577 +
"examiner_name, examiner_email, examiner_phone, notes) "
1578 +
"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) "
1579 + getConflictClause();
1580 bulkPs = conn.prepareStatement(sql);
1584 if (null == eamCase.
getOrg()) {
1585 bulkPs.setNull(2, Types.INTEGER);
1593 bulkPs.setNull(5, Types.INTEGER);
1598 bulkPs.setNull(6, Types.INTEGER);
1603 bulkPs.setNull(7, Types.INTEGER);
1608 bulkPs.setNull(8, Types.INTEGER);
1612 if (
"".equals(eamCase.
getNotes())) {
1613 bulkPs.setNull(9, Types.INTEGER);
1615 bulkPs.setString(9, eamCase.
getNotes());
1623 if (counter >= bulkArtifactsThreshold) {
1624 bulkPs.executeBatch();
1629 bulkPs.executeBatch();
1630 }
catch (SQLException ex) {
1631 throw new EamDbException(
"Error inserting bulk cases.", ex);
1650 if (eamArtifact == null) {
1651 throw new EamDbException(
"CorrelationAttributeInstance is null");
1654 throw new EamDbException(
"Correlation case is null");
1657 throw new EamDbException(
"Correlation data source is null");
1659 Connection conn = connect();
1660 PreparedStatement preparedQuery = null;
1666 +
"WHERE case_id=? "
1667 +
"AND data_source_id=? "
1669 +
"AND file_path=?";
1672 preparedQuery = conn.prepareStatement(sqlUpdate);
1673 preparedQuery.setString(1, eamArtifact.
getComment());
1677 preparedQuery.setString(5, eamArtifact.
getFilePath().toLowerCase());
1678 preparedQuery.executeUpdate();
1679 }
catch (SQLException ex) {
1680 throw new EamDbException(
"Error getting/setting artifact instance comment=" + eamArtifact.
getComment(), ex);
1703 CorrelationDataSource correlationDataSource,
long objectID)
throws EamDbException, CorrelationAttributeNormalizationException {
1705 if (correlationCase == null) {
1706 throw new EamDbException(
"Correlation case is null");
1709 Connection conn = connect();
1711 PreparedStatement preparedStatement = null;
1712 ResultSet resultSet = null;
1719 =
"SELECT id, value, file_path, known_status, comment FROM "
1721 +
" WHERE case_id=?"
1722 +
" AND file_obj_id=?";
1724 preparedStatement = conn.prepareStatement(sql);
1725 preparedStatement.setInt(1, correlationCase.
getID());
1726 preparedStatement.setInt(2, (
int) objectID);
1727 resultSet = preparedStatement.executeQuery();
1728 if (resultSet.next()) {
1729 int instanceId = resultSet.getInt(1);
1730 String value = resultSet.getString(2);
1731 String filePath = resultSet.getString(3);
1732 int knownStatus = resultSet.getInt(4);
1733 String comment = resultSet.getString(5);
1736 instanceId, correlationCase, correlationDataSource, filePath, comment, TskData.FileKnown.valueOf((byte) knownStatus), objectID);
1738 }
catch (SQLException ex) {
1739 throw new EamDbException(
"Error getting notable artifact instances.", ex);
1746 return correlationAttributeInstance;
1765 CorrelationDataSource correlationDataSource, String value, String filePath)
throws EamDbException, CorrelationAttributeNormalizationException {
1767 if (correlationCase == null) {
1768 throw new EamDbException(
"Correlation case is null");
1770 if (correlationDataSource == null) {
1771 throw new EamDbException(
"Correlation data source is null");
1773 if (filePath == null) {
1774 throw new EamDbException(
"Correlation file path is null");
1777 Connection conn = connect();
1779 PreparedStatement preparedStatement = null;
1780 ResultSet resultSet = null;
1788 =
"SELECT id, known_status, comment FROM "
1790 +
" WHERE case_id=?"
1791 +
" AND data_source_id=?"
1793 +
" AND file_path=?";
1795 preparedStatement = conn.prepareStatement(sql);
1796 preparedStatement.setInt(1, correlationCase.
getID());
1797 preparedStatement.setInt(2, correlationDataSource.
getID());
1798 preparedStatement.setString(3, normalizedValue);
1799 preparedStatement.setString(4, filePath.toLowerCase());
1800 resultSet = preparedStatement.executeQuery();
1801 if (resultSet.next()) {
1802 int instanceId = resultSet.getInt(1);
1803 int knownStatus = resultSet.getInt(2);
1804 String comment = resultSet.getString(3);
1807 instanceId, correlationCase, correlationDataSource, filePath, comment, TskData.FileKnown.valueOf((byte) knownStatus), null);
1809 }
catch (SQLException ex) {
1810 throw new EamDbException(
"Error getting notable artifact instances.", ex);
1817 return correlationAttributeInstance;
1831 public void setAttributeInstanceKnownStatus(
CorrelationAttributeInstance eamArtifact, TskData.FileKnown knownStatus)
throws EamDbException {
1832 if (eamArtifact == null) {
1833 throw new EamDbException(
"CorrelationAttribute is null");
1835 if (knownStatus == null) {
1836 throw new EamDbException(
"Known status is null");
1840 throw new EamDbException(
"Correlation case is null");
1843 throw new EamDbException(
"Correlation data source is null");
1846 Connection conn = connect();
1848 PreparedStatement preparedUpdate = null;
1849 PreparedStatement preparedQuery = null;
1850 ResultSet resultSet = null;
1857 +
" WHERE case_id=? "
1858 +
"AND data_source_id=? "
1860 +
"AND file_path=?";
1865 +
" SET known_status=?, comment=? "
1869 preparedQuery = conn.prepareStatement(sqlQuery);
1873 preparedQuery.setString(4, eamArtifact.
getFilePath());
1874 resultSet = preparedQuery.executeQuery();
1875 if (resultSet.next()) {
1876 int instance_id = resultSet.getInt(
"id");
1877 preparedUpdate = conn.prepareStatement(sqlUpdate);
1879 preparedUpdate.setByte(1, knownStatus.getFileKnownValue());
1884 preparedUpdate.setNull(2, Types.INTEGER);
1886 preparedUpdate.setString(2, eamArtifact.
getComment());
1888 preparedUpdate.setInt(3, instance_id);
1890 preparedUpdate.executeUpdate();
1902 addArtifactInstance(eamArtifact);
1905 }
catch (SQLException ex) {
1906 throw new EamDbException(
"Error getting/setting artifact instance knownStatus=" + knownStatus.getName(), ex);
1925 public List<CorrelationAttributeInstance> getArtifactInstancesKnownBad(
CorrelationAttributeInstance.
Type aType, String value)
throws EamDbException, CorrelationAttributeNormalizationException {
1928 Connection conn = connect();
1930 List<CorrelationAttributeInstance> artifactInstances =
new ArrayList<>();
1933 PreparedStatement preparedStatement = null;
1934 ResultSet resultSet = null;
1945 +
"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, data_sources.datasource_obj_id, data_sources.md5, data_sources.sha1, data_sources.sha256 FROM "
1947 +
" LEFT JOIN cases ON "
1949 +
".case_id=cases.id"
1950 +
" LEFT JOIN data_sources ON "
1952 +
".data_source_id=data_sources.id"
1953 +
" WHERE value=? AND known_status=?";
1956 preparedStatement = conn.prepareStatement(sql);
1957 preparedStatement.setString(1, normalizedValue);
1958 preparedStatement.setByte(2, TskData.FileKnown.BAD.getFileKnownValue());
1959 resultSet = preparedStatement.executeQuery();
1960 while (resultSet.next()) {
1961 artifactInstance = getEamArtifactInstanceFromResultSet(resultSet, aType);
1962 artifactInstances.add(artifactInstance);
1964 }
catch (SQLException ex) {
1965 throw new EamDbException(
"Error getting notable artifact instances.", ex);
1972 return artifactInstances;
1988 if (aType == null) {
1989 throw new EamDbException(
"Correlation type is null");
1992 Connection conn = connect();
1994 List<CorrelationAttributeInstance> artifactInstances =
new ArrayList<>();
1997 PreparedStatement preparedStatement = null;
1998 ResultSet resultSet = null;
2002 =
"SELECT cases.case_name, cases.case_uid, data_sources.name, device_id, file_path, known_status, comment, data_sources.case_id, id, value, file_obj_id, data_sources.datasource_obj_id, data_sources.md5, data_sources.sha1, data_sources.sha256 FROM "
2004 +
" LEFT JOIN cases ON "
2006 +
".case_id=cases.id"
2007 +
" LEFT JOIN data_sources ON "
2009 +
".data_source_id=data_sources.id"
2010 +
" WHERE known_status=?"
2016 preparedStatement = conn.prepareStatement(sql);
2017 preparedStatement.setByte(1, TskData.FileKnown.BAD.getFileKnownValue());
2018 resultSet = preparedStatement.executeQuery();
2019 while (resultSet.next()) {
2021 artifactInstance = getEamArtifactInstanceFromResultSet(resultSet, aType);
2022 artifactInstances.add(artifactInstance);
2023 }
catch (CorrelationAttributeNormalizationException ex) {
2024 logger.log(Level.INFO,
"Unable to get artifact instance from resultset.", ex);
2027 }
catch (SQLException ex) {
2028 throw new EamDbException(
"Error getting notable artifact instances.", ex);
2035 return artifactInstances;
2047 public Long getCountArtifactInstancesKnownBad(
CorrelationAttributeInstance.
Type aType, String value)
throws EamDbException, CorrelationAttributeNormalizationException {
2051 Connection conn = connect();
2053 Long badInstances = 0L;
2054 PreparedStatement preparedStatement = null;
2055 ResultSet resultSet = null;
2059 =
"SELECT count(*) FROM "
2061 +
" WHERE value=? AND known_status=?";
2064 preparedStatement = conn.prepareStatement(sql);
2065 preparedStatement.setString(1, normalizedValue);
2066 preparedStatement.setByte(2, TskData.FileKnown.BAD.getFileKnownValue());
2067 resultSet = preparedStatement.executeQuery();
2069 badInstances = resultSet.getLong(1);
2070 }
catch (SQLException ex) {
2071 throw new EamDbException(
"Error getting count of notable artifact instances.", ex);
2078 return badInstances;
2094 public List<String> getListCasesHavingArtifactInstancesKnownBad(
CorrelationAttributeInstance.
Type aType, String value)
throws EamDbException, CorrelationAttributeNormalizationException {
2098 Connection conn = connect();
2100 Collection<String> caseNames =
new LinkedHashSet<>();
2102 PreparedStatement preparedStatement = null;
2103 ResultSet resultSet = null;
2107 =
"SELECT DISTINCT case_name FROM "
2109 +
" INNER JOIN cases ON "
2111 +
".case_id=cases.id WHERE "
2115 +
".known_status=?";
2118 preparedStatement = conn.prepareStatement(sql);
2119 preparedStatement.setString(1, normalizedValue);
2120 preparedStatement.setByte(2, TskData.FileKnown.BAD.getFileKnownValue());
2121 resultSet = preparedStatement.executeQuery();
2122 while (resultSet.next()) {
2123 caseNames.add(resultSet.getString(
"case_name"));
2125 }
catch (SQLException ex) {
2126 throw new EamDbException(
"Error getting notable artifact instances.", ex);
2133 return caseNames.stream().collect(Collectors.toList());
2144 public void deleteReferenceSet(
int referenceSetID)
throws EamDbException {
2145 deleteReferenceSetEntries(referenceSetID);
2146 deleteReferenceSetEntry(referenceSetID);
2156 private void deleteReferenceSetEntry(
int referenceSetID)
throws EamDbException {
2157 Connection conn = connect();
2159 PreparedStatement preparedStatement = null;
2160 String sql =
"DELETE FROM reference_sets WHERE id=?";
2163 preparedStatement = conn.prepareStatement(sql);
2164 preparedStatement.setInt(1, referenceSetID);
2165 preparedStatement.executeUpdate();
2166 }
catch (SQLException ex) {
2167 throw new EamDbException(
"Error deleting reference set " + referenceSetID, ex);
2182 private void deleteReferenceSetEntries(
int referenceSetID)
throws EamDbException {
2183 Connection conn = connect();
2185 PreparedStatement preparedStatement = null;
2186 String sql =
"DELETE FROM %s WHERE reference_set_id=?";
2192 preparedStatement = conn.prepareStatement(String.format(sql, fileTableName));
2193 preparedStatement.setInt(1, referenceSetID);
2194 preparedStatement.executeUpdate();
2195 }
catch (SQLException ex) {
2196 throw new EamDbException(
"Error deleting files from reference set " + referenceSetID, ex);
2217 public boolean referenceSetIsValid(
int referenceSetID, String setName, String version)
throws EamDbException {
2218 EamGlobalSet refSet = this.getReferenceSetByID(referenceSetID);
2219 if (refSet == null) {
2238 public boolean isFileHashInReferenceSet(String hash,
int referenceSetID)
throws EamDbException, CorrelationAttributeNormalizationException {
2252 public boolean isValueInReferenceSet(String value,
int referenceSetID,
int correlationTypeID)
throws EamDbException, CorrelationAttributeNormalizationException {
2256 Connection conn = connect();
2258 Long matchingInstances = 0L;
2259 PreparedStatement preparedStatement = null;
2260 ResultSet resultSet = null;
2261 String sql =
"SELECT count(*) FROM %s WHERE value=? AND reference_set_id=?";
2266 preparedStatement = conn.prepareStatement(String.format(sql, fileTableName));
2267 preparedStatement.setString(1, normalizeValued);
2268 preparedStatement.setInt(2, referenceSetID);
2269 resultSet = preparedStatement.executeQuery();
2271 matchingInstances = resultSet.getLong(1);
2272 }
catch (SQLException ex) {
2273 throw new EamDbException(
"Error determining if value (" + normalizeValued +
") is in reference set " + referenceSetID, ex);
2280 return 0 < matchingInstances;
2292 public boolean isArtifactKnownBadByReference(
CorrelationAttributeInstance.
Type aType, String value)
throws EamDbException, CorrelationAttributeNormalizationException {
2302 Connection conn = connect();
2304 Long badInstances = 0L;
2305 PreparedStatement preparedStatement = null;
2306 ResultSet resultSet = null;
2307 String sql =
"SELECT count(*) FROM %s WHERE value=? AND known_status=?";
2311 preparedStatement.setString(1, normalizeValued);
2312 preparedStatement.setByte(2, TskData.FileKnown.BAD.getFileKnownValue());
2313 resultSet = preparedStatement.executeQuery();
2315 badInstances = resultSet.getLong(1);
2316 }
catch (SQLException ex) {
2317 throw new EamDbException(
"Error determining if artifact is notable by reference.", ex);
2324 return 0 < badInstances;
2338 throw new EamDbException(
"Correlation type is null");
2341 if (instanceTableCallback == null) {
2342 throw new EamDbException(
"Callback interface is null");
2345 Connection conn = connect();
2346 PreparedStatement preparedStatement = null;
2347 ResultSet resultSet = null;
2349 StringBuilder sql =
new StringBuilder();
2350 sql.append(
"select * from ");
2351 sql.append(tableName);
2354 preparedStatement = conn.prepareStatement(sql.toString());
2355 resultSet = preparedStatement.executeQuery();
2356 instanceTableCallback.
process(resultSet);
2357 }
catch (SQLException ex) {
2358 throw new EamDbException(
"Error getting all artifact instances from instances table", ex);
2378 throw new EamDbException(
"Correlation type is null");
2381 if (instanceTableCallback == null) {
2382 throw new EamDbException(
"Callback interface is null");
2385 if (whereClause == null) {
2386 throw new EamDbException(
"Where clause is null");
2389 Connection conn = connect();
2390 PreparedStatement preparedStatement = null;
2391 ResultSet resultSet = null;
2393 StringBuilder sql =
new StringBuilder(300);
2394 sql.append(
"select * from ")
2397 .append(whereClause);
2400 preparedStatement = conn.prepareStatement(sql.toString());
2401 resultSet = preparedStatement.executeQuery();
2402 instanceTableCallback.
process(resultSet);
2403 }
catch (SQLException ex) {
2404 throw new EamDbException(
"Error getting all artifact instances from instances table", ex);
2414 if (eamOrg == null) {
2415 throw new EamDbException(
"EamOrganization is null");
2416 }
else if (eamOrg.
getOrgID() != -1) {
2417 throw new EamDbException(
"EamOrganization already has an ID");
2420 Connection conn = connect();
2421 ResultSet generatedKeys = null;
2422 PreparedStatement preparedStatement = null;
2423 String sql =
"INSERT INTO organizations(org_name, poc_name, poc_email, poc_phone) VALUES (?, ?, ?, ?) "
2424 + getConflictClause();
2427 preparedStatement = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
2428 preparedStatement.setString(1, eamOrg.
getName());
2429 preparedStatement.setString(2, eamOrg.
getPocName());
2430 preparedStatement.setString(3, eamOrg.
getPocEmail());
2431 preparedStatement.setString(4, eamOrg.
getPocPhone());
2433 preparedStatement.executeUpdate();
2434 generatedKeys = preparedStatement.getGeneratedKeys();
2435 if (generatedKeys.next()) {
2436 eamOrg.setOrgID((
int) generatedKeys.getLong(1));
2439 throw new SQLException(
"Creating user failed, no ID obtained.");
2441 }
catch (SQLException ex) {
2442 throw new EamDbException(
"Error inserting new organization.", ex);
2458 public List<EamOrganization> getOrganizations()
throws EamDbException {
2459 Connection conn = connect();
2461 List<EamOrganization> orgs =
new ArrayList<>();
2462 PreparedStatement preparedStatement = null;
2463 ResultSet resultSet = null;
2464 String sql =
"SELECT * FROM organizations";
2467 preparedStatement = conn.prepareStatement(sql);
2468 resultSet = preparedStatement.executeQuery();
2469 while (resultSet.next()) {
2470 orgs.add(getEamOrganizationFromResultSet(resultSet));
2474 }
catch (SQLException ex) {
2475 throw new EamDbException(
"Error getting all organizations.", ex);
2493 public EamOrganization getOrganizationByID(
int orgID)
throws EamDbException {
2494 Connection conn = connect();
2496 PreparedStatement preparedStatement = null;
2497 ResultSet resultSet = null;
2498 String sql =
"SELECT * FROM organizations WHERE id=?";
2501 preparedStatement = conn.prepareStatement(sql);
2502 preparedStatement.setInt(1, orgID);
2503 resultSet = preparedStatement.executeQuery();
2505 return getEamOrganizationFromResultSet(resultSet);
2507 }
catch (SQLException ex) {
2508 throw new EamDbException(
"Error getting organization by id.", ex);
2526 public EamOrganization getReferenceSetOrganization(
int referenceSetID)
throws EamDbException {
2528 EamGlobalSet globalSet = getReferenceSetByID(referenceSetID);
2529 if (globalSet == null) {
2530 throw new EamDbException(
"Reference set with ID " + referenceSetID +
" not found");
2532 return (getOrganizationByID(globalSet.
getOrgID()));
2544 throw new EamDbException(
"EamOrganization is null");
2546 throw new EamDbException(
"Organization has -1 row ID");
2559 public void updateOrganization(
EamOrganization updatedOrganization)
throws EamDbException {
2560 testArgument(updatedOrganization);
2562 Connection conn = connect();
2563 PreparedStatement preparedStatement = null;
2564 String sql =
"UPDATE organizations SET org_name = ?, poc_name = ?, poc_email = ?, poc_phone = ? WHERE id = ?";
2566 preparedStatement = conn.prepareStatement(sql);
2567 preparedStatement.setString(1, updatedOrganization.
getName());
2568 preparedStatement.setString(2, updatedOrganization.
getPocName());
2569 preparedStatement.setString(3, updatedOrganization.
getPocEmail());
2570 preparedStatement.setString(4, updatedOrganization.
getPocPhone());
2571 preparedStatement.setInt(5, updatedOrganization.
getOrgID());
2572 preparedStatement.executeUpdate();
2573 }
catch (SQLException ex) {
2574 throw new EamDbException(
"Error updating organization.", ex);
2582 public void deleteOrganization(
EamOrganization organizationToDelete)
throws EamDbException {
2583 testArgument(organizationToDelete);
2585 Connection conn = connect();
2586 PreparedStatement checkIfUsedStatement = null;
2587 ResultSet resultSet = null;
2588 String checkIfUsedSql =
"SELECT (select count(*) FROM cases WHERE org_id=?) + (select count(*) FROM reference_sets WHERE org_id=?)";
2589 PreparedStatement deleteOrgStatement = null;
2590 String deleteOrgSql =
"DELETE FROM organizations WHERE id=?";
2592 checkIfUsedStatement = conn.prepareStatement(checkIfUsedSql);
2593 checkIfUsedStatement.setInt(1, organizationToDelete.
getOrgID());
2594 checkIfUsedStatement.setInt(2, organizationToDelete.
getOrgID());
2595 resultSet = checkIfUsedStatement.executeQuery();
2597 if (resultSet.getLong(1) > 0) {
2598 throw new EamDbException(
"Can not delete organization which is currently in use by a case or reference set in the central repository.");
2600 deleteOrgStatement = conn.prepareStatement(deleteOrgSql);
2601 deleteOrgStatement.setInt(1, organizationToDelete.
getOrgID());
2602 deleteOrgStatement.executeUpdate();
2603 }
catch (SQLException ex) {
2604 throw new EamDbException(
"Error executing query when attempting to delete organization by id.", ex);
2623 public int newReferenceSet(
EamGlobalSet eamGlobalSet)
throws EamDbException {
2624 if (eamGlobalSet == null) {
2625 throw new EamDbException(
"EamGlobalSet is null");
2629 throw new EamDbException(
"File known status on the EamGlobalSet is null");
2632 if (eamGlobalSet.
getType() == null) {
2633 throw new EamDbException(
"Type on the EamGlobalSet is null");
2636 Connection conn = connect();
2638 PreparedStatement preparedStatement1 = null;
2639 PreparedStatement preparedStatement2 = null;
2640 ResultSet resultSet = null;
2641 String sql1 =
"INSERT INTO reference_sets(org_id, set_name, version, known_status, read_only, type, import_date) VALUES (?, ?, ?, ?, ?, ?, ?) "
2642 + getConflictClause();
2643 String sql2 =
"SELECT id FROM reference_sets WHERE org_id=? AND set_name=? AND version=? AND import_date=? LIMIT 1";
2646 preparedStatement1 = conn.prepareStatement(sql1);
2647 preparedStatement1.setInt(1, eamGlobalSet.
getOrgID());
2648 preparedStatement1.setString(2, eamGlobalSet.
getSetName());
2649 preparedStatement1.setString(3, eamGlobalSet.
getVersion());
2651 preparedStatement1.setBoolean(5, eamGlobalSet.
isReadOnly());
2652 preparedStatement1.setInt(6, eamGlobalSet.
getType().getId());
2653 preparedStatement1.setString(7, eamGlobalSet.
getImportDate().toString());
2655 preparedStatement1.executeUpdate();
2657 preparedStatement2 = conn.prepareStatement(sql2);
2658 preparedStatement2.setInt(1, eamGlobalSet.
getOrgID());
2659 preparedStatement2.setString(2, eamGlobalSet.
getSetName());
2660 preparedStatement2.setString(3, eamGlobalSet.
getVersion());
2661 preparedStatement2.setString(4, eamGlobalSet.
getImportDate().toString());
2663 resultSet = preparedStatement2.executeQuery();
2665 return resultSet.getInt(
"id");
2667 }
catch (SQLException ex) {
2668 throw new EamDbException(
"Error inserting new global set.", ex);
2687 public EamGlobalSet getReferenceSetByID(
int referenceSetID)
throws EamDbException {
2688 Connection conn = connect();
2690 PreparedStatement preparedStatement1 = null;
2691 ResultSet resultSet = null;
2692 String sql1 =
"SELECT * FROM reference_sets WHERE id=?";
2695 preparedStatement1 = conn.prepareStatement(sql1);
2696 preparedStatement1.setInt(1, referenceSetID);
2697 resultSet = preparedStatement1.executeQuery();
2698 if (resultSet.next()) {
2699 return getEamGlobalSetFromResultSet(resultSet);
2704 }
catch (SQLException ex) {
2705 throw new EamDbException(
"Error getting reference set by id.", ex);
2725 if (correlationType == null) {
2726 throw new EamDbException(
"Correlation type is null");
2729 List<EamGlobalSet> results =
new ArrayList<>();
2730 Connection conn = connect();
2732 PreparedStatement preparedStatement1 = null;
2733 ResultSet resultSet = null;
2734 String sql1 =
"SELECT * FROM reference_sets WHERE type=" + correlationType.getId();
2737 preparedStatement1 = conn.prepareStatement(sql1);
2738 resultSet = preparedStatement1.executeQuery();
2739 while (resultSet.next()) {
2740 results.add(getEamGlobalSetFromResultSet(resultSet));
2743 }
catch (SQLException ex) {
2744 throw new EamDbException(
"Error getting reference sets.", ex);
2765 throw new EamDbException(
"Known status of EamGlobalFileInstance is null");
2767 if (correlationType == null) {
2768 throw new EamDbException(
"Correlation type is null");
2771 Connection conn = connect();
2773 PreparedStatement preparedStatement = null;
2775 String sql =
"INSERT INTO %s(reference_set_id, value, known_status, comment) VALUES (?, ?, ?, ?) "
2776 + getConflictClause();
2780 preparedStatement.setInt(1, eamGlobalFileInstance.
getGlobalSetID());
2781 preparedStatement.setString(2, eamGlobalFileInstance.
getMD5Hash());
2782 preparedStatement.setByte(3, eamGlobalFileInstance.
getKnownStatus().getFileKnownValue());
2783 preparedStatement.setString(4, eamGlobalFileInstance.
getComment());
2784 preparedStatement.executeUpdate();
2785 }
catch (SQLException ex) {
2786 throw new EamDbException(
"Error inserting new reference instance into reference_ table.", ex);
2806 public boolean referenceSetExists(String referenceSetName, String version)
throws EamDbException {
2807 Connection conn = connect();
2809 PreparedStatement preparedStatement1 = null;
2810 ResultSet resultSet = null;
2811 String sql1 =
"SELECT * FROM reference_sets WHERE set_name=? AND version=?";
2814 preparedStatement1 = conn.prepareStatement(sql1);
2815 preparedStatement1.setString(1, referenceSetName);
2816 preparedStatement1.setString(2, version);
2817 resultSet = preparedStatement1.executeQuery();
2818 return (resultSet.next());
2820 }
catch (SQLException ex) {
2821 throw new EamDbException(
"Error testing whether reference set exists (name: " + referenceSetName
2822 +
" version: " + version, ex);
2836 public void bulkInsertReferenceTypeEntries(Set<EamGlobalFileInstance> globalInstances,
CorrelationAttributeInstance.
Type contentType)
throws EamDbException {
2837 if (contentType == null) {
2838 throw new EamDbException(
"Correlation type is null");
2840 if (globalInstances == null) {
2841 throw new EamDbException(
"Null set of EamGlobalFileInstance");
2844 Connection conn = connect();
2846 PreparedStatement bulkPs = null;
2848 conn.setAutoCommit(
false);
2851 String sql =
"INSERT INTO %s(reference_set_id, value, known_status, comment) VALUES (?, ?, ?, ?) "
2852 + getConflictClause();
2857 if (globalInstance.getKnownStatus() == null) {
2858 throw new EamDbException(
"EamGlobalFileInstance with value " + globalInstance.getMD5Hash() +
" has null known status");
2861 bulkPs.setInt(1, globalInstance.getGlobalSetID());
2862 bulkPs.setString(2, globalInstance.getMD5Hash());
2863 bulkPs.setByte(3, globalInstance.getKnownStatus().getFileKnownValue());
2864 bulkPs.setString(4, globalInstance.getComment());
2868 bulkPs.executeBatch();
2870 }
catch (SQLException | EamDbException ex) {
2873 }
catch (SQLException ex2) {
2876 throw new EamDbException(
"Error inserting bulk artifacts.", ex);
2894 public List<EamGlobalFileInstance> getReferenceInstancesByTypeValue(
CorrelationAttributeInstance.
Type aType, String aValue)
throws EamDbException, CorrelationAttributeNormalizationException {
2897 Connection conn = connect();
2899 List<EamGlobalFileInstance> globalFileInstances =
new ArrayList<>();
2900 PreparedStatement preparedStatement1 = null;
2901 ResultSet resultSet = null;
2902 String sql1 =
"SELECT * FROM %s WHERE value=?";
2906 preparedStatement1.setString(1, normalizeValued);
2907 resultSet = preparedStatement1.executeQuery();
2908 while (resultSet.next()) {
2909 globalFileInstances.add(getEamGlobalFileInstanceFromResultSet(resultSet));
2912 }
catch (SQLException ex) {
2913 throw new EamDbException(
"Error getting reference instances by type and value.", ex);
2920 return globalFileInstances;
2934 if (newType == null) {
2935 throw new EamDbException(
"Correlation type is null");
2938 if (-1 == newType.getId()) {
2939 typeId = newCorrelationTypeNotKnownId(newType);
2941 typeId = newCorrelationTypeKnownId(newType);
2958 Connection conn = connect();
2960 PreparedStatement preparedStatement = null;
2961 PreparedStatement preparedStatementQuery = null;
2962 ResultSet resultSet = null;
2967 insertSql =
"INSERT INTO correlation_types(display_name, db_table_name, supported, enabled) VALUES (?, ?, ?, ?) " + getConflictClause();
2969 querySql =
"SELECT * FROM correlation_types WHERE display_name=? AND db_table_name=?";
2972 preparedStatement = conn.prepareStatement(insertSql);
2974 preparedStatement.setString(1, newType.getDisplayName());
2975 preparedStatement.setString(2, newType.getDbTableName());
2976 preparedStatement.setInt(3, newType.isSupported() ? 1 : 0);
2977 preparedStatement.setInt(4, newType.isEnabled() ? 1 : 0);
2979 preparedStatement.executeUpdate();
2981 preparedStatementQuery = conn.prepareStatement(querySql);
2982 preparedStatementQuery.setString(1, newType.getDisplayName());
2983 preparedStatementQuery.setString(2, newType.getDbTableName());
2985 resultSet = preparedStatementQuery.executeQuery();
2986 if (resultSet.next()) {
2988 typeId = correlationType.getId();
2990 }
catch (SQLException ex) {
2991 throw new EamDbException(
"Error inserting new correlation type.", ex);
3011 Connection conn = connect();
3013 PreparedStatement preparedStatement = null;
3014 PreparedStatement preparedStatementQuery = null;
3015 ResultSet resultSet = null;
3020 insertSql =
"INSERT INTO correlation_types(id, display_name, db_table_name, supported, enabled) VALUES (?, ?, ?, ?, ?) " + getConflictClause();
3022 querySql =
"SELECT * FROM correlation_types WHERE display_name=? AND db_table_name=?";
3025 preparedStatement = conn.prepareStatement(insertSql);
3027 preparedStatement.setInt(1, newType.getId());
3028 preparedStatement.setString(2, newType.getDisplayName());
3029 preparedStatement.setString(3, newType.getDbTableName());
3030 preparedStatement.setInt(4, newType.isSupported() ? 1 : 0);
3031 preparedStatement.setInt(5, newType.isEnabled() ? 1 : 0);
3033 preparedStatement.executeUpdate();
3035 preparedStatementQuery = conn.prepareStatement(querySql);
3036 preparedStatementQuery.setString(1, newType.getDisplayName());
3037 preparedStatementQuery.setString(2, newType.getDbTableName());
3039 resultSet = preparedStatementQuery.executeQuery();
3040 if (resultSet.next()) {
3042 typeId = correlationType.getId();
3044 }
catch (SQLException ex) {
3045 throw new EamDbException(
"Error inserting new correlation type.", ex);
3057 Connection conn = connect();
3060 PreparedStatement preparedStatement = null;
3061 ResultSet resultSet = null;
3062 String sql =
"SELECT * FROM correlation_types";
3065 preparedStatement = conn.prepareStatement(sql);
3066 resultSet = preparedStatement.executeQuery();
3067 while (resultSet.next()) {
3068 aTypes.add(getCorrelationTypeFromResultSet(resultSet));
3072 }
catch (SQLException ex) {
3073 throw new EamDbException(
"Error getting all correlation types.", ex);
3092 Connection conn = connect();
3095 PreparedStatement preparedStatement = null;
3096 ResultSet resultSet = null;
3097 String sql =
"SELECT * FROM correlation_types WHERE enabled=1";
3100 preparedStatement = conn.prepareStatement(sql);
3101 resultSet = preparedStatement.executeQuery();
3102 while (resultSet.next()) {
3103 aTypes.add(getCorrelationTypeFromResultSet(resultSet));
3107 }
catch (SQLException ex) {
3108 throw new EamDbException(
"Error getting enabled correlation types.", ex);
3127 Connection conn = connect();
3130 PreparedStatement preparedStatement = null;
3131 ResultSet resultSet = null;
3132 String sql =
"SELECT * FROM correlation_types WHERE supported=1";
3135 preparedStatement = conn.prepareStatement(sql);
3136 resultSet = preparedStatement.executeQuery();
3137 while (resultSet.next()) {
3138 aTypes.add(getCorrelationTypeFromResultSet(resultSet));
3142 }
catch (SQLException ex) {
3143 throw new EamDbException(
"Error getting supported correlation types.", ex);
3160 Connection conn = connect();
3162 PreparedStatement preparedStatement = null;
3163 String sql =
"UPDATE correlation_types SET display_name=?, db_table_name=?, supported=?, enabled=? WHERE id=?";
3166 preparedStatement = conn.prepareStatement(sql);
3167 preparedStatement.setString(1, aType.getDisplayName());
3168 preparedStatement.setString(2, aType.getDbTableName());
3169 preparedStatement.setInt(3, aType.isSupported() ? 1 : 0);
3170 preparedStatement.setInt(4, aType.isEnabled() ? 1 : 0);
3171 preparedStatement.setInt(5, aType.getId());
3172 preparedStatement.executeUpdate();
3173 typeCache.put(aType.getId(), aType);
3174 }
catch (SQLException ex) {
3175 throw new EamDbException(
"Error updating correlation type.", ex);
3195 return typeCache.get(typeId, () -> getCorrelationTypeByIdFromCr(typeId));
3196 }
catch (CacheLoader.InvalidCacheLoadException ignored) {
3199 }
catch (ExecutionException ex) {
3200 throw new EamDbException(
"Error getting correlation type", ex);
3214 Connection conn = connect();
3217 PreparedStatement preparedStatement = null;
3218 ResultSet resultSet = null;
3219 String sql =
"SELECT * FROM correlation_types WHERE id=?";
3222 preparedStatement = conn.prepareStatement(sql);
3223 preparedStatement.setInt(1, typeId);
3224 resultSet = preparedStatement.executeQuery();
3225 if (resultSet.next()) {
3226 aType = getCorrelationTypeFromResultSet(resultSet);
3229 throw new EamDbException(
"Failed to find entry for correlation type ID = " + typeId);
3232 }
catch (SQLException ex) {
3233 throw new EamDbException(
"Error getting correlation type by id.", ex);
3251 private CorrelationCase getEamCaseFromResultSet(ResultSet resultSet)
throws SQLException {
3252 if (null == resultSet) {
3258 resultSet.getInt(
"org_id");
3259 if (!resultSet.wasNull()) {
3262 resultSet.getString(
"org_name"),
3263 resultSet.getString(
"poc_name"),
3264 resultSet.getString(
"poc_email"),
3265 resultSet.getString(
"poc_phone"));
3268 CorrelationCase eamCase =
new CorrelationCase(resultSet.getInt(
"case_id"), resultSet.getString(
"case_uid"), eamOrg, resultSet.getString(
"case_name"),
3269 resultSet.getString(
"creation_date"), resultSet.getString(
"case_number"), resultSet.getString(
"examiner_name"),
3270 resultSet.getString(
"examiner_email"), resultSet.getString(
"examiner_phone"), resultSet.getString(
"notes"));
3275 private CorrelationDataSource getEamDataSourceFromResultSet(ResultSet resultSet)
throws SQLException {
3276 if (null == resultSet) {
3281 resultSet.getInt(
"case_id"),
3282 resultSet.getInt(
"id"),
3283 resultSet.getString(
"device_id"),
3284 resultSet.getString(
"name"),
3285 resultSet.getLong(
"datasource_obj_id"),
3286 resultSet.getString(
"md5"),
3287 resultSet.getString(
"sha1"),
3288 resultSet.getString(
"sha256")
3291 return eamDataSource;
3295 if (null == resultSet) {
3300 resultSet.getInt(
"id"),
3301 resultSet.getString(
"display_name"),
3302 resultSet.getString(
"db_table_name"),
3303 resultSet.getBoolean(
"supported"),
3304 resultSet.getBoolean(
"enabled")
3307 return eamArtifactType;
3321 if (null == resultSet) {
3326 resultSet.getString(
"value"),
3327 resultSet.getInt(
"id"),
3328 new CorrelationCase(resultSet.getInt(
"case_id"), resultSet.getString(
"case_uid"), resultSet.getString(
"case_name")),
3330 resultSet.getInt(
"case_id"), resultSet.getInt(
"data_source_id"), resultSet.getString(
"device_id"), resultSet.getString(
"name"),
3331 resultSet.getLong(
"datasource_obj_id"), resultSet.getString(
"md5"), resultSet.getString(
"sha1"), resultSet.getString(
"sha256")),
3332 resultSet.getString(
"file_path"),
3333 resultSet.getString(
"comment"),
3334 TskData.FileKnown.valueOf(resultSet.getByte(
"known_status")),
3335 resultSet.getLong(
"file_obj_id"));
3338 private EamOrganization getEamOrganizationFromResultSet(ResultSet resultSet)
throws SQLException {
3339 if (null == resultSet) {
3344 resultSet.getInt(
"id"),
3345 resultSet.getString(
"org_name"),
3346 resultSet.getString(
"poc_name"),
3347 resultSet.getString(
"poc_email"),
3348 resultSet.getString(
"poc_phone")
3352 private EamGlobalSet getEamGlobalSetFromResultSet(ResultSet resultSet)
throws SQLException, EamDbException {
3353 if (null == resultSet) {
3358 resultSet.getInt(
"id"),
3359 resultSet.getInt(
"org_id"),
3360 resultSet.getString(
"set_name"),
3361 resultSet.getString(
"version"),
3362 TskData.FileKnown.valueOf(resultSet.getByte(
"known_status")),
3363 resultSet.getBoolean(
"read_only"),
3365 LocalDate.parse(resultSet.getString(
"import_date"))
3369 private EamGlobalFileInstance getEamGlobalFileInstanceFromResultSet(ResultSet resultSet)
throws SQLException, EamDbException, CorrelationAttributeNormalizationException {
3370 if (null == resultSet) {
3375 resultSet.getInt(
"id"),
3376 resultSet.getInt(
"reference_set_id"),
3377 resultSet.getString(
"value"),
3378 TskData.FileKnown.valueOf(resultSet.getByte(
"known_status")),
3379 resultSet.getString(
"comment")
3393 abstract boolean doesColumnExist(Connection conn, String tableName, String columnName)
throws SQLException;
3400 @Messages({
"AbstractSqlEamDb.upgradeSchema.incompatible=The selected Central Repository is not compatible with the current version of the application, please upgrade the application if you wish to use this Central Repository."})
3404 ResultSet resultSet = null;
3405 Statement statement = null;
3406 PreparedStatement preparedStatement = null;
3407 Connection conn = null;
3411 conn = connect(
false);
3412 conn.setAutoCommit(
false);
3413 statement = conn.createStatement();
3415 int minorVersion = 0;
3416 String minorVersionStr = null;
3417 resultSet = statement.executeQuery(
"SELECT value FROM db_info WHERE name='" + AbstractSqlEamDb.SCHEMA_MINOR_VERSION_KEY +
"'");
3418 if (resultSet.next()) {
3419 minorVersionStr = resultSet.getString(
"value");
3421 minorVersion = Integer.parseInt(minorVersionStr);
3422 }
catch (NumberFormatException ex) {
3423 throw new EamDbException(
"Bad value for schema minor version (" + minorVersionStr +
") - database is corrupt", ex);
3426 throw new EamDbException(
"Failed to read schema minor version from db_info table");
3429 int majorVersion = 0;
3430 String majorVersionStr = null;
3431 resultSet = statement.executeQuery(
"SELECT value FROM db_info WHERE name='" + AbstractSqlEamDb.SCHEMA_MAJOR_VERSION_KEY +
"'");
3432 if (resultSet.next()) {
3433 majorVersionStr = resultSet.getString(
"value");
3435 majorVersion = Integer.parseInt(majorVersionStr);
3436 }
catch (NumberFormatException ex) {
3437 throw new EamDbException(
"Bad value for schema version (" + majorVersionStr +
") - database is corrupt", ex);
3440 throw new EamDbException(
"Failed to read schema major version from db_info table");
3451 CaseDbSchemaVersionNumber dbSchemaVersion =
new CaseDbSchemaVersionNumber(majorVersion, minorVersion);
3456 if (SOFTWARE_CR_DB_SCHEMA_VERSION.getMajor() < dbSchemaVersion.getMajor()) {
3459 if (dbSchemaVersion.equals(SOFTWARE_CR_DB_SCHEMA_VERSION)) {
3460 logger.log(Level.INFO,
"Central Repository is up to date");
3463 if (dbSchemaVersion.compareTo(SOFTWARE_CR_DB_SCHEMA_VERSION) > 0) {
3464 logger.log(Level.INFO,
"Central Repository is of newer version than software creates");
3471 if (dbSchemaVersion.compareTo(
new CaseDbSchemaVersionNumber(1, 1)) < 0) {
3472 statement.execute(
"ALTER TABLE reference_sets ADD COLUMN known_status INTEGER;");
3473 statement.execute(
"ALTER TABLE reference_sets ADD COLUMN read_only BOOLEAN;");
3474 statement.execute(
"ALTER TABLE reference_sets ADD COLUMN type INTEGER;");
3479 EamDbUtil.insertDefaultOrganization(conn);
3485 if (dbSchemaVersion.compareTo(
new CaseDbSchemaVersionNumber(1, 2)) < 0) {
3486 final String addIntegerColumnTemplate =
"ALTER TABLE %s ADD COLUMN %s INTEGER;";
3487 final String addSsidTableTemplate;
3488 final String addCaseIdIndexTemplate;
3489 final String addDataSourceIdIndexTemplate;
3490 final String addValueIndexTemplate;
3491 final String addKnownStatusIndexTemplate;
3492 final String addObjectIdIndexTemplate;
3494 final String addAttributeSql;
3496 switch (selectedPlatform) {
3498 addAttributeSql =
"INSERT INTO correlation_types(id, display_name, db_table_name, supported, enabled) VALUES (?, ?, ?, ?, ?) " + getConflictClause();
3508 addAttributeSql =
"INSERT OR IGNORE INTO correlation_types(id, display_name, db_table_name, supported, enabled) VALUES (?, ?, ?, ?, ?)";
3518 throw new EamDbException(
"Currently selected database platform \"" + selectedPlatform.name() +
"\" can not be upgraded.");
3520 final String dataSourcesTableName =
"data_sources";
3521 final String dataSourceObjectIdColumnName =
"datasource_obj_id";
3522 if (!doesColumnExist(conn, dataSourcesTableName, dataSourceObjectIdColumnName)) {
3523 statement.execute(String.format(addIntegerColumnTemplate, dataSourcesTableName, dataSourceObjectIdColumnName));
3525 final String dataSourceObjectIdIndexTemplate =
"CREATE INDEX IF NOT EXISTS datasource_object_id ON data_sources (%s)";
3526 statement.execute(String.format(dataSourceObjectIdIndexTemplate, dataSourceObjectIdColumnName));
3527 List<String> instaceTablesToAdd =
new ArrayList<>();
3529 final String wirelessNetworksDbTableName =
"wireless_networks";
3530 instaceTablesToAdd.add(wirelessNetworksDbTableName +
"_instances");
3531 final String macAddressDbTableName =
"mac_address";
3532 instaceTablesToAdd.add(macAddressDbTableName +
"_instances");
3533 final String imeiNumberDbTableName =
"imei_number";
3534 instaceTablesToAdd.add(imeiNumberDbTableName +
"_instances");
3535 final String iccidNumberDbTableName =
"iccid_number";
3536 instaceTablesToAdd.add(iccidNumberDbTableName +
"_instances");
3537 final String imsiNumberDbTableName =
"imsi_number";
3538 instaceTablesToAdd.add(imsiNumberDbTableName +
"_instances");
3541 preparedStatement = conn.prepareStatement(addAttributeSql);
3543 preparedStatement.setString(2, Bundle.CorrelationType_SSID_displayName());
3544 preparedStatement.setString(3, wirelessNetworksDbTableName);
3545 preparedStatement.setInt(4, 1);
3546 preparedStatement.setInt(5, 1);
3547 preparedStatement.execute();
3550 preparedStatement = conn.prepareStatement(addAttributeSql);
3552 preparedStatement.setString(2, Bundle.CorrelationType_MAC_displayName());
3553 preparedStatement.setString(3, macAddressDbTableName);
3554 preparedStatement.setInt(4, 1);
3555 preparedStatement.setInt(5, 1);
3556 preparedStatement.execute();
3559 preparedStatement = conn.prepareStatement(addAttributeSql);
3561 preparedStatement.setString(2, Bundle.CorrelationType_IMEI_displayName());
3562 preparedStatement.setString(3, imeiNumberDbTableName);
3563 preparedStatement.setInt(4, 1);
3564 preparedStatement.setInt(5, 1);
3565 preparedStatement.execute();
3568 preparedStatement = conn.prepareStatement(addAttributeSql);
3570 preparedStatement.setString(2, Bundle.CorrelationType_IMSI_displayName());
3571 preparedStatement.setString(3, imsiNumberDbTableName);
3572 preparedStatement.setInt(4, 1);
3573 preparedStatement.setInt(5, 1);
3574 preparedStatement.execute();
3577 preparedStatement = conn.prepareStatement(addAttributeSql);
3579 preparedStatement.setString(2, Bundle.CorrelationType_ICCID_displayName());
3580 preparedStatement.setString(3, iccidNumberDbTableName);
3581 preparedStatement.setInt(4, 1);
3582 preparedStatement.setInt(5, 1);
3583 preparedStatement.execute();
3586 for (String tableName : instaceTablesToAdd) {
3587 statement.execute(String.format(addSsidTableTemplate, tableName, tableName));
3588 statement.execute(String.format(addCaseIdIndexTemplate, tableName, tableName));
3589 statement.execute(String.format(addDataSourceIdIndexTemplate, tableName, tableName));
3590 statement.execute(String.format(addValueIndexTemplate, tableName, tableName));
3591 statement.execute(String.format(addKnownStatusIndexTemplate, tableName, tableName));
3595 String instance_type_dbname;
3596 final String objectIdColumnName =
"file_obj_id";
3599 if (!doesColumnExist(conn, instance_type_dbname, objectIdColumnName)) {
3600 statement.execute(String.format(addIntegerColumnTemplate, instance_type_dbname, objectIdColumnName));
3602 statement.execute(String.format(addObjectIdIndexTemplate, instance_type_dbname, instance_type_dbname));
3608 if (!doesColumnExist(conn, dataSourcesTableName,
"md5")) {
3609 statement.execute(
"ALTER TABLE data_sources ADD COLUMN md5 TEXT DEFAULT NULL");
3611 if (!doesColumnExist(conn, dataSourcesTableName,
"sha1")) {
3612 statement.execute(
"ALTER TABLE data_sources ADD COLUMN sha1 TEXT DEFAULT NULL");
3614 if (!doesColumnExist(conn, dataSourcesTableName,
"sha256")) {
3615 statement.execute(
"ALTER TABLE data_sources ADD COLUMN sha256 TEXT DEFAULT NULL");
3626 String creationMajorVer;
3627 resultSet = statement.executeQuery(
"SELECT value FROM db_info WHERE name = '" + AbstractSqlEamDb.CREATION_SCHEMA_MAJOR_VERSION_KEY +
"'");
3628 if (resultSet.next()) {
3629 creationMajorVer = resultSet.getString(
"value");
3631 creationMajorVer =
"0";
3633 String creationMinorVer;
3634 resultSet = statement.executeQuery(
"SELECT value FROM db_info WHERE name = '" + AbstractSqlEamDb.CREATION_SCHEMA_MINOR_VERSION_KEY +
"'");
3635 if (resultSet.next()) {
3636 creationMinorVer = resultSet.getString(
"value");
3638 creationMinorVer =
"0";
3640 statement.execute(
"DROP TABLE db_info");
3642 statement.execute(
"CREATE TABLE db_info (id SERIAL, name TEXT UNIQUE NOT NULL, value TEXT NOT NULL)");
3644 statement.execute(
"CREATE TABLE db_info (id INTEGER PRIMARY KEY, name TEXT UNIQUE NOT NULL, value TEXT NOT NULL)");
3646 statement.execute(
"INSERT INTO db_info (name, value) VALUES ('" + AbstractSqlEamDb.SCHEMA_MAJOR_VERSION_KEY +
"','" + majorVersionStr +
"')");
3647 statement.execute(
"INSERT INTO db_info (name, value) VALUES ('" + AbstractSqlEamDb.SCHEMA_MINOR_VERSION_KEY +
"','" + minorVersionStr +
"')");
3648 statement.execute(
"INSERT INTO db_info (name, value) VALUES ('" + AbstractSqlEamDb.CREATION_SCHEMA_MAJOR_VERSION_KEY +
"','" + creationMajorVer +
"')");
3649 statement.execute(
"INSERT INTO db_info (name, value) VALUES ('" + AbstractSqlEamDb.CREATION_SCHEMA_MINOR_VERSION_KEY +
"','" + creationMinorVer +
"')");
3654 if (dbSchemaVersion.compareTo(
new CaseDbSchemaVersionNumber(1, 3)) < 0) {
3655 switch (selectedPlatform) {
3657 statement.execute(
"ALTER TABLE data_sources DROP CONSTRAINT datasource_unique");
3659 statement.execute(
"ALTER TABLE data_sources ADD CONSTRAINT datasource_unique UNIQUE (case_id, device_id, name, datasource_obj_id)");
3663 statement.execute(
"DROP INDEX IF EXISTS data_sources_name");
3664 statement.execute(
"DROP INDEX IF EXISTS data_sources_object_id");
3665 statement.execute(
"ALTER TABLE data_sources RENAME TO old_data_sources");
3667 statement.execute(
"CREATE TABLE IF NOT EXISTS data_sources (id integer primary key autoincrement NOT NULL,"
3668 +
"case_id integer NOT NULL,device_id text NOT NULL,name text NOT NULL,datasource_obj_id integer,"
3669 +
"md5 text DEFAULT NULL,sha1 text DEFAULT NULL,sha256 text DEFAULT NULL,"
3670 +
"foreign key (case_id) references cases(id) ON UPDATE SET NULL ON DELETE SET NULL,"
3671 +
"CONSTRAINT datasource_unique UNIQUE (case_id, device_id, name, datasource_obj_id))");
3674 statement.execute(
"INSERT INTO data_sources SELECT * FROM old_data_sources");
3675 statement.execute(
"DROP TABLE old_data_sources");
3678 throw new EamDbException(
"Currently selected database platform \"" + selectedPlatform.name() +
"\" can not be upgraded.");
3681 updateSchemaVersion(conn);
3683 logger.log(Level.INFO, String.format(
"Central Repository schema updated to version %s", SOFTWARE_CR_DB_SCHEMA_VERSION));
3685 }
catch (SQLException | EamDbException ex) {
3690 }
catch (SQLException ex2) {
3691 logger.log(Level.SEVERE, String.format(
"Central Repository rollback of failed schema update to %s failed", SOFTWARE_CR_DB_SCHEMA_VERSION), ex2);
TskData.FileKnown getKnownStatus()
String getExaminerPhone()
CorrelationAttributeInstance.Type getType()
static String correlationTypeToInstanceTableName(CorrelationAttributeInstance.Type type)
static final int ICCID_TYPE_ID
static void closeResultSet(ResultSet resultSet)
static void closeStatement(Statement statement)
static List< CorrelationAttributeInstance.Type > getDefaultCorrelationTypes()
String getCorrelationValue()
static final int IMEI_TYPE_ID
static TimingMetric getTimingMetric(String name)
TskData.FileKnown getKnownStatus()
void process(ResultSet resultSet)
Type getCorrelationType()
static String normalize(CorrelationAttributeInstance.Type attributeType, String data)
static EamDb getInstance()
static void closeConnection(Connection conn)
void setKnownStatus(TskData.FileKnown knownStatus)
CorrelationAttributeInstance.Type getCorrelationTypeById(int typeId)
TskData.FileKnown getFileKnownStatus()
CorrelationDataSource getCorrelationDataSource()
String getExaminerEmail()
LocalDate getImportDate()
String getExaminerPhone()
static final int MAC_TYPE_ID
Long getDataSourceObjectID()
static void submitTimingMetric(TimingMetric metric)
static final int IMSI_TYPE_ID
synchronized static Logger getLogger(String name)
CorrelationCase getCorrelationCase()
static String correlationTypeToReferenceTableName(CorrelationAttributeInstance.Type type)
static EamOrganization getDefault()
static final int SSID_TYPE_ID
static final int FILES_TYPE_ID
String getExaminerEmail()