Autopsy  4.19.1
Graphical digital forensics platform for The Sleuth Kit and other tools.
SingleUserCaseConverter.java
Go to the documentation of this file.
1 /*
2  * Autopsy Forensic Browser
3  *
4  * Copyright 2011-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.casemodule;
20 
21 import java.io.File;
22 import java.io.IOException;
23 import java.nio.file.Path;
24 import java.nio.file.Paths;
25 import java.sql.Connection;
26 import java.sql.DriverManager;
27 import java.sql.PreparedStatement;
28 import java.sql.ResultSet;
29 import java.sql.ResultSetMetaData;
30 import java.sql.SQLException;
31 import java.sql.Statement;
32 import java.text.SimpleDateFormat;
33 import java.util.Date;
34 import org.apache.commons.io.FileUtils;
35 import org.openide.util.NbBundle;
40 import org.sleuthkit.datamodel.CaseDbConnectionInfo;
41 import org.sleuthkit.datamodel.SleuthkitCase;
42 import org.sleuthkit.datamodel.TskData;
43 
50 
51  private static final String MODULE_FOLDER = "ModuleOutput"; //NON-NLS
52  private static final String AUTOPSY_DB_FILE = "autopsy.db"; //NON-NLS
53  private static final String DOTAUT = CaseMetadata.getFileExtension(); //NON-NLS
54  private static final String TIMELINE_FOLDER = "Timeline"; //NON-NLS
55  private static final String TIMELINE_FILE = "events.db"; //NON-NLS
56  private static final String POSTGRES_DEFAULT_DB_NAME = "postgres"; //NON-NLS
57  private static final int MAX_DB_NAME_LENGTH = 63;
58 
59  public class ImportCaseData {
60 
61  private final Path imageInputFolder;
62  private final Path caseInputFolder;
63  private final Path imageOutputFolder;
64  private final Path caseOutputFolder;
65  private final String oldCaseName;
66  private final String newCaseName;
67  private final boolean copySourceImages;
68  private final boolean deleteCase;
69  private String postgreSQLDbName;
70  private final String autFileName;
71  private final String rawFolderName;
72  private final CaseDbConnectionInfo db;
73 
75  Path imageInput,
76  Path caseInput,
77  Path imageOutput,
78  Path caseOutput,
79  String oldCaseName,
80  String newCaseName,
81  String autFileName,
82  String rawFolderName,
83  boolean copySourceImages,
84  boolean deleteCase) throws UserPreferencesException {
85 
86  this.imageInputFolder = imageInput;
87  this.caseInputFolder = caseInput;
88  this.imageOutputFolder = imageOutput;
89  this.caseOutputFolder = caseOutput;
90  this.oldCaseName = oldCaseName;
91  this.newCaseName = newCaseName;
92  this.autFileName = autFileName;
93  this.rawFolderName = rawFolderName;
94  this.copySourceImages = copySourceImages;
95  this.deleteCase = deleteCase;
97  }
98 
99  public Path getCaseInputFolder() {
100  return this.caseInputFolder;
101  }
102 
103  public Path getCaseOutputFolder() {
104  return this.caseOutputFolder;
105  }
106 
107  Path getImageInputFolder() {
108  return this.imageInputFolder;
109  }
110 
111  Path getImageOutputFolder() {
112  return this.imageOutputFolder;
113  }
114 
115  String getOldCaseName() {
116  return this.oldCaseName;
117  }
118 
119  String getNewCaseName() {
120  return this.newCaseName;
121  }
122 
123  boolean getCopySourceImages() {
124  return this.copySourceImages;
125  }
126 
127  boolean getDeleteCase() {
128  return this.deleteCase;
129  }
130 
131  String getPostgreSQLDbName() {
132  return this.postgreSQLDbName;
133  }
134 
135  String getAutFileName() {
136  return this.autFileName;
137  }
138 
139  String getRawFolderName() {
140  return this.rawFolderName;
141  }
142 
143  CaseDbConnectionInfo getDb() {
144  return this.db;
145  }
146 
147  void setPostgreSQLDbName(String dbName) {
148  this.postgreSQLDbName = dbName;
149  }
150  }
151 
161  public static void importCase(ImportCaseData icd) throws Exception {
162 
163  Class.forName("org.postgresql.Driver"); //NON-NLS
164 
165  // Make sure there is a SQLite databse file
166  Path oldDatabasePath = icd.getCaseInputFolder().resolve(AUTOPSY_DB_FILE);
167  if (false == oldDatabasePath.toFile().exists()) {
168  throw new Exception(NbBundle.getMessage(SingleUserCaseConverter.class, "SingleUserCaseConverter.BadDatabaseFileName")); //NON-NLS
169  }
170 
171  // Read old xml config
172  CaseMetadata oldCaseMetadata = new CaseMetadata(icd.getCaseInputFolder().resolve(icd.getAutFileName()));
173  if (oldCaseMetadata.getCaseType() == CaseType.MULTI_USER_CASE) {
174  throw new Exception(NbBundle.getMessage(SingleUserCaseConverter.class, "SingleUserCaseConverter.AlreadyMultiUser")); //NON-NLS
175  }
176 
177  // Create sanitized names for PostgreSQL and Solr
178  /*
179  * RJC: Removed package access sanitizeCaseName method, so this is no
180  * longer correct, but this whole class is currently out-of-date (out of
181  * synch with case database schema) and probably belongs in the TSK
182  * layer anyway, see JIRA-1984.
183  */
184  SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMdd_HHmmss"); //NON-NLS
185  Date date = new Date();
186  String dbName = icd.getNewCaseName() + "_" + dateFormat.format(date); //NON-NLS
187  icd.setPostgreSQLDbName(dbName);
188 
189  // Copy items to new hostname folder structure
190  copyResults(icd);
191 
192  // Convert from SQLite to PostgreSQL
193  importDb(icd);
194 
195  // Update paths inside databse
196  fixPaths(icd);
197 
198  // Copy images
199  copyImages(icd);
200 
201  // Create new .aut file
202  CaseMetadata newCaseMetadata = new CaseMetadata(CaseType.MULTI_USER_CASE,
203  icd.getCaseOutputFolder().toString(),
204  icd.getNewCaseName(),
205  new CaseDetails(icd.getNewCaseName(),
206  oldCaseMetadata.getCaseNumber(),
207  oldCaseMetadata.getExaminer(),
208  oldCaseMetadata.getExaminerPhone(),
209  oldCaseMetadata.getExaminerEmail(),
210  oldCaseMetadata.getCaseNotes()));
211  newCaseMetadata.setCaseDatabaseName(dbName);
212  // Set created date. This calls writefile, no need to call it again
213  newCaseMetadata.setCreatedDate(oldCaseMetadata.getCreatedDate());
214  newCaseMetadata.setCreatedByVersion(oldCaseMetadata.getCreatedByVersion());
215 
216  // At this point the import has been finished successfully so we can delete the original case
217  // (if requested). This *should* be fairly safe - at this point we know there was an autopsy file
218  // and database in the given directory so the user shouldn't be able to accidently blow away
219  // their C drive.
220  if (icd.getDeleteCase()) {
221  FileUtils.deleteDirectory(icd.getCaseInputFolder().toFile());
222  }
223  }
224 
232  private static File findInputFolder(ImportCaseData icd) {
233 
234  File thePath = icd.getImageInputFolder().resolve(icd.getOldCaseName()).toFile();
235  if (thePath.isDirectory()) {
237  return thePath;
238  }
239  thePath = icd.getImageInputFolder().resolve(icd.getRawFolderName()).toFile();
240  if (thePath.isDirectory()) {
242  return thePath;
243  }
244  return icd.getImageInputFolder().toFile();
245  }
246 
255  private static void copyResults(ImportCaseData icd) throws IOException {
257  String hostName = NetworkUtils.getLocalHostName();
258 
259  Path destination;
260  Path source = icd.getCaseInputFolder();
261  if (source.toFile().exists()) {
262  destination = icd.getCaseOutputFolder().resolve(hostName);
263  FileUtils.copyDirectory(source.toFile(), destination.toFile());
264  }
265 
266  source = icd.getCaseInputFolder().resolve(TIMELINE_FILE);
267  if (source.toFile().exists()) {
268  destination = Paths.get(icd.getCaseOutputFolder().toString(), hostName, MODULE_FOLDER, TIMELINE_FOLDER, TIMELINE_FILE);
269  FileUtils.copyFile(source.toFile(), destination.toFile());
270  }
271 
272  // Remove the single-user .aut file from the multi-user folder
273  File oldAutopsyFile = Paths.get(icd.getCaseOutputFolder().toString(), hostName, icd.getOldCaseName() + DOTAUT).toFile();
274  if (oldAutopsyFile.exists()) {
275  oldAutopsyFile.delete();
276  }
277 
278  // Remove the single-user database file from the multi-user folder
279  File oldDatabaseFile = Paths.get(icd.getCaseOutputFolder().toString(), hostName, AUTOPSY_DB_FILE).toFile();
280  if (oldDatabaseFile.exists()) {
281  oldDatabaseFile.delete();
282  }
283 
284  // Remove the single-user Timeline file from the multi-user folder
285  File oldTimelineFile = Paths.get(icd.getCaseOutputFolder().toString(), hostName, TIMELINE_FILE).toFile();
286  if (oldTimelineFile.exists()) {
287  oldTimelineFile.delete();
288  }
289  }
290 
302  private static void importDb(ImportCaseData icd) throws SQLException, ClassNotFoundException, Exception {
303  // deconflict the database name
305 
306  // Create a new database via SleuthkitCase
307  SleuthkitCase newCase = SleuthkitCase.newCase(icd.getPostgreSQLDbName(), icd.getDb(), icd.getCaseOutputFolder().toString());
308  newCase.close();
309 
311  Class.forName("org.sqlite.JDBC"); //NON-NLS
312  Connection sqliteConnection = getSQLiteConnection(icd);
313  Connection postgreSQLConnection = getPostgreSQLConnection(icd);
314 
315  // blackboard_artifact_types
316  Statement inputStatement = sqliteConnection.createStatement();
317  ResultSet inputResultSet = inputStatement.executeQuery("SELECT * FROM blackboard_artifact_types"); //NON-NLS
318  Statement outputStatement;
319  Statement numberingPK;
320  long biggestPK = 0;
321 
322  while (inputResultSet.next()) {
323  try {
324  long value = inputResultSet.getLong(1);
325  if (value > biggestPK) {
326  biggestPK = value;
327  }
328  Statement check = postgreSQLConnection.createStatement();
329  ResultSet checkResult = check.executeQuery("SELECT * FROM blackboard_artifact_types WHERE artifact_type_id=" + value + " AND type_name LIKE '" + inputResultSet.getString(2) + "' AND display_name LIKE '" + inputResultSet.getString(3) + "'"); //NON-NLS
330  if (!checkResult.isBeforeFirst()) { // only insert if it doesn't exist
331  String sql = "INSERT INTO blackboard_artifact_types (artifact_type_id, type_name, display_name) VALUES (" //NON-NLS
332  + value + ", '"
333  + SleuthkitCase.escapeSingleQuotes(inputResultSet.getString(2)) + "',"
334  + " ? )"; //NON-NLS
335  PreparedStatement pst = postgreSQLConnection.prepareStatement(sql);
336  populateNullableString(pst, inputResultSet, 3, 1);
337  pst.executeUpdate();
338  }
339  } catch (SQLException ex) {
340  if (ex.getErrorCode() != 0) { // 0 if the entry already exists
341  throw new SQLException(ex);
342  }
343  }
344  }
345  numberingPK = postgreSQLConnection.createStatement();
346  numberingPK.execute("ALTER SEQUENCE blackboard_artifact_types_artifact_type_id_seq RESTART WITH " + (biggestPK + 1)); //NON-NLS
347 
348  // blackboard_attribute_types
349  biggestPK = 0;
350  inputStatement = sqliteConnection.createStatement();
351  inputResultSet = inputStatement.executeQuery("SELECT * FROM blackboard_attribute_types"); //NON-NLS
352 
353  while (inputResultSet.next()) {
354  try {
355  long value = inputResultSet.getLong(1);
356  if (value > biggestPK) {
357  biggestPK = value;
358  }
359  Statement check = postgreSQLConnection.createStatement();
360  ResultSet checkResult = check.executeQuery("SELECT * FROM blackboard_attribute_types WHERE attribute_type_id=" + value + " AND type_name LIKE '" + inputResultSet.getString(2) + "' AND display_name LIKE '" + inputResultSet.getString(3) + "'"); //NON-NLS
361  if (!checkResult.isBeforeFirst()) { // only insert if it doesn't exist
362  String sql = "INSERT INTO blackboard_attribute_types (attribute_type_id, type_name, display_name) VALUES (" //NON-NLS
363  + value + ", '"
364  + SleuthkitCase.escapeSingleQuotes(inputResultSet.getString(2)) + "',"
365  + " ? )"; //NON-NLS
366 
367  PreparedStatement pst = postgreSQLConnection.prepareStatement(sql);
368  populateNullableString(pst, inputResultSet, 3, 1);
369  pst.executeUpdate();
370  }
371  } catch (SQLException ex) {
372  if (ex.getErrorCode() != 0) { // 0 if the entry already exists
373  throw new SQLException(ex);
374  }
375  }
376  }
377  numberingPK = postgreSQLConnection.createStatement();
378  numberingPK.execute("ALTER SEQUENCE blackboard_attribute_types_attribute_type_id_seq RESTART WITH " + (biggestPK + 1)); //NON-NLS
379 
380  // tsk_objects
381  biggestPK = 0;
382  inputStatement = sqliteConnection.createStatement();
383  inputResultSet = inputStatement.executeQuery("SELECT * FROM tsk_objects"); //NON-NLS
384 
385  while (inputResultSet.next()) {
386  outputStatement = postgreSQLConnection.createStatement();
387  try {
388  long value = inputResultSet.getLong(1);
389  if (value > biggestPK) {
390  biggestPK = value;
391  }
392  outputStatement.executeUpdate("INSERT INTO tsk_objects (obj_id, par_obj_id, type) VALUES (" //NON-NLS
393  + value + ","
394  + getNullableLong(inputResultSet, 2) + ","
395  + inputResultSet.getInt(3) + ")"); //NON-NLS
396  } catch (SQLException ex) {
397  if (ex.getErrorCode() != 0) { // 0 if the entry already exists
398  throw new SQLException(ex);
399  }
400  }
401  }
402  numberingPK = postgreSQLConnection.createStatement();
403  numberingPK.execute("ALTER SEQUENCE tsk_objects_obj_id_seq RESTART WITH " + (biggestPK + 1)); //NON-NLS
404 
405  // tsk_image_names, no primary key
406  inputStatement = sqliteConnection.createStatement();
407  inputResultSet = inputStatement.executeQuery("SELECT * FROM tsk_image_names"); //NON-NLS
408 
409  while (inputResultSet.next()) {
410  outputStatement = postgreSQLConnection.createStatement();
411  try {
412  outputStatement.executeUpdate("INSERT INTO tsk_image_names (obj_id, name, sequence) VALUES (" //NON-NLS
413  + inputResultSet.getLong(1) + ",'"
414  + inputResultSet.getString(2) + "',"
415  + inputResultSet.getInt(3) + ")"); //NON-NLS
416  } catch (SQLException ex) {
417  if (ex.getErrorCode() != 0) { // 0 if the entry already exists
418  throw new SQLException(ex);
419  }
420  }
421  }
422 
423  // tsk_image_info
424  biggestPK = 0;
425  inputStatement = sqliteConnection.createStatement();
426  inputResultSet = inputStatement.executeQuery("SELECT * FROM tsk_image_info"); //NON-NLS
427 
428  while (inputResultSet.next()) {
429  try {
430  long value = inputResultSet.getLong(1);
431  if (value > biggestPK) {
432  biggestPK = value;
433  }
434  String sql = "INSERT INTO tsk_image_info (obj_id, type, ssize, tzone, size, md5, display_name) VALUES (" //NON-NLS
435  + value + ","
436  + getNullableInt(inputResultSet, 2) + ","
437  + getNullableInt(inputResultSet, 3) + ","
438  + " ? ,"
439  + getNullableLong(inputResultSet, 5) + ","
440  + " ? ,"
441  + " ? )"; //NON-NLS
442 
443  PreparedStatement pst = postgreSQLConnection.prepareStatement(sql);
444  populateNullableString(pst, inputResultSet, 4, 1);
445  populateNullableString(pst, inputResultSet, 6, 2);
446  populateNullableString(pst, inputResultSet, 7, 3);
447  pst.executeUpdate();
448 
449  } catch (SQLException ex) {
450  if (ex.getErrorCode() != 0) { // 0 if the entry already exists
451  throw new SQLException(ex);
452  }
453  }
454  }
455  numberingPK = postgreSQLConnection.createStatement();
456  numberingPK.execute("ALTER SEQUENCE tsk_image_info_obj_id_seq RESTART WITH " + (biggestPK + 1)); //NON-NLS
457 
458  // tsk_fs_info
459  biggestPK = 0;
460  inputStatement = sqliteConnection.createStatement();
461  inputResultSet = inputStatement.executeQuery("SELECT * FROM tsk_fs_info"); //NON-NLS
462 
463  while (inputResultSet.next()) {
464  try {
465  long value = inputResultSet.getLong(1);
466  if (value > biggestPK) {
467  biggestPK = value;
468  }
469  String sql = "INSERT INTO tsk_fs_info (obj_id, img_offset, fs_type, block_size, block_count, root_inum, first_inum, last_inum, display_name) VALUES (" //NON-NLS
470  + value + ","
471  + inputResultSet.getLong(2) + ","
472  + inputResultSet.getInt(3) + ","
473  + inputResultSet.getLong(4) + ","
474  + inputResultSet.getLong(5) + ","
475  + inputResultSet.getLong(6) + ","
476  + inputResultSet.getLong(7) + ","
477  + inputResultSet.getLong(8) + ","
478  + " ? )"; //NON-NLS
479 
480  PreparedStatement pst = postgreSQLConnection.prepareStatement(sql);
481  populateNullableString(pst, inputResultSet, 9, 1);
482  pst.executeUpdate();
483 
484  } catch (SQLException ex) {
485  if (ex.getErrorCode() != 0) { // 0 if the entry already exists
486  throw new SQLException(ex);
487  }
488  }
489  }
490  numberingPK = postgreSQLConnection.createStatement();
491  numberingPK.execute("ALTER SEQUENCE tsk_fs_info_obj_id_seq RESTART WITH " + (biggestPK + 1)); //NON-NLS
492 
493  // tsk_files_path
494  biggestPK = 0;
495  inputStatement = sqliteConnection.createStatement();
496  inputResultSet = inputStatement.executeQuery("SELECT * FROM tsk_files_path"); //NON-NLS
497 
498  while (inputResultSet.next()) {
499  outputStatement = postgreSQLConnection.createStatement();
500  try {
501  long value = inputResultSet.getLong(1);
502  if (value > biggestPK) {
503  biggestPK = value;
504  }
505 
506  // If the entry contains an encoding type, copy it. Otherwise use NONE.
507  // The test on column count can be removed if we upgrade the database before conversion.
508  int encoding = TskData.EncodingType.NONE.getType();
509  ResultSetMetaData rsMetaData = inputResultSet.getMetaData();
510  if (rsMetaData.getColumnCount() == 3) {
511  encoding = inputResultSet.getInt(3);
512  }
513  outputStatement.executeUpdate("INSERT INTO tsk_files_path (obj_id, path, encoding_type) VALUES (" //NON-NLS
514  + value + ", '"
515  + SleuthkitCase.escapeSingleQuotes(inputResultSet.getString(2)) + "', "
516  + encoding + ")"); //NON-NLS
517  } catch (SQLException ex) {
518  if (ex.getErrorCode() != 0) { // 0 if the entry already exists
519  throw new SQLException(ex);
520  }
521  }
522  }
523  numberingPK = postgreSQLConnection.createStatement();
524  numberingPK.execute("ALTER SEQUENCE tsk_files_path_obj_id_seq RESTART WITH " + (biggestPK + 1)); //NON-NLS
525 
526  // tsk_files
527  biggestPK = 0;
528  inputStatement = sqliteConnection.createStatement();
529  inputResultSet = inputStatement.executeQuery("SELECT * FROM tsk_files"); //NON-NLS
530 
531  while (inputResultSet.next()) {
532  try {
533  long value = inputResultSet.getLong(1);
534  if (value > biggestPK) {
535  biggestPK = value;
536  }
537  String sql = "INSERT INTO tsk_files (obj_id, fs_obj_id, attr_type, attr_id, name, meta_addr, meta_seq, type, has_layout, has_path, dir_type, meta_type, dir_flags, meta_flags, size, ctime, crtime, atime, mtime, mode, uid, gid, md5, known, parent_path) VALUES (" //NON-NLS
538  + value + ","
539  + getNullableLong(inputResultSet, 2) + ","
540  + getNullableInt(inputResultSet, 3) + ","
541  + getNullableInt(inputResultSet, 4) + ",'"
542  + SleuthkitCase.escapeSingleQuotes(inputResultSet.getString(5)) + "',"
543  + getNullableLong(inputResultSet, 6) + ","
544  + getNullableLong(inputResultSet, 7) + ","
545  + getNullableInt(inputResultSet, 8) + ","
546  + getNullableInt(inputResultSet, 9) + ","
547  + getNullableInt(inputResultSet, 10) + ","
548  + getNullableInt(inputResultSet, 11) + ","
549  + getNullableInt(inputResultSet, 12) + ","
550  + getNullableInt(inputResultSet, 13) + ","
551  + getNullableInt(inputResultSet, 14) + ","
552  + getNullableLong(inputResultSet, 15) + ","
553  + getNullableLong(inputResultSet, 16) + ","
554  + getNullableLong(inputResultSet, 17) + ","
555  + getNullableLong(inputResultSet, 18) + ","
556  + getNullableLong(inputResultSet, 19) + ","
557  + getNullableInt(inputResultSet, 20) + ","
558  + getNullableInt(inputResultSet, 21) + ","
559  + getNullableInt(inputResultSet, 22) + ","
560  + " ? ,"
561  + getNullableInt(inputResultSet, 24) + ","
562  + " ? )"; //NON-NLS
563 
564  PreparedStatement pst = postgreSQLConnection.prepareStatement(sql);
565  populateNullableString(pst, inputResultSet, 23, 1);
566  populateNullableString(pst, inputResultSet, 25, 2);
567  pst.executeUpdate();
568 
569  } catch (SQLException ex) {
570  if (ex.getErrorCode() != 0) { // 0 if the entry already exists
571  throw new SQLException(ex);
572  }
573  }
574  }
575  numberingPK = postgreSQLConnection.createStatement();
576  numberingPK.execute("ALTER SEQUENCE tsk_files_obj_id_seq RESTART WITH " + (biggestPK + 1)); //NON-NLS
577 
578  // tsk_file_layout, no primary key
579  inputStatement = sqliteConnection.createStatement();
580  inputResultSet = inputStatement.executeQuery("SELECT * FROM tsk_file_layout"); //NON-NLS
581 
582  while (inputResultSet.next()) {
583  outputStatement = postgreSQLConnection.createStatement();
584  try {
585  outputStatement.executeUpdate("INSERT INTO tsk_file_layout (obj_id, byte_start, byte_len, sequence) VALUES (" //NON-NLS
586  + inputResultSet.getLong(1) + ","
587  + inputResultSet.getLong(2) + ","
588  + inputResultSet.getLong(3) + ","
589  + inputResultSet.getInt(4) + ")"); //NON-NLS
590  } catch (SQLException ex) {
591  if (ex.getErrorCode() != 0) { // 0 if the entry already exists
592  throw new SQLException(ex);
593  }
594  }
595  }
596 
597  // tsk_db_info, no primary key
598  inputStatement = sqliteConnection.createStatement();
599  inputResultSet = inputStatement.executeQuery("SELECT * FROM tsk_db_info"); //NON-NLS
600 
601  while (inputResultSet.next()) {
602  outputStatement = postgreSQLConnection.createStatement();
603  try {
604  Statement check = postgreSQLConnection.createStatement();
605  ResultSet checkResult = check.executeQuery("SELECT * FROM tsk_db_info WHERE schema_ver=" + inputResultSet.getInt(1) + " AND tsk_ver=" + inputResultSet.getInt(2)); //NON-NLS
606  if (!checkResult.isBeforeFirst()) { // only insert if it doesn't exist
607  outputStatement.executeUpdate("INSERT INTO tsk_db_info (schema_ver, tsk_ver) VALUES (" //NON-NLS
608  + getNullableInt(inputResultSet, 1) + ","
609  + getNullableInt(inputResultSet, 2) + ")"); //NON-NLS
610  }
611  } catch (SQLException ex) {
612  if (ex.getErrorCode() != 0) { // 0 if the entry already exists
613  throw new SQLException(ex);
614  }
615  }
616  }
617 
618  // tag_names
619  biggestPK = 0;
620  inputStatement = sqliteConnection.createStatement();
621  inputResultSet = inputStatement.executeQuery("SELECT * FROM tag_names"); //NON-NLS
622 
623  while (inputResultSet.next()) {
624  try {
625  long value = inputResultSet.getLong(1);
626  if (value > biggestPK) {
627  biggestPK = value;
628  }
629  String sql = "INSERT INTO tag_names (tag_name_id, display_name, description, color) VALUES (" //NON-NLS
630  + value + ","
631  + " ? ,'"
632  + SleuthkitCase.escapeSingleQuotes(inputResultSet.getString(3)) + "','"
633  + SleuthkitCase.escapeSingleQuotes(inputResultSet.getString(4)) + "')"; //NON-NLS
634 
635  PreparedStatement pst = postgreSQLConnection.prepareStatement(sql);
636  populateNullableString(pst, inputResultSet, 2, 1);
637  pst.executeUpdate();
638 
639  } catch (SQLException ex) {
640  if (ex.getErrorCode() != 0) { // 0 if the entry already exists
641  throw new SQLException(ex);
642  }
643  }
644  }
645  numberingPK = postgreSQLConnection.createStatement();
646  numberingPK.execute("ALTER SEQUENCE tag_names_tag_name_id_seq RESTART WITH " + (biggestPK + 1)); //NON-NLS
647 
648  // reports
649  biggestPK = 0;
650  inputStatement = sqliteConnection.createStatement();
651  inputResultSet = inputStatement.executeQuery("SELECT * FROM reports"); //NON-NLS
652 
653  while (inputResultSet.next()) {
654  outputStatement = postgreSQLConnection.createStatement();
655  try {
656  long value = inputResultSet.getLong(1);
657  if (value > biggestPK) {
658  biggestPK = value;
659  }
660  outputStatement.executeUpdate("INSERT INTO reports (report_id, path, crtime, src_module_name, report_name) VALUES (" //NON-NLS
661  + value + ", '"
662  + SleuthkitCase.escapeSingleQuotes(inputResultSet.getString(2)) + "',"
663  + inputResultSet.getInt(3) + ",'"
664  + SleuthkitCase.escapeSingleQuotes(inputResultSet.getString(4)) + "','"
665  + SleuthkitCase.escapeSingleQuotes(inputResultSet.getString(5)) + "')"); //NON-NLS
666 
667  } catch (SQLException ex) {
668  if (ex.getErrorCode() != 0) { // 0 if the entry already exists
669  throw new SQLException(ex);
670  }
671  }
672  }
673  numberingPK = postgreSQLConnection.createStatement();
674  numberingPK.execute("ALTER SEQUENCE reports_report_id_seq RESTART WITH " + (biggestPK + 1)); //NON-NLS
675 
676  // blackboard_artifacts
677  biggestPK = Long.MIN_VALUE; // This table uses very large negative primary key values, so start at Long.MIN_VALUE
678  inputStatement = sqliteConnection.createStatement();
679  inputResultSet = inputStatement.executeQuery("SELECT * FROM blackboard_artifacts"); //NON-NLS
680 
681  while (inputResultSet.next()) {
682  outputStatement = postgreSQLConnection.createStatement();
683  try {
684  long value = inputResultSet.getLong(1);
685  if (value > biggestPK) {
686  biggestPK = value;
687  }
688  outputStatement.executeUpdate("INSERT INTO blackboard_artifacts (artifact_id, obj_id, artifact_type_id) VALUES (" //NON-NLS
689  + value + ","
690  + inputResultSet.getLong(2) + ","
691  + inputResultSet.getLong(3) + ")"); //NON-NLS
692 
693  } catch (SQLException ex) {
694  if (ex.getErrorCode() != 0) { // 0 if the entry already exists
695  throw new SQLException(ex);
696  }
697  }
698  }
699  numberingPK = postgreSQLConnection.createStatement();
700  numberingPK.execute("ALTER SEQUENCE blackboard_artifacts_artifact_id_seq RESTART WITH " + (biggestPK + 1)); //NON-NLS
701 
702  // blackboard_attributes, no primary key
703  inputStatement = sqliteConnection.createStatement();
704  inputResultSet = inputStatement.executeQuery("SELECT * FROM blackboard_attributes"); //NON-NLS
705 
706  while (inputResultSet.next()) {
707  try {
708  String sql = "INSERT INTO blackboard_attributes (artifact_id, artifact_type_id, source, context, attribute_type_id, value_type, value_byte, value_text, value_int32, value_int64, value_double) VALUES (" //NON-NLS
709  + inputResultSet.getLong(1) + ","
710  + inputResultSet.getLong(2) + ","
711  + " ? ,"
712  + " ? ,"
713  + inputResultSet.getLong(5) + ","
714  + inputResultSet.getInt(6) + ","
715  + " ? ,"
716  + " ? ,"
717  + getNullableInt(inputResultSet, 9) + ","
718  + getNullableLong(inputResultSet, 10) + ","
719  + " ? )"; //NON-NLS
720  PreparedStatement pst = postgreSQLConnection.prepareStatement(sql);
721  populateNullableString(pst, inputResultSet, 3, 1);
722  populateNullableString(pst, inputResultSet, 4, 2);
723  populateNullableByteArray(pst, inputResultSet, 7, 3);
724  populateNullableString(pst, inputResultSet, 8, 4);
725  populateNullableNumeric(pst, inputResultSet, 11, 5);
726  pst.executeUpdate();
727 
728  } catch (SQLException ex) {
729  if (ex.getErrorCode() != 0) { // 0 if the entry already exists
730  throw new SQLException(ex);
731  }
732  }
733  }
734 
735  // tsk_vs_parts
736  biggestPK = 0;
737  inputStatement = sqliteConnection.createStatement();
738  inputResultSet = inputStatement.executeQuery("SELECT * FROM tsk_vs_parts"); //NON-NLS
739 
740  while (inputResultSet.next()) {
741  try {
742  long value = inputResultSet.getLong(1);
743  if (value > biggestPK) {
744  biggestPK = value;
745  }
746  String sql = "INSERT INTO tsk_vs_parts (obj_id, addr, start, length, descr, flags) VALUES (" //NON-NLS
747  + value + ","
748  + inputResultSet.getLong(2) + ","
749  + inputResultSet.getLong(3) + ","
750  + inputResultSet.getLong(4) + ","
751  + " ? ,"
752  + inputResultSet.getInt(6) + ")"; //NON-NLS
753  PreparedStatement pst = postgreSQLConnection.prepareStatement(sql);
754  populateNullableString(pst, inputResultSet, 5, 1);
755  pst.executeUpdate();
756 
757  } catch (SQLException ex) {
758  if (ex.getErrorCode() != 0) { // 0 if the entry already exists
759  throw new SQLException(ex);
760  }
761  }
762  }
763  numberingPK = postgreSQLConnection.createStatement();
764  numberingPK.execute("ALTER SEQUENCE tsk_vs_parts_obj_id_seq RESTART WITH " + (biggestPK + 1)); //NON-NLS
765 
766  // tsk_vs_info
767  biggestPK = 0;
768  inputStatement = sqliteConnection.createStatement();
769  inputResultSet = inputStatement.executeQuery("SELECT * FROM tsk_vs_info"); //NON-NLS
770 
771  while (inputResultSet.next()) {
772  outputStatement = postgreSQLConnection.createStatement();
773  try {
774  long value = inputResultSet.getLong(1);
775  if (value > biggestPK) {
776  biggestPK = value;
777  }
778  outputStatement.executeUpdate("INSERT INTO tsk_vs_info (obj_id, vs_type, img_offset, block_size) VALUES (" //NON-NLS
779  + value + ","
780  + inputResultSet.getInt(2) + ","
781  + inputResultSet.getLong(3) + ","
782  + inputResultSet.getLong(4) + ")"); //NON-NLS
783 
784  } catch (SQLException ex) {
785  if (ex.getErrorCode() != 0) { // 0 if the entry already exists
786  throw new SQLException(ex);
787  }
788  }
789  }
790  numberingPK = postgreSQLConnection.createStatement();
791  numberingPK.execute("ALTER SEQUENCE tsk_vs_info_obj_id_seq RESTART WITH " + (biggestPK + 1)); //NON-NLS
792 
793  // tsk_files_derived
794  biggestPK = 0;
795  inputStatement = sqliteConnection.createStatement();
796  inputResultSet = inputStatement.executeQuery("SELECT * FROM tsk_files_derived"); //NON-NLS
797 
798  while (inputResultSet.next()) {
799  try {
800  long value = inputResultSet.getLong(1);
801  if (value > biggestPK) {
802  biggestPK = value;
803  }
804  String sql = "INSERT INTO tsk_files_derived (obj_id, derived_id, rederive) VALUES (" //NON-NLS
805  + value + ","
806  + inputResultSet.getLong(2) + ","
807  + " ? )"; //NON-NLS
808  PreparedStatement pst = postgreSQLConnection.prepareStatement(sql);
809  populateNullableString(pst, inputResultSet, 3, 1);
810  pst.executeUpdate();
811 
812  } catch (SQLException ex) {
813  if (ex.getErrorCode() != 0) { // 0 if the entry already exists
814  throw new SQLException(ex);
815  }
816  }
817  }
818  numberingPK = postgreSQLConnection.createStatement();
819  numberingPK.execute("ALTER SEQUENCE tsk_files_derived_obj_id_seq RESTART WITH " + (biggestPK + 1)); //NON-NLS
820 
821  // tsk_files_derived_method
822  biggestPK = 0;
823  inputStatement = sqliteConnection.createStatement();
824  inputResultSet = inputStatement.executeQuery("SELECT * FROM tsk_files_derived_method"); //NON-NLS
825 
826  while (inputResultSet.next()) {
827  try {
828  long value = inputResultSet.getLong(1);
829  if (value > biggestPK) {
830  biggestPK = value;
831  }
832  String sql = "INSERT INTO tsk_files_derived_method (derived_id, tool_name, tool_version, other) VALUES (" //NON-NLS
833  + value + ", '"
834  + inputResultSet.getString(2) + "','"
835  + inputResultSet.getString(3) + "',"
836  + " ? )"; //NON-NLS
837  PreparedStatement pst = postgreSQLConnection.prepareStatement(sql);
838  populateNullableString(pst, inputResultSet, 4, 1);
839  pst.executeUpdate();
840 
841  } catch (SQLException ex) {
842  if (ex.getErrorCode() != 0) { // 0 if the entry already exists
843  throw new SQLException(ex);
844  }
845  }
846  }
847  numberingPK = postgreSQLConnection.createStatement();
848  numberingPK.execute("ALTER SEQUENCE tsk_files_derived_method_derived_id_seq RESTART WITH " + (biggestPK + 1)); //NON-NLS
849 
850  // content_tags
851  biggestPK = 0;
852  inputStatement = sqliteConnection.createStatement();
853  inputResultSet = inputStatement.executeQuery("SELECT * FROM content_tags LEFT OUTER JOIN tsk_examiners ON content_tags.examiner_id = tsk_examiners.examiner_id"); //NON-NLS
854 
855  while (inputResultSet.next()) {
856  outputStatement = postgreSQLConnection.createStatement();
857  try {
858  long value = inputResultSet.getLong(1);
859  if (value > biggestPK) {
860  biggestPK = value;
861  }
862  outputStatement.executeUpdate("INSERT INTO content_tags (tag_id, obj_id, tag_name_id, comment, begin_byte_offset, end_byte_offset, examiner_id) VALUES (" //NON-NLS
863  + value + ","
864  + inputResultSet.getLong(2) + ","
865  + inputResultSet.getLong(3) + ",'"
866  + inputResultSet.getString(4) + "',"
867  + inputResultSet.getLong(5) + ","
868  + inputResultSet.getLong(6) + ","
869  + inputResultSet.getInt(7) + ")"); //NON-NLS
870 
871  } catch (SQLException ex) {
872  if (ex.getErrorCode() != 0) { // 0 if the entry already exists
873  throw new SQLException(ex);
874  }
875  }
876  }
877  numberingPK = postgreSQLConnection.createStatement();
878  numberingPK.execute("ALTER SEQUENCE content_tags_tag_id_seq RESTART WITH " + (biggestPK + 1)); //NON-NLS
879 
880  // blackboard_artifact_tags
881  biggestPK = 0;
882  inputStatement = sqliteConnection.createStatement();
883  inputResultSet = inputStatement.executeQuery("SELECT * FROM blackboard_artifact_tags"); //NON-NLS
884 
885  while (inputResultSet.next()) {
886  outputStatement = postgreSQLConnection.createStatement();
887  try {
888  long value = inputResultSet.getLong(1);
889  if (value > biggestPK) {
890  biggestPK = value;
891  }
892  outputStatement.executeUpdate("INSERT INTO blackboard_artifact_tags (tag_id, artifact_id, tag_name_id, comment) VALUES (" //NON-NLS
893  + value + ","
894  + inputResultSet.getLong(2) + ","
895  + inputResultSet.getLong(3) + ",'"
896  + inputResultSet.getString(4) + "','"
897  + inputResultSet.getString(5) + "')"); //NON-NLS
898 
899  } catch (SQLException ex) {
900  if (ex.getErrorCode() != 0) { // 0 if the entry already exists
901  throw new SQLException(ex);
902  }
903  }
904  }
905  numberingPK = postgreSQLConnection.createStatement();
906  numberingPK.execute("ALTER SEQUENCE blackboard_artifact_tags_tag_id_seq RESTART WITH " + (biggestPK + 1)); //NON-NLS
907 
908  sqliteConnection.close();
909  postgreSQLConnection.close();
910  }
911 
923  private static void deconflictDatabaseName(ImportCaseData icd) throws ClassNotFoundException, SQLException, Exception {
924 
925  Connection postgreSQLConnection = getPostgreSQLConnection(icd, POSTGRES_DEFAULT_DB_NAME);
926 
927  int number = 1;
928  boolean unique = false;
929  String sanitizedDbName = icd.getPostgreSQLDbName();
930  if (sanitizedDbName.length() > MAX_DB_NAME_LENGTH) {
931  sanitizedDbName = sanitizedDbName.substring(0, MAX_DB_NAME_LENGTH);
932  }
933 
934  if (postgreSQLConnection != null) {
935  while (unique == false) {
936  Statement st = postgreSQLConnection.createStatement();
937  ResultSet answer = st.executeQuery("SELECT datname FROM pg_catalog.pg_database WHERE LOWER(datname) LIKE LOWER('" + sanitizedDbName + "%')"); //NON-NLS
938 
939  if (!answer.next()) {
940  unique = true;
941  } else {
942  // not unique. add numbers to db name.
943  if (number == Integer.MAX_VALUE) {
944  // oops. it never became unique. give up.
945  throw new Exception(NbBundle.getMessage(SingleUserCaseConverter.class, "SingleUserCaseConverter.NonUniqueDatabaseName")); //NON-NLS
946  }
947  sanitizedDbName = "db_" + Integer.toString(number) + "_" + icd.getPostgreSQLDbName(); //NON-NLS
948 
949  // Chop full db name to 63 characters (max for PostgreSQL)
950  if (sanitizedDbName.length() > MAX_DB_NAME_LENGTH) {
951  sanitizedDbName = sanitizedDbName.substring(0, MAX_DB_NAME_LENGTH);
952  }
953  ++number;
954  }
955  }
956  postgreSQLConnection.close();
957  } else {
958  // Could be caused by database credentials, using user accounts that
959  // can not check if other databases exist, so allow it to continue
960  }
961 
962  icd.setPostgreSQLDbName(sanitizedDbName);
963  }
964 
973  private static void copyImages(ImportCaseData icd) throws Exception {
974  if (icd.getCopySourceImages()) {
975  File imageSource = findInputFolder(icd); // Find the folder for the input images
976  File imageDestination = new File(icd.getImageOutputFolder().toString());
977 
978  // If we can find the input images, copy if needed.
979  if (imageSource.exists()) {
980  FileUtils.copyDirectory(imageSource, imageDestination);
981 
982  } else {
983  throw new Exception(NbBundle.getMessage(SingleUserCaseConverter.class, "SingleUserCaseConverter.UnableToCopySourceImages")); //NON-NLS
984  }
985  }
986  }
987 
997  private static void fixPaths(ImportCaseData icd) throws SQLException, Exception {
999 
1000  String input = icd.getImageInputFolder().toString();
1001  String output = icd.getImageOutputFolder().toString();
1002 
1003  Connection postgresqlConnection = getPostgreSQLConnection(icd);
1004 
1005  if (postgresqlConnection != null) {
1006  String hostName = NetworkUtils.getLocalHostName();
1007 
1008  // add hostname to reports
1009  Statement updateStatement = postgresqlConnection.createStatement();
1010  updateStatement.executeUpdate("UPDATE reports SET path=CONCAT('" + hostName + "/', path) WHERE path IS NOT NULL AND path != ''"); //NON-NLS
1011 
1012  // add hostname to tsk_files_path
1013  updateStatement = postgresqlConnection.createStatement();
1014  updateStatement.executeUpdate("UPDATE tsk_files_path SET path=CONCAT('" + hostName + "\\', path) WHERE path IS NOT NULL AND path != ''"); //NON-NLS
1015 
1016  String caseName = icd.getRawFolderName().toLowerCase();
1017 
1018  if (icd.getCopySourceImages()) {
1019  // update path for images
1020  Statement inputStatement = postgresqlConnection.createStatement();
1021  ResultSet inputResultSet = inputStatement.executeQuery("SELECT * FROM tsk_image_names"); //NON-NLS
1022 
1023  while (inputResultSet.next()) {
1024  Path oldPath = Paths.get(inputResultSet.getString(2));
1025 
1026  for (int x = 0; x < oldPath.getNameCount(); ++x) {
1027  if (oldPath.getName(x).toString().toLowerCase().equals(caseName)) {
1028  Path newPath = Paths.get(output, oldPath.subpath(x + 1, oldPath.getNameCount()).toString());
1029  updateStatement = postgresqlConnection.createStatement();
1030  updateStatement.executeUpdate("UPDATE tsk_image_names SET name='" + newPath.toString() + "' WHERE obj_id = " + inputResultSet.getInt(1)); //NON-NLS
1031  break;
1032  }
1033  }
1034  }
1035  }
1036  postgresqlConnection.close();
1037  } else {
1038  throw new Exception(NbBundle.getMessage(SingleUserCaseConverter.class, "SingleUserCaseConverter.CanNotOpenDatabase")); //NON-NLS
1039  }
1040  }
1041 
1053  private static String getNullableInt(ResultSet rs, int index) throws SQLException {
1054  int value = rs.getInt(index);
1055  if (rs.wasNull()) {
1056  return "NULL"; //NON-NLS
1057  } else {
1058  return Integer.toString(value);
1059  }
1060  }
1061 
1073  private static String getNullableLong(ResultSet rs, int index) throws SQLException {
1074  long value = rs.getLong(index);
1075  if (rs.wasNull()) {
1076  return "NULL"; //NON-NLS
1077  } else {
1078  return Long.toString(value);
1079  }
1080  }
1081 
1093  private static void populateNullableString(PreparedStatement pst, ResultSet rs, int rsIndex, int psIndex) throws SQLException {
1094  String nullableString = rs.getString(rsIndex);
1095  if (rs.wasNull()) {
1096  pst.setNull(psIndex, java.sql.Types.NULL);
1097  } else {
1098  pst.setString(psIndex, SleuthkitCase.escapeSingleQuotes(nullableString));
1099  }
1100  }
1101 
1113  private static void populateNullableByteArray(PreparedStatement pst, ResultSet rs, int rsIndex, int psIndex) throws SQLException {
1114  byte[] nullableBytes = rs.getBytes(rsIndex);
1115  if (rs.wasNull()) {
1116  pst.setNull(psIndex, java.sql.Types.NULL);
1117  } else {
1118  pst.setBytes(psIndex, nullableBytes);
1119  }
1120  }
1121 
1133  private static void populateNullableNumeric(PreparedStatement pst, ResultSet rs, int rsIndex, int psIndex) throws SQLException {
1134  double nullableNumeric = rs.getDouble(rsIndex);
1135  if (rs.wasNull()) {
1136  pst.setNull(psIndex, java.sql.Types.NULL);
1137  } else {
1138  pst.setDouble(psIndex, nullableNumeric);
1139  }
1140  }
1141 
1151  private static Connection getPostgreSQLConnection(ImportCaseData icd) throws SQLException {
1152  return getPostgreSQLConnection(icd, icd.getPostgreSQLDbName());
1153  }
1154 
1165  private static Connection getPostgreSQLConnection(ImportCaseData icd, String dbName) throws SQLException {
1166  return DriverManager.getConnection("jdbc:postgresql://" //NON-NLS
1167  + icd.getDb().getHost() + ":"
1168  + icd.getDb().getPort() + "/"
1169  + dbName,
1170  icd.getDb().getUserName(),
1171  icd.getDb().getPassword()); //NON-NLS
1172  }
1173 
1183  private static Connection getSQLiteConnection(ImportCaseData icd) throws SQLException {
1184  return DriverManager.getConnection("jdbc:sqlite:" + icd.getCaseInputFolder().resolve(AUTOPSY_DB_FILE).toString(), "", ""); //NON-NLS
1185  }
1186 
1187 }
static void populateNullableNumeric(PreparedStatement pst, ResultSet rs, int rsIndex, int psIndex)
ImportCaseData(Path imageInput, Path caseInput, Path imageOutput, Path caseOutput, String oldCaseName, String newCaseName, String autFileName, String rawFolderName, boolean copySourceImages, boolean deleteCase)
static CaseDbConnectionInfo getDatabaseConnectionInfo()
static void populateNullableString(PreparedStatement pst, ResultSet rs, int rsIndex, int psIndex)
static void populateNullableByteArray(PreparedStatement pst, ResultSet rs, int rsIndex, int psIndex)
static Connection getPostgreSQLConnection(ImportCaseData icd, String dbName)

Copyright © 2012-2021 Basis Technology. Generated on: Thu Sep 30 2021
This work is licensed under a Creative Commons Attribution-Share Alike 3.0 United States License.