Autopsy  4.4.1
Graphical digital forensics platform for The Sleuth Kit and other tools.
AbstractSqlEamDb.java
Go to the documentation of this file.
1 /*
2  * Central Repository
3  *
4  * Copyright 2015-2017 Basis Technology Corp.
5  * Contact: carrier <at> sleuthkit <dot> org
6  *
7  * Licensed under the Apache License, Version 2.0 (the "License");
8  * you may not use this file except in compliance with the License.
9  * You may obtain a copy of the License at
10  *
11  * http://www.apache.org/licenses/LICENSE-2.0
12  *
13  * Unless required by applicable law or agreed to in writing, software
14  * distributed under the License is distributed on an "AS IS" BASIS,
15  * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
16  * See the License for the specific language governing permissions and
17  * limitations under the License.
18  */
19 package org.sleuthkit.autopsy.centralrepository.datamodel;
20 
21 import java.net.UnknownHostException;
22 import java.util.ArrayList;
23 import java.util.List;
24 import java.util.Collection;
25 import java.util.LinkedHashSet;
26 import java.util.stream.Collectors;
27 import java.sql.Connection;
28 import java.sql.PreparedStatement;
29 import java.sql.ResultSet;
30 import java.sql.SQLException;
31 import java.sql.Types;
32 import java.time.LocalDate;
33 import java.util.HashMap;
34 import java.util.Map;
35 import java.util.Set;
37 
39 import org.sleuthkit.datamodel.TskData;
40 
46 public abstract class AbstractSqlEamDb implements EamDb {
47 
48  private final static Logger LOGGER = Logger.getLogger(AbstractSqlEamDb.class.getName());
49 
51 
52  private int bulkArtifactsCount;
53  protected int bulkArtifactsThreshold;
54  private final Map<String, Collection<CorrelationAttribute>> bulkArtifacts;
55  private final List<String> badTags;
56 
62  protected AbstractSqlEamDb() throws EamDbException{
63  badTags = new ArrayList<>();
64  bulkArtifactsCount = 0;
65  bulkArtifacts = new HashMap<>();
66 
68  DEFAULT_CORRELATION_TYPES.forEach((type) -> {
69  bulkArtifacts.put(type.getDbTableName(), new ArrayList<>());
70  });
71  }
72 
76  protected abstract Connection connect() throws EamDbException;
77 
83  @Override
84  public List<String> getBadTags() {
85  synchronized (badTags) {
86  return new ArrayList<>(badTags);
87  }
88  }
89 
95  @Override
96  public void setBadTags(List<String> tags) {
97  synchronized (badTags) {
98  badTags.clear();
99  badTags.addAll(tags);
100  }
101  }
102 
111  @Override
112  public void newDbInfo(String name, String value) throws EamDbException {
113  Connection conn = connect();
114 
115  PreparedStatement preparedStatement = null;
116  String sql = "INSERT INTO db_info (name, value) VALUES (?, ?)";
117  try {
118  preparedStatement = conn.prepareStatement(sql);
119  preparedStatement.setString(1, name);
120  preparedStatement.setString(2, value);
121  preparedStatement.executeUpdate();
122  } catch (SQLException ex) {
123  throw new EamDbException("Error adding new name/value pair to db_info.", ex);
124  } finally {
125  EamDbUtil.closePreparedStatement(preparedStatement);
127  }
128 
129  }
130 
140  @Override
141  public String getDbInfo(String name) throws EamDbException {
142  Connection conn = connect();
143 
144  PreparedStatement preparedStatement = null;
145  ResultSet resultSet = null;
146  String value = null;
147  String sql = "SELECT value FROM db_info WHERE name=?";
148  try {
149  preparedStatement = conn.prepareStatement(sql);
150  preparedStatement.setString(1, name);
151  resultSet = preparedStatement.executeQuery();
152  if (resultSet.next()) {
153  value = resultSet.getString("value");
154  }
155  } catch (SQLException ex) {
156  throw new EamDbException("Error getting value for name.", ex);
157  } finally {
158  EamDbUtil.closePreparedStatement(preparedStatement);
159  EamDbUtil.closeResultSet(resultSet);
161  }
162 
163  return value;
164  }
165 
174  @Override
175  public void updateDbInfo(String name, String value) throws EamDbException {
176  Connection conn = connect();
177 
178  PreparedStatement preparedStatement = null;
179  String sql = "UPDATE db_info SET value=? WHERE name=?";
180  try {
181  preparedStatement = conn.prepareStatement(sql);
182  preparedStatement.setString(1, value);
183  preparedStatement.setString(2, name);
184  preparedStatement.executeUpdate();
185  } catch (SQLException ex) {
186  throw new EamDbException("Error updating value for name.", ex);
187  } finally {
188  EamDbUtil.closePreparedStatement(preparedStatement);
190  }
191  }
192 
200  @Override
201  public void newCase(CorrelationCase eamCase) throws EamDbException {
202  Connection conn = connect();
203 
204  PreparedStatement preparedStatement = null;
205 
206  String sql = "INSERT INTO cases(case_uid, org_id, case_name, creation_date, case_number, "
207  + "examiner_name, examiner_email, examiner_phone, notes) "
208  + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
209 
210  try {
211  preparedStatement = conn.prepareStatement(sql);
212 
213  preparedStatement.setString(1, eamCase.getCaseUUID());
214  if (null == eamCase.getOrg()) {
215  preparedStatement.setNull(2, Types.INTEGER);
216  } else {
217  preparedStatement.setInt(2, eamCase.getOrg().getOrgID());
218  }
219  preparedStatement.setString(3, eamCase.getDisplayName());
220  preparedStatement.setString(4, eamCase.getCreationDate());
221  if ("".equals(eamCase.getCaseNumber())) {
222  preparedStatement.setNull(5, Types.INTEGER);
223  } else {
224  preparedStatement.setString(5, eamCase.getCaseNumber());
225  }
226  if ("".equals(eamCase.getExaminerName())) {
227  preparedStatement.setNull(6, Types.INTEGER);
228  } else {
229  preparedStatement.setString(6, eamCase.getExaminerName());
230  }
231  if ("".equals(eamCase.getExaminerEmail())) {
232  preparedStatement.setNull(7, Types.INTEGER);
233  } else {
234  preparedStatement.setString(7, eamCase.getExaminerEmail());
235  }
236  if ("".equals(eamCase.getExaminerPhone())) {
237  preparedStatement.setNull(8, Types.INTEGER);
238  } else {
239  preparedStatement.setString(8, eamCase.getExaminerPhone());
240  }
241  if ("".equals(eamCase.getNotes())) {
242  preparedStatement.setNull(9, Types.INTEGER);
243  } else {
244  preparedStatement.setString(9, eamCase.getNotes());
245  }
246 
247  preparedStatement.executeUpdate();
248  } catch (SQLException ex) {
249  throw new EamDbException("Error inserting new case.", ex); // NON-NLS
250  } finally {
251  EamDbUtil.closePreparedStatement(preparedStatement);
253  }
254  }
255 
261  @Override
262  public CorrelationCase newCase(Case autopsyCase) throws EamDbException{
263  if(autopsyCase == null){
264  throw new EamDbException("Case is null");
265  }
266 
267  CorrelationCase curCeCase = new CorrelationCase(
268  -1,
269  autopsyCase.getName(), // unique case ID
271  autopsyCase.getDisplayName(),
272  autopsyCase.getCreatedDate(),
273  autopsyCase.getNumber(),
274  autopsyCase.getExaminer(),
275  null,
276  null,
277  null);
278  newCase(curCeCase);
279  return curCeCase;
280  }
281 
287  @Override
288  public void updateCase(CorrelationCase eamCase) throws EamDbException {
289  Connection conn = connect();
290 
291  PreparedStatement preparedStatement = null;
292  String sql = "UPDATE cases "
293  + "SET org_id=?, case_name=?, creation_date=?, case_number=?, examiner_name=?, examiner_email=?, examiner_phone=?, notes=? "
294  + "WHERE case_uid=?";
295 
296  try {
297  preparedStatement = conn.prepareStatement(sql);
298 
299  if (null == eamCase.getOrg()) {
300  preparedStatement.setNull(1, Types.INTEGER);
301  } else {
302  preparedStatement.setInt(1, eamCase.getOrg().getOrgID());
303  }
304  preparedStatement.setString(2, eamCase.getDisplayName());
305  preparedStatement.setString(3, eamCase.getCreationDate());
306 
307  if ("".equals(eamCase.getCaseNumber())) {
308  preparedStatement.setNull(4, Types.INTEGER);
309  } else {
310  preparedStatement.setString(4, eamCase.getCaseNumber());
311  }
312  if ("".equals(eamCase.getExaminerName())) {
313  preparedStatement.setNull(5, Types.INTEGER);
314  } else {
315  preparedStatement.setString(5, eamCase.getExaminerName());
316  }
317  if ("".equals(eamCase.getExaminerEmail())) {
318  preparedStatement.setNull(6, Types.INTEGER);
319  } else {
320  preparedStatement.setString(6, eamCase.getExaminerEmail());
321  }
322  if ("".equals(eamCase.getExaminerPhone())) {
323  preparedStatement.setNull(7, Types.INTEGER);
324  } else {
325  preparedStatement.setString(7, eamCase.getExaminerPhone());
326  }
327  if ("".equals(eamCase.getNotes())) {
328  preparedStatement.setNull(8, Types.INTEGER);
329  } else {
330  preparedStatement.setString(8, eamCase.getNotes());
331  }
332 
333  preparedStatement.setString(9, eamCase.getCaseUUID());
334 
335  preparedStatement.executeUpdate();
336  } catch (SQLException ex) {
337  throw new EamDbException("Error updating case.", ex); // NON-NLS
338  } finally {
339  EamDbUtil.closePreparedStatement(preparedStatement);
341  }
342  }
343 
351  @Override
352  public CorrelationCase getCaseByUUID(String caseUUID) throws EamDbException {
353  // @@@ We should have a cache here...
354 
355  Connection conn = connect();
356 
357  CorrelationCase eamCaseResult = null;
358  PreparedStatement preparedStatement = null;
359  ResultSet resultSet = null;
360 
361  String sql = "SELECT cases.id as case_id, case_uid, case_name, creation_date, case_number, examiner_name, "
362  + "examiner_email, examiner_phone, notes, organizations.id as org_id, org_name, poc_name, poc_email, poc_phone "
363  + "FROM cases "
364  + "LEFT JOIN organizations ON cases.org_id=organizations.id "
365  + "WHERE case_uid=?";
366 
367  try {
368  preparedStatement = conn.prepareStatement(sql);
369  preparedStatement.setString(1, caseUUID);
370  resultSet = preparedStatement.executeQuery();
371  if (resultSet.next()) {
372  eamCaseResult = getEamCaseFromResultSet(resultSet);
373  }
374  } catch (SQLException ex) {
375  throw new EamDbException("Error getting case details.", ex); // NON-NLS
376  } finally {
377  EamDbUtil.closePreparedStatement(preparedStatement);
378  EamDbUtil.closeResultSet(resultSet);
380  }
381 
382  return eamCaseResult;
383  }
384 
390  @Override
391  public List<CorrelationCase> getCases() throws EamDbException {
392  Connection conn = connect();
393 
394  List<CorrelationCase> cases = new ArrayList<>();
395  CorrelationCase eamCaseResult;
396  PreparedStatement preparedStatement = null;
397  ResultSet resultSet = null;
398 
399  String sql = "SELECT cases.id as case_id, case_uid, case_name, creation_date, case_number, examiner_name, "
400  + "examiner_email, examiner_phone, notes, organizations.id as org_id, org_name, poc_name, poc_email, poc_phone "
401  + "FROM cases "
402  + "LEFT JOIN organizations ON cases.org_id=organizations.id";
403 
404  try {
405  preparedStatement = conn.prepareStatement(sql);
406  resultSet = preparedStatement.executeQuery();
407  while (resultSet.next()) {
408  eamCaseResult = getEamCaseFromResultSet(resultSet);
409  cases.add(eamCaseResult);
410  }
411  } catch (SQLException ex) {
412  throw new EamDbException("Error getting all cases.", ex); // NON-NLS
413  } finally {
414  EamDbUtil.closePreparedStatement(preparedStatement);
415  EamDbUtil.closeResultSet(resultSet);
417  }
418 
419  return cases;
420  }
421 
427  @Override
428  public void newDataSource(CorrelationDataSource eamDataSource) throws EamDbException {
429  Connection conn = connect();
430 
431  PreparedStatement preparedStatement = null;
432 
433  String sql = "INSERT INTO data_sources(device_id, name) VALUES (?, ?)";
434 
435  try {
436  preparedStatement = conn.prepareStatement(sql);
437 
438  preparedStatement.setString(1, eamDataSource.getDeviceID());
439  preparedStatement.setString(2, eamDataSource.getName());
440 
441  preparedStatement.executeUpdate();
442  } catch (SQLException ex) {
443  throw new EamDbException("Error inserting new data source.", ex); // NON-NLS
444  } finally {
445  EamDbUtil.closePreparedStatement(preparedStatement);
447  }
448  }
449 
455 // @Override
456 // public void updateDataSource(CorrelationDataSource eamDataSource) throws EamDbException {
457 // Connection conn = connect();
458 // BC: This needs to be updated because device_id is not unique. Query needs to also use case_id
459 // PreparedStatement preparedStatement = null;
460 // String sql = "UPDATE data_sources SET name=? WHERE device_id=?";
461 //
462 // try {
463 // preparedStatement = conn.prepareStatement(sql);
464 //
465 // preparedStatement.setString(1, eamDataSource.getName());
466 // preparedStatement.setString(2, eamDataSource.getDeviceID());
467 //
468 // preparedStatement.executeUpdate();
469 // } catch (SQLException ex) {
470 // throw new EamDbException("Error updating case.", ex); // NON-NLS
471 // } finally {
472 // EamDbUtil.closePreparedStatement(preparedStatement);
473 // EamDbUtil.closeConnection(conn);
474 // }
475 // }
476 
484  @Override
485  public CorrelationDataSource getDataSourceDetails(String dataSourceDeviceId) throws EamDbException {
486  Connection conn = connect();
487 
488  CorrelationDataSource eamDataSourceResult = null;
489  PreparedStatement preparedStatement = null;
490  ResultSet resultSet = null;
491 
492  String sql = "SELECT * FROM data_sources WHERE device_id=?"; // NON-NLS
493 
494  try {
495  preparedStatement = conn.prepareStatement(sql);
496  preparedStatement.setString(1, dataSourceDeviceId);
497  resultSet = preparedStatement.executeQuery();
498  if (resultSet.next()) {
499  eamDataSourceResult = getEamDataSourceFromResultSet(resultSet);
500  }
501  } catch (SQLException ex) {
502  throw new EamDbException("Error getting case details.", ex); // NON-NLS
503  } finally {
504  EamDbUtil.closePreparedStatement(preparedStatement);
505  EamDbUtil.closeResultSet(resultSet);
507  }
508 
509  return eamDataSourceResult;
510  }
511 
517  @Override
518  public List<CorrelationDataSource> getDataSources() throws EamDbException {
519  Connection conn = connect();
520 
521  List<CorrelationDataSource> dataSources = new ArrayList<>();
522  CorrelationDataSource eamDataSourceResult;
523  PreparedStatement preparedStatement = null;
524  ResultSet resultSet = null;
525 
526  String sql = "SELECT * FROM data_sources";
527 
528  try {
529  preparedStatement = conn.prepareStatement(sql);
530  resultSet = preparedStatement.executeQuery();
531  while (resultSet.next()) {
532  eamDataSourceResult = getEamDataSourceFromResultSet(resultSet);
533  dataSources.add(eamDataSourceResult);
534  }
535  } catch (SQLException ex) {
536  throw new EamDbException("Error getting all data sources.", ex); // NON-NLS
537  } finally {
538  EamDbUtil.closePreparedStatement(preparedStatement);
539  EamDbUtil.closeResultSet(resultSet);
541  }
542 
543  return dataSources;
544  }
545 
552  @Override
553  public void addArtifact(CorrelationAttribute eamArtifact) throws EamDbException {
554  Connection conn = connect();
555 
556  List<CorrelationAttributeInstance> eamInstances = eamArtifact.getInstances();
557  PreparedStatement preparedStatement = null;
558 
559 
560  // @@@ We should cache the case and data source IDs in memory
561  String tableName = EamDbUtil.correlationTypeToInstanceTableName(eamArtifact.getCorrelationType());
562  StringBuilder sql = new StringBuilder();
563  sql.append("INSERT INTO ");
564  sql.append(tableName);
565  sql.append("(case_id, data_source_id, value, file_path, known_status, comment) ");
566  sql.append("VALUES ((SELECT id FROM cases WHERE case_uid=? LIMIT 1), ");
567  sql.append("(SELECT id FROM data_sources WHERE device_id=? LIMIT 1), ?, ?, ?, ?)");
568 
569  try {
570  preparedStatement = conn.prepareStatement(sql.toString());
571  for (CorrelationAttributeInstance eamInstance : eamInstances) {
572  if(! eamArtifact.getCorrelationValue().isEmpty()){
573  preparedStatement.setString(1, eamInstance.getCorrelationCase().getCaseUUID());
574  preparedStatement.setString(2, eamInstance.getCorrelationDataSource().getDeviceID());
575  preparedStatement.setString(3, eamArtifact.getCorrelationValue());
576  preparedStatement.setString(4, eamInstance.getFilePath());
577  preparedStatement.setByte(5, eamInstance.getKnownStatus().getFileKnownValue());
578  if ("".equals(eamInstance.getComment())) {
579  preparedStatement.setNull(6, Types.INTEGER);
580  } else {
581  preparedStatement.setString(6, eamInstance.getComment());
582  }
583 
584  preparedStatement.executeUpdate();
585  }
586  }
587  } catch (SQLException ex) {
588  throw new EamDbException("Error inserting new artifact into artifacts table.", ex); // NON-NLS
589  } finally {
590  EamDbUtil.closePreparedStatement(preparedStatement);
592  }
593  }
594 
603  @Override
604  public List<CorrelationAttributeInstance> getArtifactInstancesByTypeValue(CorrelationAttribute.Type aType, String value) throws EamDbException {
605  Connection conn = connect();
606 
607  List<CorrelationAttributeInstance> artifactInstances = new ArrayList<>();
608 
609  CorrelationAttributeInstance artifactInstance;
610  PreparedStatement preparedStatement = null;
611  ResultSet resultSet = null;
612 
613  String tableName = EamDbUtil.correlationTypeToInstanceTableName(aType);
614  StringBuilder sql = new StringBuilder();
615  sql.append("SELECT cases.case_name, cases.case_uid, data_sources.name, device_id, file_path, known_status, comment FROM ");
616  sql.append(tableName);
617  sql.append(" LEFT JOIN cases ON ");
618  sql.append(tableName);
619  sql.append(".case_id=cases.id");
620  sql.append(" LEFT JOIN data_sources ON ");
621  sql.append(tableName);
622  sql.append(".data_source_id=data_sources.id");
623  sql.append(" WHERE value=?");
624 
625  try {
626  preparedStatement = conn.prepareStatement(sql.toString());
627  preparedStatement.setString(1, value);
628  resultSet = preparedStatement.executeQuery();
629  while (resultSet.next()) {
630  artifactInstance = getEamArtifactInstanceFromResultSet(resultSet);
631  artifactInstances.add(artifactInstance);
632  }
633  } catch (SQLException ex) {
634  throw new EamDbException("Error getting artifact instances by artifactType and artifactValue.", ex); // NON-NLS
635  } finally {
636  EamDbUtil.closePreparedStatement(preparedStatement);
637  EamDbUtil.closeResultSet(resultSet);
639  }
640 
641  return artifactInstances;
642  }
643 
655  @Override
656  public List<CorrelationAttributeInstance> getArtifactInstancesByPath(CorrelationAttribute.Type aType, String filePath) throws EamDbException {
657  Connection conn = connect();
658 
659  List<CorrelationAttributeInstance> artifactInstances = new ArrayList<>();
660 
661  CorrelationAttributeInstance artifactInstance;
662  PreparedStatement preparedStatement = null;
663  ResultSet resultSet = null;
664 
665  String tableName = EamDbUtil.correlationTypeToInstanceTableName(aType);
666  StringBuilder sql = new StringBuilder();
667  sql.append("SELECT cases.case_name, cases.case_uid, data_sources.name, device_id, file_path, known_status, comment FROM ");
668  sql.append(tableName);
669  sql.append(" LEFT JOIN cases ON ");
670  sql.append(tableName);
671  sql.append(".case_id=cases.id");
672  sql.append(" LEFT JOIN data_sources ON ");
673  sql.append(tableName);
674  sql.append(".data_source_id=data_sources.id");
675  sql.append(" WHERE file_path=?");
676 
677  try {
678  preparedStatement = conn.prepareStatement(sql.toString());
679  preparedStatement.setString(1, filePath);
680  resultSet = preparedStatement.executeQuery();
681  while (resultSet.next()) {
682  artifactInstance = getEamArtifactInstanceFromResultSet(resultSet);
683  artifactInstances.add(artifactInstance);
684  }
685  } catch (SQLException ex) {
686  throw new EamDbException("Error getting artifact instances by artifactType and artifactValue.", ex); // NON-NLS
687  } finally {
688  EamDbUtil.closePreparedStatement(preparedStatement);
689  EamDbUtil.closeResultSet(resultSet);
691  }
692 
693  return artifactInstances;
694  }
695 
706  @Override
708  Connection conn = connect();
709 
710  Long instanceCount = 0L;
711  PreparedStatement preparedStatement = null;
712  ResultSet resultSet = null;
713 
714  String tableName = EamDbUtil.correlationTypeToInstanceTableName(aType);
715  StringBuilder sql = new StringBuilder();
716  sql.append("SELECT count(*) FROM ");
717  sql.append(tableName);
718  sql.append(" WHERE value=?");
719 
720  try {
721  preparedStatement = conn.prepareStatement(sql.toString());
722  preparedStatement.setString(1, value);
723  resultSet = preparedStatement.executeQuery();
724  resultSet.next();
725  instanceCount = resultSet.getLong(1);
726  } catch (SQLException ex) {
727  throw new EamDbException("Error getting count of artifact instances by artifactType and artifactValue.", ex); // NON-NLS
728  } finally {
729  EamDbUtil.closePreparedStatement(preparedStatement);
730  EamDbUtil.closeResultSet(resultSet);
732  }
733 
734  return instanceCount;
735  }
736 
737 
738  @Override
740  Double uniqueTypeValueTuples = getCountUniqueCaseDataSourceTuplesHavingTypeValue(corAttr.getCorrelationType(), corAttr.getCorrelationValue()).doubleValue();
741  Double uniqueCaseDataSourceTuples = getCountUniqueDataSources().doubleValue();
742  Double commonalityPercentage = uniqueTypeValueTuples / uniqueCaseDataSourceTuples * 100;
743  return commonalityPercentage.intValue();
744  }
745 
756  @Override
758  Connection conn = connect();
759 
760  Long instanceCount = 0L;
761  PreparedStatement preparedStatement = null;
762  ResultSet resultSet = null;
763 
764  String tableName = EamDbUtil.correlationTypeToInstanceTableName(aType);
765  StringBuilder sql = new StringBuilder();
766  sql.append("SELECT count(*) FROM (SELECT DISTINCT case_id, data_source_id FROM ");
767  sql.append(tableName);
768  sql.append(" WHERE value=?) AS ");
769  sql.append(tableName);
770  sql.append("_distinct_case_data_source_tuple");
771 
772  try {
773  preparedStatement = conn.prepareStatement(sql.toString());
774  preparedStatement.setString(1, value);
775  resultSet = preparedStatement.executeQuery();
776  resultSet.next();
777  instanceCount = resultSet.getLong(1);
778  } catch (SQLException ex) {
779  throw new EamDbException("Error counting unique caseDisplayName/dataSource tuples having artifactType and artifactValue.", ex); // NON-NLS
780  } finally {
781  EamDbUtil.closePreparedStatement(preparedStatement);
782  EamDbUtil.closeResultSet(resultSet);
784  }
785 
786  return instanceCount;
787  }
788 
789 
790  @Override
792  Connection conn = connect();
793 
794  Long instanceCount = 0L;
795  PreparedStatement preparedStatement = null;
796  ResultSet resultSet = null;
797 
798  String stmt = "SELECT count(*) FROM data_sources";
799 
800  try {
801  preparedStatement = conn.prepareStatement(stmt);
802  resultSet = preparedStatement.executeQuery();
803  resultSet.next();
804  instanceCount = resultSet.getLong(1);
805  } catch (SQLException ex) {
806  throw new EamDbException("Error counting data sources.", ex); // NON-NLS
807  } finally {
808  EamDbUtil.closePreparedStatement(preparedStatement);
809  EamDbUtil.closeResultSet(resultSet);
811  }
812 
813  return instanceCount;
814  }
815 
827  @Override
828  public Long getCountArtifactInstancesByCaseDataSource(String caseUUID, String dataSourceID) throws EamDbException {
829  Connection conn = connect();
830 
831  Long instanceCount = 0L;
832  List<CorrelationAttribute.Type> artifactTypes = getDefinedCorrelationTypes();
833  PreparedStatement preparedStatement = null;
834  ResultSet resultSet = null;
835 
836  // Figure out sql variables or subqueries
837  StringBuilder sql = new StringBuilder();
838  sql.append("SELECT 0 ");
839 
840  for (CorrelationAttribute.Type type : artifactTypes) {
841  String table_name = EamDbUtil.correlationTypeToInstanceTableName(type);
842 
843  sql.append("+ (SELECT count(*) FROM ");
844  sql.append(table_name);
845  sql.append(" WHERE case_id=(SELECT id FROM cases WHERE case_uid=?) and data_source_id=(SELECT id FROM data_sources WHERE device_id=?))");
846  }
847 
848  try {
849  preparedStatement = conn.prepareStatement(sql.toString());
850 
851  for (int i = 0; i < artifactTypes.size(); ++i) {
852  preparedStatement.setString(2 * i + 1, caseUUID);
853  preparedStatement.setString(2 * i + 2, dataSourceID);
854  }
855 
856  resultSet = preparedStatement.executeQuery();
857  resultSet.next();
858  instanceCount = resultSet.getLong(1);
859  } catch (SQLException ex) {
860  throw new EamDbException("Error counting artifact instances by caseName/dataSource.", ex); // NON-NLS
861  } finally {
862  EamDbUtil.closePreparedStatement(preparedStatement);
863  EamDbUtil.closeResultSet(resultSet);
865  }
866 
867  return instanceCount;
868  }
869 
877  @Override
878  public void prepareBulkArtifact(CorrelationAttribute eamArtifact) throws EamDbException {
879 
880  synchronized (bulkArtifacts) {
881  bulkArtifacts.get(eamArtifact.getCorrelationType().getDbTableName()).add(eamArtifact);
882  bulkArtifactsCount++;
883 
884  if (bulkArtifactsCount >= bulkArtifactsThreshold) {
886  }
887  }
888  }
889 
895  protected abstract String getConflictClause();
896 
901  @Override
902  public void bulkInsertArtifacts() throws EamDbException {
903  List<CorrelationAttribute.Type> artifactTypes = getDefinedCorrelationTypes();
904 
905  Connection conn = connect();
906  PreparedStatement bulkPs = null;
907 
908  try {
909  synchronized (bulkArtifacts) {
910  if (bulkArtifactsCount == 0) {
911  return;
912  }
913 
914  for (CorrelationAttribute.Type type : artifactTypes) {
915 
916  String tableName = EamDbUtil.correlationTypeToInstanceTableName(type);
917  StringBuilder sql = new StringBuilder();
918  sql.append("INSERT INTO ");
919  sql.append(tableName);
920  sql.append(" (case_id, data_source_id, value, file_path, known_status, comment) ");
921  sql.append("VALUES ((SELECT id FROM cases WHERE case_uid=? LIMIT 1), ");
922  sql.append("(SELECT id FROM data_sources WHERE device_id=? LIMIT 1), ?, ?, ?, ?) ");
923  sql.append(getConflictClause());
924 
925  bulkPs = conn.prepareStatement(sql.toString());
926 
927  Collection<CorrelationAttribute> eamArtifacts = bulkArtifacts.get(type.getDbTableName());
928  for (CorrelationAttribute eamArtifact : eamArtifacts) {
929  List<CorrelationAttributeInstance> eamInstances = eamArtifact.getInstances();
930 
931  for (CorrelationAttributeInstance eamInstance : eamInstances) {
932  if(! eamArtifact.getCorrelationValue().isEmpty()){
933  bulkPs.setString(1, eamInstance.getCorrelationCase().getCaseUUID());
934  bulkPs.setString(2, eamInstance.getCorrelationDataSource().getDeviceID());
935  bulkPs.setString(3, eamArtifact.getCorrelationValue());
936  bulkPs.setString(4, eamInstance.getFilePath());
937  bulkPs.setByte(5, eamInstance.getKnownStatus().getFileKnownValue());
938  if ("".equals(eamInstance.getComment())) {
939  bulkPs.setNull(6, Types.INTEGER);
940  } else {
941  bulkPs.setString(6, eamInstance.getComment());
942  }
943  bulkPs.addBatch();
944  }
945  }
946  }
947 
948  bulkPs.executeBatch();
949  bulkArtifacts.get(type.getDbTableName()).clear();
950  }
951 
952  // Reset state
953  bulkArtifactsCount = 0;
954  }
955  } catch (SQLException ex) {
956  throw new EamDbException("Error inserting bulk artifacts.", ex); // NON-NLS
957  } finally {
960  }
961  }
962 
966  @Override
967  public void bulkInsertCases(List<CorrelationCase> cases) throws EamDbException {
968  Connection conn = connect();
969 
970  if (cases.isEmpty()) {
971  return;
972  }
973 
974  int counter = 0;
975  PreparedStatement bulkPs = null;
976  try {
977  String sql = "INSERT INTO cases(case_uid, org_id, case_name, creation_date, case_number, "
978  + "examiner_name, examiner_email, examiner_phone, notes) "
979  + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) "
980  + getConflictClause();
981  bulkPs = conn.prepareStatement(sql);
982 
983  for (CorrelationCase eamCase : cases) {
984  bulkPs.setString(1, eamCase.getCaseUUID());
985  if (null == eamCase.getOrg()) {
986  bulkPs.setNull(2, Types.INTEGER);
987  } else {
988  bulkPs.setInt(2, eamCase.getOrg().getOrgID());
989  }
990  bulkPs.setString(3, eamCase.getDisplayName());
991  bulkPs.setString(4, eamCase.getCreationDate());
992 
993  if ("".equals(eamCase.getCaseNumber())) {
994  bulkPs.setNull(5, Types.INTEGER);
995  } else {
996  bulkPs.setString(5, eamCase.getCaseNumber());
997  }
998  if ("".equals(eamCase.getExaminerName())) {
999  bulkPs.setNull(6, Types.INTEGER);
1000  } else {
1001  bulkPs.setString(6, eamCase.getExaminerName());
1002  }
1003  if ("".equals(eamCase.getExaminerEmail())) {
1004  bulkPs.setNull(7, Types.INTEGER);
1005  } else {
1006  bulkPs.setString(7, eamCase.getExaminerEmail());
1007  }
1008  if ("".equals(eamCase.getExaminerPhone())) {
1009  bulkPs.setNull(8, Types.INTEGER);
1010  } else {
1011  bulkPs.setString(8, eamCase.getExaminerPhone());
1012  }
1013  if ("".equals(eamCase.getNotes())) {
1014  bulkPs.setNull(9, Types.INTEGER);
1015  } else {
1016  bulkPs.setString(9, eamCase.getNotes());
1017  }
1018 
1019  bulkPs.addBatch();
1020 
1021  counter++;
1022 
1023  // limit a batch's max size to bulkArtifactsThreshold
1024  if (counter >= bulkArtifactsThreshold) {
1025  bulkPs.executeBatch();
1026  counter = 0;
1027  }
1028  }
1029  // send the remaining batch records
1030  bulkPs.executeBatch();
1031  } catch (SQLException ex) {
1032  throw new EamDbException("Error inserting bulk cases.", ex); // NON-NLS
1033  } finally {
1035  EamDbUtil.closeConnection(conn);
1036  }
1037  }
1038 
1047  @Override
1048  public void setArtifactInstanceKnownStatus(CorrelationAttribute eamArtifact, TskData.FileKnown knownStatus) throws EamDbException {
1049  Connection conn = connect();
1050 
1051  if (1 != eamArtifact.getInstances().size()) {
1052  throw new EamDbException("Error: Artifact must have exactly one (1) Artifact Instance to set as notable."); // NON-NLS
1053  }
1054 
1055  List<CorrelationAttributeInstance> eamInstances = eamArtifact.getInstances();
1056  CorrelationAttributeInstance eamInstance = eamInstances.get(0);
1057 
1058  PreparedStatement preparedUpdate = null;
1059  PreparedStatement preparedQuery = null;
1060  ResultSet resultSet = null;
1061 
1062  String tableName = EamDbUtil.correlationTypeToInstanceTableName(eamArtifact.getCorrelationType());
1063 
1064  StringBuilder sqlQuery = new StringBuilder();
1065  sqlQuery.append("SELECT id FROM ");
1066  sqlQuery.append(tableName);
1067  sqlQuery.append(" WHERE case_id=(SELECT id FROM cases WHERE case_uid=?) ");
1068  sqlQuery.append("AND data_source_id=(SELECT id FROM data_sources WHERE device_id=?) ");
1069  sqlQuery.append("AND value=? ");
1070  sqlQuery.append("AND file_path=?");
1071 
1072  StringBuilder sqlUpdate = new StringBuilder();
1073  sqlUpdate.append("UPDATE ");
1074  sqlUpdate.append(tableName);
1075  sqlUpdate.append(" SET known_status=?, comment=? ");
1076  sqlUpdate.append("WHERE id=?");
1077 
1078  try {
1079  preparedQuery = conn.prepareStatement(sqlQuery.toString());
1080  preparedQuery.setString(1, eamInstance.getCorrelationCase().getCaseUUID());
1081  preparedQuery.setString(2, eamInstance.getCorrelationDataSource().getDeviceID());
1082  preparedQuery.setString(3, eamArtifact.getCorrelationValue());
1083  preparedQuery.setString(4, eamInstance.getFilePath());
1084  resultSet = preparedQuery.executeQuery();
1085  if (resultSet.next()) {
1086  int instance_id = resultSet.getInt("id");
1087  preparedUpdate = conn.prepareStatement(sqlUpdate.toString());
1088 
1089  preparedUpdate.setByte(1, knownStatus.getFileKnownValue());
1090  // NOTE: if the user tags the same instance as BAD multiple times,
1091  // the comment from the most recent tagging is the one that will
1092  // prevail in the DB.
1093  if ("".equals(eamInstance.getComment())) {
1094  preparedUpdate.setNull(2, Types.INTEGER);
1095  } else {
1096  preparedUpdate.setString(2, eamInstance.getComment());
1097  }
1098  preparedUpdate.setInt(3, instance_id);
1099 
1100  preparedUpdate.executeUpdate();
1101  } else {
1102  // In this case, the user is tagging something that isn't in the database,
1103  // which means the case and/or datasource may also not be in the database.
1104  // We could improve effiency by keeping a list of all datasources and cases
1105  // in the database, but we don't expect the user to be tagging large numbers
1106  // of items (that didn't have the CE ingest module run on them) at once.
1107 
1108  if(null == getCaseByUUID(eamInstance.getCorrelationCase().getCaseUUID())){
1109  newCase(eamInstance.getCorrelationCase());
1110  }
1111 
1112  if (null == getDataSourceDetails(eamInstance.getCorrelationDataSource().getDeviceID())) {
1113  newDataSource(eamInstance.getCorrelationDataSource());
1114  }
1115 
1116  eamArtifact.getInstances().get(0).setKnownStatus(knownStatus);
1117  addArtifact(eamArtifact);
1118  }
1119 
1120  } catch (SQLException ex) {
1121  throw new EamDbException("Error getting/setting artifact instance knownStatus=" + knownStatus.getName(), ex); // NON-NLS
1122  } finally {
1123  EamDbUtil.closePreparedStatement(preparedUpdate);
1124  EamDbUtil.closePreparedStatement(preparedQuery);
1125  EamDbUtil.closeResultSet(resultSet);
1126  EamDbUtil.closeConnection(conn);
1127  }
1128  }
1129 
1139  @Override
1140  public List<CorrelationAttributeInstance> getArtifactInstancesKnownBad(CorrelationAttribute.Type aType, String value) throws EamDbException {
1141  Connection conn = connect();
1142 
1143  List<CorrelationAttributeInstance> artifactInstances = new ArrayList<>();
1144 
1145  CorrelationAttributeInstance artifactInstance;
1146  PreparedStatement preparedStatement = null;
1147  ResultSet resultSet = null;
1148 
1149  String tableName = EamDbUtil.correlationTypeToInstanceTableName(aType);
1150  StringBuilder sql = new StringBuilder();
1151  sql.append("SELECT cases.case_name, cases.case_uid, data_sources.name, device_id, file_path, known_status, comment FROM ");
1152  sql.append(tableName);
1153  sql.append(" LEFT JOIN cases ON ");
1154  sql.append(tableName);
1155  sql.append(".case_id=cases.id");
1156  sql.append(" LEFT JOIN data_sources ON ");
1157  sql.append(tableName);
1158  sql.append(".data_source_id=data_sources.id");
1159  sql.append(" WHERE value=? AND known_status=?");
1160 
1161  try {
1162  preparedStatement = conn.prepareStatement(sql.toString());
1163  preparedStatement.setString(1, value);
1164  preparedStatement.setByte(2, TskData.FileKnown.BAD.getFileKnownValue());
1165  resultSet = preparedStatement.executeQuery();
1166  while (resultSet.next()) {
1167  artifactInstance = getEamArtifactInstanceFromResultSet(resultSet);
1168  artifactInstances.add(artifactInstance);
1169  }
1170  } catch (SQLException ex) {
1171  throw new EamDbException("Error getting notable artifact instances.", ex); // NON-NLS
1172  } finally {
1173  EamDbUtil.closePreparedStatement(preparedStatement);
1174  EamDbUtil.closeResultSet(resultSet);
1175  EamDbUtil.closeConnection(conn);
1176  }
1177 
1178  return artifactInstances;
1179  }
1180 
1189  @Override
1191  Connection conn = connect();
1192 
1193  Long badInstances = 0L;
1194  PreparedStatement preparedStatement = null;
1195  ResultSet resultSet = null;
1196 
1197  String tableName = EamDbUtil.correlationTypeToInstanceTableName(aType);
1198  StringBuilder sql = new StringBuilder();
1199  sql.append("SELECT count(*) FROM ");
1200  sql.append(tableName);
1201  sql.append(" WHERE value=? AND known_status=?");
1202 
1203  try {
1204  preparedStatement = conn.prepareStatement(sql.toString());
1205  preparedStatement.setString(1, value);
1206  preparedStatement.setByte(2, TskData.FileKnown.BAD.getFileKnownValue());
1207  resultSet = preparedStatement.executeQuery();
1208  resultSet.next();
1209  badInstances = resultSet.getLong(1);
1210  } catch (SQLException ex) {
1211  throw new EamDbException("Error getting count of notable artifact instances.", ex); // NON-NLS
1212  } finally {
1213  EamDbUtil.closePreparedStatement(preparedStatement);
1214  EamDbUtil.closeResultSet(resultSet);
1215  EamDbUtil.closeConnection(conn);
1216  }
1217 
1218  return badInstances;
1219  }
1220 
1233  @Override
1235  Connection conn = connect();
1236 
1237  Collection<String> caseNames = new LinkedHashSet<>();
1238 
1239  PreparedStatement preparedStatement = null;
1240  ResultSet resultSet = null;
1241 
1242  String tableName = EamDbUtil.correlationTypeToInstanceTableName(aType);
1243  StringBuilder sql = new StringBuilder();
1244  sql.append("SELECT DISTINCT case_name FROM ");
1245  sql.append(tableName);
1246  sql.append(" INNER JOIN cases ON ");
1247  sql.append(tableName);
1248  sql.append(".case_id=cases.id WHERE ");
1249  sql.append(tableName);
1250  sql.append(".value=? AND ");
1251  sql.append(tableName);
1252  sql.append(".known_status=?");
1253 
1254  try {
1255  preparedStatement = conn.prepareStatement(sql.toString());
1256  preparedStatement.setString(1, value);
1257  preparedStatement.setByte(2, TskData.FileKnown.BAD.getFileKnownValue());
1258  resultSet = preparedStatement.executeQuery();
1259  while (resultSet.next()) {
1260  caseNames.add(resultSet.getString("case_name"));
1261  }
1262  } catch (SQLException ex) {
1263  throw new EamDbException("Error getting notable artifact instances.", ex); // NON-NLS
1264  } finally {
1265  EamDbUtil.closePreparedStatement(preparedStatement);
1266  EamDbUtil.closeResultSet(resultSet);
1267  EamDbUtil.closeConnection(conn);
1268  }
1269 
1270  return caseNames.stream().collect(Collectors.toList());
1271  }
1272 
1281  @Override
1282  public boolean isArtifactlKnownBadByReference(CorrelationAttribute.Type aType, String value) throws EamDbException {
1283 
1284  // TEMP: Only support file correlation type
1285  if (aType.getId() != CorrelationAttribute.FILES_TYPE_ID) {
1286  return false;
1287  }
1288 
1289  Connection conn = connect();
1290 
1291  Long badInstances = 0L;
1292  PreparedStatement preparedStatement = null;
1293  ResultSet resultSet = null;
1294  String sql = "SELECT count(*) FROM %s WHERE value=? AND known_status=?";
1295 
1296  try {
1297  preparedStatement = conn.prepareStatement(String.format(sql, EamDbUtil.correlationTypeToReferenceTableName(aType)));
1298  preparedStatement.setString(1, value);
1299  preparedStatement.setByte(2, TskData.FileKnown.BAD.getFileKnownValue());
1300  resultSet = preparedStatement.executeQuery();
1301  resultSet.next();
1302  badInstances = resultSet.getLong(1);
1303  } catch (SQLException ex) {
1304  throw new EamDbException("Error determining if artifact is notable by reference.", ex); // NON-NLS
1305  } finally {
1306  EamDbUtil.closePreparedStatement(preparedStatement);
1307  EamDbUtil.closeResultSet(resultSet);
1308  EamDbUtil.closeConnection(conn);
1309  }
1310 
1311  return 0 < badInstances;
1312  }
1313 
1321  @Override
1322  public void newOrganization(EamOrganization eamOrg) throws EamDbException {
1323  Connection conn = connect();
1324 
1325  PreparedStatement preparedStatement = null;
1326  String sql = "INSERT INTO organizations(org_name, poc_name, poc_email, poc_phone) VALUES (?, ?, ?, ?)";
1327 
1328  try {
1329  preparedStatement = conn.prepareStatement(sql);
1330  preparedStatement.setString(1, eamOrg.getName());
1331  preparedStatement.setString(2, eamOrg.getPocName());
1332  preparedStatement.setString(3, eamOrg.getPocEmail());
1333  preparedStatement.setString(4, eamOrg.getPocPhone());
1334 
1335  preparedStatement.executeUpdate();
1336  } catch (SQLException ex) {
1337  throw new EamDbException("Error inserting new organization.", ex); // NON-NLS
1338  } finally {
1339  EamDbUtil.closePreparedStatement(preparedStatement);
1340  EamDbUtil.closeConnection(conn);
1341  }
1342  }
1343 
1351  @Override
1352  public List<EamOrganization> getOrganizations() throws EamDbException {
1353  Connection conn = connect();
1354 
1355  List<EamOrganization> orgs = new ArrayList<>();
1356  PreparedStatement preparedStatement = null;
1357  ResultSet resultSet = null;
1358  String sql = "SELECT * FROM organizations";
1359 
1360  try {
1361  preparedStatement = conn.prepareStatement(sql);
1362  resultSet = preparedStatement.executeQuery();
1363  while (resultSet.next()) {
1364  orgs.add(getEamOrganizationFromResultSet(resultSet));
1365  }
1366  return orgs;
1367 
1368  } catch (SQLException ex) {
1369  throw new EamDbException("Error getting all organizations.", ex); // NON-NLS
1370  } finally {
1371  EamDbUtil.closePreparedStatement(preparedStatement);
1372  EamDbUtil.closeResultSet(resultSet);
1373  EamDbUtil.closeConnection(conn);
1374  }
1375  }
1376 
1386  @Override
1388  Connection conn = connect();
1389 
1390  PreparedStatement preparedStatement = null;
1391  ResultSet resultSet = null;
1392  String sql = "SELECT * FROM organizations WHERE id=?";
1393 
1394  try {
1395  preparedStatement = conn.prepareStatement(sql);
1396  preparedStatement.setInt(1, orgID);
1397  resultSet = preparedStatement.executeQuery();
1398  resultSet.next();
1399  return getEamOrganizationFromResultSet(resultSet);
1400 
1401  } catch (SQLException ex) {
1402  throw new EamDbException("Error getting organization by id.", ex); // NON-NLS
1403  } finally {
1404  EamDbUtil.closePreparedStatement(preparedStatement);
1405  EamDbUtil.closeResultSet(resultSet);
1406  EamDbUtil.closeConnection(conn);
1407  }
1408  }
1409 
1419  @Override
1420  public int newReferencelSet(EamGlobalSet eamGlobalSet) throws EamDbException {
1421  Connection conn = connect();
1422 
1423  PreparedStatement preparedStatement1 = null;
1424  PreparedStatement preparedStatement2 = null;
1425  ResultSet resultSet = null;
1426  String sql1 = "INSERT INTO reference_sets(org_id, set_name, version, import_date) VALUES (?, ?, ?, ?)";
1427  String sql2 = "SELECT id FROM reference_sets WHERE org_id=? AND set_name=? AND version=? AND import_date=? LIMIT 1";
1428 
1429  try {
1430  preparedStatement1 = conn.prepareStatement(sql1);
1431  preparedStatement1.setInt(1, eamGlobalSet.getOrgID());
1432  preparedStatement1.setString(2, eamGlobalSet.getSetName());
1433  preparedStatement1.setString(3, eamGlobalSet.getVersion());
1434  preparedStatement1.setString(4, eamGlobalSet.getImportDate().toString());
1435 
1436  preparedStatement1.executeUpdate();
1437 
1438  preparedStatement2 = conn.prepareStatement(sql2);
1439  preparedStatement2.setInt(1, eamGlobalSet.getOrgID());
1440  preparedStatement2.setString(2, eamGlobalSet.getSetName());
1441  preparedStatement2.setString(3, eamGlobalSet.getVersion());
1442  preparedStatement2.setString(4, eamGlobalSet.getImportDate().toString());
1443 
1444  resultSet = preparedStatement2.executeQuery();
1445  resultSet.next();
1446  return resultSet.getInt("id");
1447 
1448  } catch (SQLException ex) {
1449  throw new EamDbException("Error inserting new global set.", ex); // NON-NLS
1450  } finally {
1451  EamDbUtil.closePreparedStatement(preparedStatement1);
1452  EamDbUtil.closePreparedStatement(preparedStatement2);
1453  EamDbUtil.closeResultSet(resultSet);
1454  EamDbUtil.closeConnection(conn);
1455  }
1456  }
1457 
1467  @Override
1468  public EamGlobalSet getReferenceSetByID(int referenceSetID) throws EamDbException {
1469  Connection conn = connect();
1470 
1471  PreparedStatement preparedStatement1 = null;
1472  ResultSet resultSet = null;
1473  String sql1 = "SELECT * FROM reference_sets WHERE id=?";
1474 
1475  try {
1476  preparedStatement1 = conn.prepareStatement(sql1);
1477  preparedStatement1.setInt(1, referenceSetID);
1478  resultSet = preparedStatement1.executeQuery();
1479  resultSet.next();
1480  return getEamGlobalSetFromResultSet(resultSet);
1481 
1482  } catch (SQLException ex) {
1483  throw new EamDbException("Error getting reference set by id.", ex); // NON-NLS
1484  } finally {
1485  EamDbUtil.closePreparedStatement(preparedStatement1);
1486  EamDbUtil.closeResultSet(resultSet);
1487  EamDbUtil.closeConnection(conn);
1488  }
1489  }
1490 
1500  @Override
1501  public void addReferenceInstance(EamGlobalFileInstance eamGlobalFileInstance, CorrelationAttribute.Type correlationType) throws EamDbException {
1502  Connection conn = connect();
1503 
1504  PreparedStatement preparedStatement = null;
1505 
1506  String sql = "INSERT INTO %s(reference_set_id, value, known_status, comment) VALUES (?, ?, ?, ?)";
1507 
1508  try {
1509  preparedStatement = conn.prepareStatement(String.format(sql, EamDbUtil.correlationTypeToReferenceTableName(correlationType)));
1510  preparedStatement.setInt(1, eamGlobalFileInstance.getGlobalSetID());
1511  preparedStatement.setString(2, eamGlobalFileInstance.getMD5Hash());
1512  preparedStatement.setByte(3, eamGlobalFileInstance.getKnownStatus().getFileKnownValue());
1513  preparedStatement.setString(4, eamGlobalFileInstance.getComment());
1514  preparedStatement.executeUpdate();
1515  } catch (SQLException ex) {
1516  throw new EamDbException("Error inserting new reference instance into reference_ table.", ex); // NON-NLS
1517  } finally {
1518  EamDbUtil.closePreparedStatement(preparedStatement);
1519  EamDbUtil.closeConnection(conn);
1520  }
1521  }
1522 
1528  @Override
1529  public void bulkInsertReferenceTypeEntries(Set<EamGlobalFileInstance> globalInstances, CorrelationAttribute.Type contentType) throws EamDbException {
1530  Connection conn = connect();
1531 
1532  PreparedStatement bulkPs = null;
1533  try {
1534  conn.setAutoCommit(false);
1535 
1536  // FUTURE: have a separate global_files table for each Type.
1537  String sql = "INSERT INTO %s(reference_set_id, value, known_status, comment) VALUES (?, ?, ?, ?) "
1538  + getConflictClause();
1539 
1540  bulkPs = conn.prepareStatement(String.format(sql, EamDbUtil.correlationTypeToReferenceTableName(contentType)));
1541 
1542  for (EamGlobalFileInstance globalInstance : globalInstances) {
1543  bulkPs.setInt(1, globalInstance.getGlobalSetID());
1544  bulkPs.setString(2, globalInstance.getMD5Hash());
1545  bulkPs.setByte(3, globalInstance.getKnownStatus().getFileKnownValue());
1546  bulkPs.setString(4, globalInstance.getComment());
1547  bulkPs.addBatch();
1548  }
1549 
1550  bulkPs.executeBatch();
1551  conn.commit();
1552  } catch (SQLException ex) {
1553  try{
1554  conn.rollback();
1555  } catch (SQLException ex2){
1556  // We're alredy in an error state
1557  }
1558  throw new EamDbException("Error inserting bulk artifacts.", ex); // NON-NLS
1559  } finally {
1561  EamDbUtil.closeConnection(conn);
1562  }
1563  }
1564 
1575  @Override
1576  public List<EamGlobalFileInstance> getReferenceInstancesByTypeValue(CorrelationAttribute.Type aType, String aValue) throws EamDbException {
1577  Connection conn = connect();
1578 
1579  List<EamGlobalFileInstance> globalFileInstances = new ArrayList<>();
1580  PreparedStatement preparedStatement1 = null;
1581  ResultSet resultSet = null;
1582  String sql1 = "SELECT * FROM %s WHERE value=?";
1583 
1584  try {
1585  preparedStatement1 = conn.prepareStatement(String.format(sql1, EamDbUtil.correlationTypeToReferenceTableName(aType)));
1586  preparedStatement1.setString(1, aValue);
1587  resultSet = preparedStatement1.executeQuery();
1588  while (resultSet.next()) {
1589  globalFileInstances.add(getEamGlobalFileInstanceFromResultSet(resultSet));
1590  }
1591  return globalFileInstances;
1592 
1593  } catch (SQLException ex) {
1594  throw new EamDbException("Error getting reference instances by type and value.", ex); // NON-NLS
1595  } finally {
1596  EamDbUtil.closePreparedStatement(preparedStatement1);
1597  EamDbUtil.closeResultSet(resultSet);
1598  EamDbUtil.closeConnection(conn);
1599  }
1600  }
1601 
1611  @Override
1613  Connection conn = connect();
1614 
1615  PreparedStatement preparedStatement = null;
1616  PreparedStatement preparedStatementQuery = null;
1617  ResultSet resultSet = null;
1618  int typeId = 0;
1619  String insertSql;
1620  String querySql;
1621  // if we have a known ID, use it, if not (is -1) let the db assign it.
1622  if (-1 == newType.getId()) {
1623  insertSql = "INSERT INTO correlation_types(display_name, db_table_name, supported, enabled) VALUES (?, ?, ?, ?)";
1624  } else {
1625  insertSql = "INSERT INTO correlation_types(id, display_name, db_table_name, supported, enabled) VALUES (?, ?, ?, ?, ?)";
1626  }
1627  querySql = "SELECT id FROM correlation_types WHERE display_name=? AND db_table_name=?";
1628 
1629  try {
1630  preparedStatement = conn.prepareStatement(insertSql);
1631 
1632  if (-1 == newType.getId()) {
1633  preparedStatement.setString(1, newType.getDisplayName());
1634  preparedStatement.setString(2, newType.getDbTableName());
1635  preparedStatement.setInt(3, newType.isSupported() ? 1 : 0);
1636  preparedStatement.setInt(4, newType.isEnabled() ? 1 : 0);
1637  } else {
1638  preparedStatement.setInt(1, newType.getId());
1639  preparedStatement.setString(2, newType.getDisplayName());
1640  preparedStatement.setString(3, newType.getDbTableName());
1641  preparedStatement.setInt(4, newType.isSupported() ? 1 : 0);
1642  preparedStatement.setInt(5, newType.isEnabled() ? 1 : 0);
1643  }
1644 
1645  preparedStatement.executeUpdate();
1646 
1647  preparedStatementQuery = conn.prepareStatement(querySql);
1648  preparedStatementQuery.setString(1, newType.getDisplayName());
1649  preparedStatementQuery.setString(2, newType.getDbTableName());
1650 
1651  resultSet = preparedStatementQuery.executeQuery();
1652  if (resultSet.next()) {
1653  CorrelationAttribute.Type correlationType = getCorrelationTypeFromResultSet(resultSet);
1654  typeId = correlationType.getId();
1655  }
1656  } catch (SQLException ex) {
1657  throw new EamDbException("Error inserting new correlation type.", ex); // NON-NLS
1658  } finally {
1659  EamDbUtil.closePreparedStatement(preparedStatement);
1660  EamDbUtil.closePreparedStatement(preparedStatementQuery);
1661  EamDbUtil.closeResultSet(resultSet);
1662  EamDbUtil.closeConnection(conn);
1663  }
1664  return typeId;
1665  }
1666 
1667 
1668  @Override
1670  Connection conn = connect();
1671 
1672  List<CorrelationAttribute.Type> aTypes = new ArrayList<>();
1673  PreparedStatement preparedStatement = null;
1674  ResultSet resultSet = null;
1675  String sql = "SELECT * FROM correlation_types";
1676 
1677  try {
1678  preparedStatement = conn.prepareStatement(sql);
1679  resultSet = preparedStatement.executeQuery();
1680  while (resultSet.next()) {
1681  aTypes.add(getCorrelationTypeFromResultSet(resultSet));
1682  }
1683  return aTypes;
1684 
1685  } catch (SQLException ex) {
1686  throw new EamDbException("Error getting all correlation types.", ex); // NON-NLS
1687  } finally {
1688  EamDbUtil.closePreparedStatement(preparedStatement);
1689  EamDbUtil.closeResultSet(resultSet);
1690  EamDbUtil.closeConnection(conn);
1691  }
1692  }
1693 
1703  @Override
1705  Connection conn = connect();
1706 
1707  List<CorrelationAttribute.Type> aTypes = new ArrayList<>();
1708  PreparedStatement preparedStatement = null;
1709  ResultSet resultSet = null;
1710  String sql = "SELECT * FROM correlation_types WHERE enabled=1";
1711 
1712  try {
1713  preparedStatement = conn.prepareStatement(sql);
1714  resultSet = preparedStatement.executeQuery();
1715  while (resultSet.next()) {
1716  aTypes.add(getCorrelationTypeFromResultSet(resultSet));
1717  }
1718  return aTypes;
1719 
1720  } catch (SQLException ex) {
1721  throw new EamDbException("Error getting enabled correlation types.", ex); // NON-NLS
1722  } finally {
1723  EamDbUtil.closePreparedStatement(preparedStatement);
1724  EamDbUtil.closeResultSet(resultSet);
1725  EamDbUtil.closeConnection(conn);
1726  }
1727  }
1728 
1738  @Override
1740  Connection conn = connect();
1741 
1742  List<CorrelationAttribute.Type> aTypes = new ArrayList<>();
1743  PreparedStatement preparedStatement = null;
1744  ResultSet resultSet = null;
1745  String sql = "SELECT * FROM correlation_types WHERE supported=1";
1746 
1747  try {
1748  preparedStatement = conn.prepareStatement(sql);
1749  resultSet = preparedStatement.executeQuery();
1750  while (resultSet.next()) {
1751  aTypes.add(getCorrelationTypeFromResultSet(resultSet));
1752  }
1753  return aTypes;
1754 
1755  } catch (SQLException ex) {
1756  throw new EamDbException("Error getting supported correlation types.", ex); // NON-NLS
1757  } finally {
1758  EamDbUtil.closePreparedStatement(preparedStatement);
1759  EamDbUtil.closeResultSet(resultSet);
1760  EamDbUtil.closeConnection(conn);
1761  }
1762  }
1763 
1771  @Override
1773  Connection conn = connect();
1774 
1775  PreparedStatement preparedStatement = null;
1776  String sql = "UPDATE correlation_types SET display_name=?, db_table_name=?, supported=?, enabled=? WHERE id=?";
1777 
1778  try {
1779  preparedStatement = conn.prepareStatement(sql);
1780  preparedStatement.setString(1, aType.getDisplayName());
1781  preparedStatement.setString(2, aType.getDbTableName());
1782  preparedStatement.setInt(3, aType.isSupported() ? 1 : 0);
1783  preparedStatement.setInt(4, aType.isEnabled() ? 1 : 0);
1784  preparedStatement.setInt(5, aType.getId());
1785  preparedStatement.executeUpdate();
1786 
1787  } catch (SQLException ex) {
1788  throw new EamDbException("Error updating correlation type.", ex); // NON-NLS
1789  } finally {
1790  EamDbUtil.closePreparedStatement(preparedStatement);
1791  EamDbUtil.closeConnection(conn);
1792  }
1793 
1794  }
1795 
1805  @Override
1807  Connection conn = connect();
1808 
1809  CorrelationAttribute.Type aType;
1810  PreparedStatement preparedStatement = null;
1811  ResultSet resultSet = null;
1812  String sql = "SELECT * FROM correlation_types WHERE id=?";
1813 
1814  try {
1815  preparedStatement = conn.prepareStatement(sql);
1816  preparedStatement.setInt(1, typeId);
1817  resultSet = preparedStatement.executeQuery();
1818  resultSet.next();
1819  aType = getCorrelationTypeFromResultSet(resultSet);
1820  return aType;
1821 
1822  } catch (SQLException ex) {
1823  throw new EamDbException("Error getting correlation type by id.", ex); // NON-NLS
1824  } finally {
1825  EamDbUtil.closePreparedStatement(preparedStatement);
1826  EamDbUtil.closeResultSet(resultSet);
1827  EamDbUtil.closeConnection(conn);
1828  }
1829  }
1830 
1841  private CorrelationCase getEamCaseFromResultSet(ResultSet resultSet) throws SQLException {
1842  if (null == resultSet) {
1843  return null;
1844  }
1845 
1846  EamOrganization eamOrg = null;
1847 
1848  resultSet.getInt("org_id");
1849  if (!resultSet.wasNull()) {
1850 
1851  eamOrg = new EamOrganization(resultSet.getInt("org_id"),
1852  resultSet.getString("org_name"),
1853  resultSet.getString("poc_name"),
1854  resultSet.getString("poc_email"),
1855  resultSet.getString("poc_phone"));
1856  }
1857 
1858  CorrelationCase eamCase = new CorrelationCase(resultSet.getInt("case_id"), resultSet.getString("case_uid"), resultSet.getString("case_name"));
1859  eamCase.setOrg(eamOrg);
1860  eamCase.setCreationDate(resultSet.getString("creation_date"));
1861  eamCase.setCaseNumber(resultSet.getString("case_number"));
1862  eamCase.setExaminerName(resultSet.getString("examiner_name"));
1863  eamCase.setExaminerEmail(resultSet.getString("examiner_email"));
1864  eamCase.setExaminerPhone(resultSet.getString("examiner_phone"));
1865  eamCase.setNotes(resultSet.getString("notes"));
1866 
1867  return eamCase;
1868  }
1869 
1870  private CorrelationDataSource getEamDataSourceFromResultSet(ResultSet resultSet) throws SQLException {
1871  if (null == resultSet) {
1872  return null;
1873  }
1874 
1875  CorrelationDataSource eamDataSource = new CorrelationDataSource(
1876  resultSet.getInt("id"),
1877  resultSet.getString("device_id"),
1878  resultSet.getString("name")
1879  );
1880 
1881  return eamDataSource;
1882  }
1883 
1884  private CorrelationAttribute.Type getCorrelationTypeFromResultSet(ResultSet resultSet) throws EamDbException, SQLException {
1885  if (null == resultSet) {
1886  return null;
1887  }
1888 
1889  CorrelationAttribute.Type eamArtifactType = new CorrelationAttribute.Type(
1890  resultSet.getInt("id"),
1891  resultSet.getString("display_name"),
1892  resultSet.getString("db_table_name"),
1893  resultSet.getBoolean("supported"),
1894  resultSet.getBoolean("enabled")
1895  );
1896 
1897  return eamArtifactType;
1898  }
1899 
1910  private CorrelationAttributeInstance getEamArtifactInstanceFromResultSet(ResultSet resultSet) throws SQLException {
1911  if (null == resultSet) {
1912  return null;
1913  }
1915  new CorrelationCase(resultSet.getString("case_uid"), resultSet.getString("case_name")),
1916  new CorrelationDataSource(-1, resultSet.getString("device_id"), resultSet.getString("name")),
1917  resultSet.getString("file_path"),
1918  resultSet.getString("comment"),
1919  TskData.FileKnown.valueOf(resultSet.getByte("known_status")),
1921  );
1922 
1923  return eamArtifactInstance;
1924  }
1925 
1926  private EamOrganization getEamOrganizationFromResultSet(ResultSet resultSet) throws SQLException {
1927  if (null == resultSet) {
1928  return null;
1929  }
1930 
1931  EamOrganization eamOrganization = new EamOrganization(
1932  resultSet.getInt("id"),
1933  resultSet.getString("org_name"),
1934  resultSet.getString("poc_name"),
1935  resultSet.getString("poc_email"),
1936  resultSet.getString("poc_phone")
1937  );
1938 
1939  return eamOrganization;
1940  }
1941 
1942  private EamGlobalSet getEamGlobalSetFromResultSet(ResultSet resultSet) throws SQLException {
1943  if (null == resultSet) {
1944  return null;
1945  }
1946 
1947  EamGlobalSet eamGlobalSet = new EamGlobalSet(
1948  resultSet.getInt("id"),
1949  resultSet.getInt("org_id"),
1950  resultSet.getString("set_name"),
1951  resultSet.getString("version"),
1952  LocalDate.parse(resultSet.getString("import_date"))
1953  );
1954 
1955  return eamGlobalSet;
1956  }
1957 
1958  private EamGlobalFileInstance getEamGlobalFileInstanceFromResultSet(ResultSet resultSet) throws SQLException {
1959  if (null == resultSet) {
1960  return null;
1961  }
1962 
1963  EamGlobalFileInstance eamGlobalFileInstance = new EamGlobalFileInstance(
1964  resultSet.getInt("id"),
1965  resultSet.getInt("reference_set_id"),
1966  resultSet.getString("value"),
1967  TskData.FileKnown.valueOf(resultSet.getByte("known_status")),
1968  resultSet.getString("comment")
1969  );
1970 
1971  return eamGlobalFileInstance;
1972  }
1973 
1974 }
List< CorrelationAttributeInstance > getArtifactInstancesByTypeValue(CorrelationAttribute.Type aType, String value)
CorrelationDataSource getDataSourceDetails(String dataSourceDeviceId)
List< EamGlobalFileInstance > getReferenceInstancesByTypeValue(CorrelationAttribute.Type aType, String aValue)
Long getCountArtifactInstancesByCaseDataSource(String caseUUID, String dataSourceID)
Long getCountArtifactInstancesKnownBad(CorrelationAttribute.Type aType, String value)
List< String > getListCasesHavingArtifactInstancesKnownBad(CorrelationAttribute.Type aType, String value)
boolean isArtifactlKnownBadByReference(CorrelationAttribute.Type aType, String value)
static String correlationTypeToReferenceTableName(CorrelationAttribute.Type type)
Definition: EamDbUtil.java:244
List< CorrelationAttributeInstance > getArtifactInstancesKnownBad(CorrelationAttribute.Type aType, String value)
List< CorrelationAttributeInstance > getArtifactInstancesByPath(CorrelationAttribute.Type aType, String filePath)
void addReferenceInstance(EamGlobalFileInstance eamGlobalFileInstance, CorrelationAttribute.Type correlationType)
void setArtifactInstanceKnownStatus(CorrelationAttribute eamArtifact, TskData.FileKnown knownStatus)
CorrelationAttribute.Type getCorrelationTypeFromResultSet(ResultSet resultSet)
CorrelationAttributeInstance getEamArtifactInstanceFromResultSet(ResultSet resultSet)
void bulkInsertReferenceTypeEntries(Set< EamGlobalFileInstance > globalInstances, CorrelationAttribute.Type contentType)
Long getCountArtifactInstancesByTypeValue(CorrelationAttribute.Type aType, String value)
static String correlationTypeToInstanceTableName(CorrelationAttribute.Type type)
Definition: EamDbUtil.java:233
Long getCountUniqueCaseDataSourceTuplesHavingTypeValue(CorrelationAttribute.Type aType, String value)
CorrelationDataSource getEamDataSourceFromResultSet(ResultSet resultSet)
synchronized static Logger getLogger(String name)
Definition: Logger.java:161
EamGlobalFileInstance getEamGlobalFileInstanceFromResultSet(ResultSet resultSet)
final Map< String, Collection< CorrelationAttribute > > bulkArtifacts
static void closePreparedStatement(PreparedStatement preparedStatement)
Definition: EamDbUtil.java:48

Copyright © 2012-2016 Basis Technology. Generated on: Fri Sep 29 2017
This work is licensed under a Creative Commons Attribution-Share Alike 3.0 United States License.