Autopsy  4.4
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(icd.getCaseOutputFolder().toString(),
204  icd.getNewCaseName(),
205  icd.getNewCaseName(),
206  oldCaseMetadata.getCaseNumber(),
207  oldCaseMetadata.getExaminer());
208  newCaseMetadata.setCaseDatabaseName(dbName);
209  // Set created date. This calls writefile, no need to call it again
210  newCaseMetadata.setCreatedDate(oldCaseMetadata.getCreatedDate());
211  newCaseMetadata.setCreatedByVersion(oldCaseMetadata.getCreatedByVersion());
212 
213  // At this point the import has been finished successfully so we can delete the original case
214  // (if requested). This *should* be fairly safe - at this point we know there was an autopsy file
215  // and database in the given directory so the user shouldn't be able to accidently blow away
216  // their C drive.
217  if (icd.getDeleteCase()) {
218  FileUtils.deleteDirectory(icd.getCaseInputFolder().toFile());
219  }
220  }
221 
229  private static File findInputFolder(ImportCaseData icd) {
230 
231  File thePath = icd.getImageInputFolder().resolve(icd.getOldCaseName()).toFile();
232  if (thePath.isDirectory()) {
234  return thePath;
235  }
236  thePath = icd.getImageInputFolder().resolve(icd.getRawFolderName()).toFile();
237  if (thePath.isDirectory()) {
239  return thePath;
240  }
241  return icd.getImageInputFolder().toFile();
242  }
243 
252  private static void copyResults(ImportCaseData icd) throws IOException {
254  String hostName = NetworkUtils.getLocalHostName();
255 
256  Path destination;
257  Path source = icd.getCaseInputFolder();
258  if (source.toFile().exists()) {
259  destination = icd.getCaseOutputFolder().resolve(hostName);
260  FileUtils.copyDirectory(source.toFile(), destination.toFile());
261  }
262 
263  source = icd.getCaseInputFolder().resolve(TIMELINE_FILE);
264  if (source.toFile().exists()) {
265  destination = Paths.get(icd.getCaseOutputFolder().toString(), hostName, MODULE_FOLDER, TIMELINE_FOLDER, TIMELINE_FILE);
266  FileUtils.copyFile(source.toFile(), destination.toFile());
267  }
268 
269  // Remove the single-user .aut file from the multi-user folder
270  File oldAutopsyFile = Paths.get(icd.getCaseOutputFolder().toString(), hostName, icd.getOldCaseName() + DOTAUT).toFile();
271  if (oldAutopsyFile.exists()) {
272  oldAutopsyFile.delete();
273  }
274 
275  // Remove the single-user database file from the multi-user folder
276  File oldDatabaseFile = Paths.get(icd.getCaseOutputFolder().toString(), hostName, AUTOPSY_DB_FILE).toFile();
277  if (oldDatabaseFile.exists()) {
278  oldDatabaseFile.delete();
279  }
280 
281  // Remove the single-user Timeline file from the multi-user folder
282  File oldTimelineFile = Paths.get(icd.getCaseOutputFolder().toString(), hostName, TIMELINE_FILE).toFile();
283  if (oldTimelineFile.exists()) {
284  oldTimelineFile.delete();
285  }
286  }
287 
299  private static void importDb(ImportCaseData icd) throws SQLException, ClassNotFoundException, Exception {
300  // deconflict the database name
302 
303  // Create a new database via SleuthkitCase
304  SleuthkitCase newCase = SleuthkitCase.newCase(icd.getPostgreSQLDbName(), icd.getDb(), icd.getCaseOutputFolder().toString());
305  newCase.close();
306 
308  Class.forName("org.sqlite.JDBC"); //NON-NLS
309  Connection sqliteConnection = getSQLiteConnection(icd);
310  Connection postgreSQLConnection = getPostgreSQLConnection(icd);
311 
312  // blackboard_artifact_types
313  Statement inputStatement = sqliteConnection.createStatement();
314  ResultSet inputResultSet = inputStatement.executeQuery("SELECT * FROM blackboard_artifact_types"); //NON-NLS
315  Statement outputStatement;
316  Statement numberingPK;
317  long biggestPK = 0;
318 
319  while (inputResultSet.next()) {
320  try {
321  long value = inputResultSet.getLong(1);
322  if (value > biggestPK) {
323  biggestPK = value;
324  }
325  Statement check = postgreSQLConnection.createStatement();
326  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
327  if (!checkResult.isBeforeFirst()) { // only insert if it doesn't exist
328  String sql = "INSERT INTO blackboard_artifact_types (artifact_type_id, type_name, display_name) VALUES (" //NON-NLS
329  + value + ", '"
330  + SleuthkitCase.escapeSingleQuotes(inputResultSet.getString(2)) + "',"
331  + " ? )"; //NON-NLS
332  PreparedStatement pst = postgreSQLConnection.prepareStatement(sql);
333  populateNullableString(pst, inputResultSet, 3, 1);
334  pst.executeUpdate();
335  }
336  } catch (SQLException ex) {
337  if (ex.getErrorCode() != 0) { // 0 if the entry already exists
338  throw new SQLException(ex);
339  }
340  }
341  }
342  numberingPK = postgreSQLConnection.createStatement();
343  numberingPK.execute("ALTER SEQUENCE blackboard_artifact_types_artifact_type_id_seq RESTART WITH " + (biggestPK + 1)); //NON-NLS
344 
345  // blackboard_attribute_types
346  biggestPK = 0;
347  inputStatement = sqliteConnection.createStatement();
348  inputResultSet = inputStatement.executeQuery("SELECT * FROM blackboard_attribute_types"); //NON-NLS
349 
350  while (inputResultSet.next()) {
351  try {
352  long value = inputResultSet.getLong(1);
353  if (value > biggestPK) {
354  biggestPK = value;
355  }
356  Statement check = postgreSQLConnection.createStatement();
357  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
358  if (!checkResult.isBeforeFirst()) { // only insert if it doesn't exist
359  String sql = "INSERT INTO blackboard_attribute_types (attribute_type_id, type_name, display_name) VALUES (" //NON-NLS
360  + value + ", '"
361  + SleuthkitCase.escapeSingleQuotes(inputResultSet.getString(2)) + "',"
362  + " ? )"; //NON-NLS
363 
364  PreparedStatement pst = postgreSQLConnection.prepareStatement(sql);
365  populateNullableString(pst, inputResultSet, 3, 1);
366  pst.executeUpdate();
367  }
368  } catch (SQLException ex) {
369  if (ex.getErrorCode() != 0) { // 0 if the entry already exists
370  throw new SQLException(ex);
371  }
372  }
373  }
374  numberingPK = postgreSQLConnection.createStatement();
375  numberingPK.execute("ALTER SEQUENCE blackboard_attribute_types_attribute_type_id_seq RESTART WITH " + (biggestPK + 1)); //NON-NLS
376 
377  // tsk_objects
378  biggestPK = 0;
379  inputStatement = sqliteConnection.createStatement();
380  inputResultSet = inputStatement.executeQuery("SELECT * FROM tsk_objects"); //NON-NLS
381 
382  while (inputResultSet.next()) {
383  outputStatement = postgreSQLConnection.createStatement();
384  try {
385  long value = inputResultSet.getLong(1);
386  if (value > biggestPK) {
387  biggestPK = value;
388  }
389  outputStatement.executeUpdate("INSERT INTO tsk_objects (obj_id, par_obj_id, type) VALUES (" //NON-NLS
390  + value + ","
391  + getNullableLong(inputResultSet, 2) + ","
392  + inputResultSet.getInt(3) + ")"); //NON-NLS
393  } catch (SQLException ex) {
394  if (ex.getErrorCode() != 0) { // 0 if the entry already exists
395  throw new SQLException(ex);
396  }
397  }
398  }
399  numberingPK = postgreSQLConnection.createStatement();
400  numberingPK.execute("ALTER SEQUENCE tsk_objects_obj_id_seq RESTART WITH " + (biggestPK + 1)); //NON-NLS
401 
402  // tsk_image_names, no primary key
403  inputStatement = sqliteConnection.createStatement();
404  inputResultSet = inputStatement.executeQuery("SELECT * FROM tsk_image_names"); //NON-NLS
405 
406  while (inputResultSet.next()) {
407  outputStatement = postgreSQLConnection.createStatement();
408  try {
409  outputStatement.executeUpdate("INSERT INTO tsk_image_names (obj_id, name, sequence) VALUES (" //NON-NLS
410  + inputResultSet.getLong(1) + ",'"
411  + inputResultSet.getString(2) + "',"
412  + inputResultSet.getInt(3) + ")"); //NON-NLS
413  } catch (SQLException ex) {
414  if (ex.getErrorCode() != 0) { // 0 if the entry already exists
415  throw new SQLException(ex);
416  }
417  }
418  }
419 
420  // tsk_image_info
421  biggestPK = 0;
422  inputStatement = sqliteConnection.createStatement();
423  inputResultSet = inputStatement.executeQuery("SELECT * FROM tsk_image_info"); //NON-NLS
424 
425  while (inputResultSet.next()) {
426  try {
427  long value = inputResultSet.getLong(1);
428  if (value > biggestPK) {
429  biggestPK = value;
430  }
431  String sql = "INSERT INTO tsk_image_info (obj_id, type, ssize, tzone, size, md5, display_name) VALUES (" //NON-NLS
432  + value + ","
433  + getNullableInt(inputResultSet, 2) + ","
434  + getNullableInt(inputResultSet, 3) + ","
435  + " ? ,"
436  + getNullableLong(inputResultSet, 5) + ","
437  + " ? ,"
438  + " ? )"; //NON-NLS
439 
440  PreparedStatement pst = postgreSQLConnection.prepareStatement(sql);
441  populateNullableString(pst, inputResultSet, 4, 1);
442  populateNullableString(pst, inputResultSet, 6, 2);
443  populateNullableString(pst, inputResultSet, 7, 3);
444  pst.executeUpdate();
445 
446  } catch (SQLException ex) {
447  if (ex.getErrorCode() != 0) { // 0 if the entry already exists
448  throw new SQLException(ex);
449  }
450  }
451  }
452  numberingPK = postgreSQLConnection.createStatement();
453  numberingPK.execute("ALTER SEQUENCE tsk_image_info_obj_id_seq RESTART WITH " + (biggestPK + 1)); //NON-NLS
454 
455  // tsk_fs_info
456  biggestPK = 0;
457  inputStatement = sqliteConnection.createStatement();
458  inputResultSet = inputStatement.executeQuery("SELECT * FROM tsk_fs_info"); //NON-NLS
459 
460  while (inputResultSet.next()) {
461  try {
462  long value = inputResultSet.getLong(1);
463  if (value > biggestPK) {
464  biggestPK = value;
465  }
466  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
467  + value + ","
468  + inputResultSet.getLong(2) + ","
469  + inputResultSet.getInt(3) + ","
470  + inputResultSet.getLong(4) + ","
471  + inputResultSet.getLong(5) + ","
472  + inputResultSet.getLong(6) + ","
473  + inputResultSet.getLong(7) + ","
474  + inputResultSet.getLong(8) + ","
475  + " ? )"; //NON-NLS
476 
477  PreparedStatement pst = postgreSQLConnection.prepareStatement(sql);
478  populateNullableString(pst, inputResultSet, 9, 1);
479  pst.executeUpdate();
480 
481  } catch (SQLException ex) {
482  if (ex.getErrorCode() != 0) { // 0 if the entry already exists
483  throw new SQLException(ex);
484  }
485  }
486  }
487  numberingPK = postgreSQLConnection.createStatement();
488  numberingPK.execute("ALTER SEQUENCE tsk_fs_info_obj_id_seq RESTART WITH " + (biggestPK + 1)); //NON-NLS
489 
490  // tsk_files_path
491  biggestPK = 0;
492  inputStatement = sqliteConnection.createStatement();
493  inputResultSet = inputStatement.executeQuery("SELECT * FROM tsk_files_path"); //NON-NLS
494 
495  while (inputResultSet.next()) {
496  outputStatement = postgreSQLConnection.createStatement();
497  try {
498  long value = inputResultSet.getLong(1);
499  if (value > biggestPK) {
500  biggestPK = value;
501  }
502 
503  // If the entry contains an encoding type, copy it. Otherwise use NONE.
504  // The test on column count can be removed if we upgrade the database before conversion.
505  int encoding = TskData.EncodingType.NONE.getType();
506  ResultSetMetaData rsMetaData = inputResultSet.getMetaData();
507  if (rsMetaData.getColumnCount() == 3) {
508  encoding = inputResultSet.getInt(3);
509  }
510  outputStatement.executeUpdate("INSERT INTO tsk_files_path (obj_id, path, encoding_type) VALUES (" //NON-NLS
511  + value + ", '"
512  + SleuthkitCase.escapeSingleQuotes(inputResultSet.getString(2)) + "', "
513  + encoding + ")"); //NON-NLS
514  } catch (SQLException ex) {
515  if (ex.getErrorCode() != 0) { // 0 if the entry already exists
516  throw new SQLException(ex);
517  }
518  }
519  }
520  numberingPK = postgreSQLConnection.createStatement();
521  numberingPK.execute("ALTER SEQUENCE tsk_files_path_obj_id_seq RESTART WITH " + (biggestPK + 1)); //NON-NLS
522 
523  // tsk_files
524  biggestPK = 0;
525  inputStatement = sqliteConnection.createStatement();
526  inputResultSet = inputStatement.executeQuery("SELECT * FROM tsk_files"); //NON-NLS
527 
528  while (inputResultSet.next()) {
529  try {
530  long value = inputResultSet.getLong(1);
531  if (value > biggestPK) {
532  biggestPK = value;
533  }
534  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
535  + value + ","
536  + getNullableLong(inputResultSet, 2) + ","
537  + getNullableInt(inputResultSet, 3) + ","
538  + getNullableInt(inputResultSet, 4) + ",'"
539  + SleuthkitCase.escapeSingleQuotes(inputResultSet.getString(5)) + "',"
540  + getNullableLong(inputResultSet, 6) + ","
541  + getNullableLong(inputResultSet, 7) + ","
542  + getNullableInt(inputResultSet, 8) + ","
543  + getNullableInt(inputResultSet, 9) + ","
544  + getNullableInt(inputResultSet, 10) + ","
545  + getNullableInt(inputResultSet, 11) + ","
546  + getNullableInt(inputResultSet, 12) + ","
547  + getNullableInt(inputResultSet, 13) + ","
548  + getNullableInt(inputResultSet, 14) + ","
549  + getNullableLong(inputResultSet, 15) + ","
550  + getNullableLong(inputResultSet, 16) + ","
551  + getNullableLong(inputResultSet, 17) + ","
552  + getNullableLong(inputResultSet, 18) + ","
553  + getNullableLong(inputResultSet, 19) + ","
554  + getNullableInt(inputResultSet, 20) + ","
555  + getNullableInt(inputResultSet, 21) + ","
556  + getNullableInt(inputResultSet, 22) + ","
557  + " ? ,"
558  + getNullableInt(inputResultSet, 24) + ","
559  + " ? )"; //NON-NLS
560 
561  PreparedStatement pst = postgreSQLConnection.prepareStatement(sql);
562  populateNullableString(pst, inputResultSet, 23, 1);
563  populateNullableString(pst, inputResultSet, 25, 2);
564  pst.executeUpdate();
565 
566  } catch (SQLException ex) {
567  if (ex.getErrorCode() != 0) { // 0 if the entry already exists
568  throw new SQLException(ex);
569  }
570  }
571  }
572  numberingPK = postgreSQLConnection.createStatement();
573  numberingPK.execute("ALTER SEQUENCE tsk_files_obj_id_seq RESTART WITH " + (biggestPK + 1)); //NON-NLS
574 
575  // tsk_file_layout, no primary key
576  inputStatement = sqliteConnection.createStatement();
577  inputResultSet = inputStatement.executeQuery("SELECT * FROM tsk_file_layout"); //NON-NLS
578 
579  while (inputResultSet.next()) {
580  outputStatement = postgreSQLConnection.createStatement();
581  try {
582  outputStatement.executeUpdate("INSERT INTO tsk_file_layout (obj_id, byte_start, byte_len, sequence) VALUES (" //NON-NLS
583  + inputResultSet.getLong(1) + ","
584  + inputResultSet.getLong(2) + ","
585  + inputResultSet.getLong(3) + ","
586  + inputResultSet.getInt(4) + ")"); //NON-NLS
587  } catch (SQLException ex) {
588  if (ex.getErrorCode() != 0) { // 0 if the entry already exists
589  throw new SQLException(ex);
590  }
591  }
592  }
593 
594  // tsk_db_info, no primary key
595  inputStatement = sqliteConnection.createStatement();
596  inputResultSet = inputStatement.executeQuery("SELECT * FROM tsk_db_info"); //NON-NLS
597 
598  while (inputResultSet.next()) {
599  outputStatement = postgreSQLConnection.createStatement();
600  try {
601  Statement check = postgreSQLConnection.createStatement();
602  ResultSet checkResult = check.executeQuery("SELECT * FROM tsk_db_info WHERE schema_ver=" + inputResultSet.getInt(1) + " AND tsk_ver=" + inputResultSet.getInt(2)); //NON-NLS
603  if (!checkResult.isBeforeFirst()) { // only insert if it doesn't exist
604  outputStatement.executeUpdate("INSERT INTO tsk_db_info (schema_ver, tsk_ver) VALUES (" //NON-NLS
605  + getNullableInt(inputResultSet, 1) + ","
606  + getNullableInt(inputResultSet, 2) + ")"); //NON-NLS
607  }
608  } catch (SQLException ex) {
609  if (ex.getErrorCode() != 0) { // 0 if the entry already exists
610  throw new SQLException(ex);
611  }
612  }
613  }
614 
615  // tag_names
616  biggestPK = 0;
617  inputStatement = sqliteConnection.createStatement();
618  inputResultSet = inputStatement.executeQuery("SELECT * FROM tag_names"); //NON-NLS
619 
620  while (inputResultSet.next()) {
621  try {
622  long value = inputResultSet.getLong(1);
623  if (value > biggestPK) {
624  biggestPK = value;
625  }
626  String sql = "INSERT INTO tag_names (tag_name_id, display_name, description, color) VALUES (" //NON-NLS
627  + value + ","
628  + " ? ,'"
629  + SleuthkitCase.escapeSingleQuotes(inputResultSet.getString(3)) + "','"
630  + SleuthkitCase.escapeSingleQuotes(inputResultSet.getString(4)) + "')"; //NON-NLS
631 
632  PreparedStatement pst = postgreSQLConnection.prepareStatement(sql);
633  populateNullableString(pst, inputResultSet, 2, 1);
634  pst.executeUpdate();
635 
636  } catch (SQLException ex) {
637  if (ex.getErrorCode() != 0) { // 0 if the entry already exists
638  throw new SQLException(ex);
639  }
640  }
641  }
642  numberingPK = postgreSQLConnection.createStatement();
643  numberingPK.execute("ALTER SEQUENCE tag_names_tag_name_id_seq RESTART WITH " + (biggestPK + 1)); //NON-NLS
644 
645  // reports
646  biggestPK = 0;
647  inputStatement = sqliteConnection.createStatement();
648  inputResultSet = inputStatement.executeQuery("SELECT * FROM reports"); //NON-NLS
649 
650  while (inputResultSet.next()) {
651  outputStatement = postgreSQLConnection.createStatement();
652  try {
653  long value = inputResultSet.getLong(1);
654  if (value > biggestPK) {
655  biggestPK = value;
656  }
657  outputStatement.executeUpdate("INSERT INTO reports (report_id, path, crtime, src_module_name, report_name) VALUES (" //NON-NLS
658  + value + ", '"
659  + SleuthkitCase.escapeSingleQuotes(inputResultSet.getString(2)) + "',"
660  + inputResultSet.getInt(3) + ",'"
661  + SleuthkitCase.escapeSingleQuotes(inputResultSet.getString(4)) + "','"
662  + SleuthkitCase.escapeSingleQuotes(inputResultSet.getString(5)) + "')"); //NON-NLS
663 
664  } catch (SQLException ex) {
665  if (ex.getErrorCode() != 0) { // 0 if the entry already exists
666  throw new SQLException(ex);
667  }
668  }
669  }
670  numberingPK = postgreSQLConnection.createStatement();
671  numberingPK.execute("ALTER SEQUENCE reports_report_id_seq RESTART WITH " + (biggestPK + 1)); //NON-NLS
672 
673  // blackboard_artifacts
674  biggestPK = Long.MIN_VALUE; // This table uses very large negative primary key values, so start at Long.MIN_VALUE
675  inputStatement = sqliteConnection.createStatement();
676  inputResultSet = inputStatement.executeQuery("SELECT * FROM blackboard_artifacts"); //NON-NLS
677 
678  while (inputResultSet.next()) {
679  outputStatement = postgreSQLConnection.createStatement();
680  try {
681  long value = inputResultSet.getLong(1);
682  if (value > biggestPK) {
683  biggestPK = value;
684  }
685  outputStatement.executeUpdate("INSERT INTO blackboard_artifacts (artifact_id, obj_id, artifact_type_id) VALUES (" //NON-NLS
686  + value + ","
687  + inputResultSet.getLong(2) + ","
688  + inputResultSet.getLong(3) + ")"); //NON-NLS
689 
690  } catch (SQLException ex) {
691  if (ex.getErrorCode() != 0) { // 0 if the entry already exists
692  throw new SQLException(ex);
693  }
694  }
695  }
696  numberingPK = postgreSQLConnection.createStatement();
697  numberingPK.execute("ALTER SEQUENCE blackboard_artifacts_artifact_id_seq RESTART WITH " + (biggestPK + 1)); //NON-NLS
698 
699  // blackboard_attributes, no primary key
700  inputStatement = sqliteConnection.createStatement();
701  inputResultSet = inputStatement.executeQuery("SELECT * FROM blackboard_attributes"); //NON-NLS
702 
703  while (inputResultSet.next()) {
704  try {
705  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
706  + inputResultSet.getLong(1) + ","
707  + inputResultSet.getLong(2) + ","
708  + " ? ,"
709  + " ? ,"
710  + inputResultSet.getLong(5) + ","
711  + inputResultSet.getInt(6) + ","
712  + " ? ,"
713  + " ? ,"
714  + getNullableInt(inputResultSet, 9) + ","
715  + getNullableLong(inputResultSet, 10) + ","
716  + " ? )"; //NON-NLS
717  PreparedStatement pst = postgreSQLConnection.prepareStatement(sql);
718  populateNullableString(pst, inputResultSet, 3, 1);
719  populateNullableString(pst, inputResultSet, 4, 2);
720  populateNullableByteArray(pst, inputResultSet, 7, 3);
721  populateNullableString(pst, inputResultSet, 8, 4);
722  populateNullableNumeric(pst, inputResultSet, 11, 5);
723  pst.executeUpdate();
724 
725  } catch (SQLException ex) {
726  if (ex.getErrorCode() != 0) { // 0 if the entry already exists
727  throw new SQLException(ex);
728  }
729  }
730  }
731 
732  // tsk_vs_parts
733  biggestPK = 0;
734  inputStatement = sqliteConnection.createStatement();
735  inputResultSet = inputStatement.executeQuery("SELECT * FROM tsk_vs_parts"); //NON-NLS
736 
737  while (inputResultSet.next()) {
738  try {
739  long value = inputResultSet.getLong(1);
740  if (value > biggestPK) {
741  biggestPK = value;
742  }
743  String sql = "INSERT INTO tsk_vs_parts (obj_id, addr, start, length, descr, flags) VALUES (" //NON-NLS
744  + value + ","
745  + inputResultSet.getLong(2) + ","
746  + inputResultSet.getLong(3) + ","
747  + inputResultSet.getLong(4) + ","
748  + " ? ,"
749  + inputResultSet.getInt(6) + ")"; //NON-NLS
750  PreparedStatement pst = postgreSQLConnection.prepareStatement(sql);
751  populateNullableString(pst, inputResultSet, 5, 1);
752  pst.executeUpdate();
753 
754  } catch (SQLException ex) {
755  if (ex.getErrorCode() != 0) { // 0 if the entry already exists
756  throw new SQLException(ex);
757  }
758  }
759  }
760  numberingPK = postgreSQLConnection.createStatement();
761  numberingPK.execute("ALTER SEQUENCE tsk_vs_parts_obj_id_seq RESTART WITH " + (biggestPK + 1)); //NON-NLS
762 
763  // tsk_vs_info
764  biggestPK = 0;
765  inputStatement = sqliteConnection.createStatement();
766  inputResultSet = inputStatement.executeQuery("SELECT * FROM tsk_vs_info"); //NON-NLS
767 
768  while (inputResultSet.next()) {
769  outputStatement = postgreSQLConnection.createStatement();
770  try {
771  long value = inputResultSet.getLong(1);
772  if (value > biggestPK) {
773  biggestPK = value;
774  }
775  outputStatement.executeUpdate("INSERT INTO tsk_vs_info (obj_id, vs_type, img_offset, block_size) VALUES (" //NON-NLS
776  + value + ","
777  + inputResultSet.getInt(2) + ","
778  + inputResultSet.getLong(3) + ","
779  + inputResultSet.getLong(4) + ")"); //NON-NLS
780 
781  } catch (SQLException ex) {
782  if (ex.getErrorCode() != 0) { // 0 if the entry already exists
783  throw new SQLException(ex);
784  }
785  }
786  }
787  numberingPK = postgreSQLConnection.createStatement();
788  numberingPK.execute("ALTER SEQUENCE tsk_vs_info_obj_id_seq RESTART WITH " + (biggestPK + 1)); //NON-NLS
789 
790  // tsk_files_derived
791  biggestPK = 0;
792  inputStatement = sqliteConnection.createStatement();
793  inputResultSet = inputStatement.executeQuery("SELECT * FROM tsk_files_derived"); //NON-NLS
794 
795  while (inputResultSet.next()) {
796  try {
797  long value = inputResultSet.getLong(1);
798  if (value > biggestPK) {
799  biggestPK = value;
800  }
801  String sql = "INSERT INTO tsk_files_derived (obj_id, derived_id, rederive) VALUES (" //NON-NLS
802  + value + ","
803  + inputResultSet.getLong(2) + ","
804  + " ? )"; //NON-NLS
805  PreparedStatement pst = postgreSQLConnection.prepareStatement(sql);
806  populateNullableString(pst, inputResultSet, 3, 1);
807  pst.executeUpdate();
808 
809  } catch (SQLException ex) {
810  if (ex.getErrorCode() != 0) { // 0 if the entry already exists
811  throw new SQLException(ex);
812  }
813  }
814  }
815  numberingPK = postgreSQLConnection.createStatement();
816  numberingPK.execute("ALTER SEQUENCE tsk_files_derived_obj_id_seq RESTART WITH " + (biggestPK + 1)); //NON-NLS
817 
818  // tsk_files_derived_method
819  biggestPK = 0;
820  inputStatement = sqliteConnection.createStatement();
821  inputResultSet = inputStatement.executeQuery("SELECT * FROM tsk_files_derived_method"); //NON-NLS
822 
823  while (inputResultSet.next()) {
824  try {
825  long value = inputResultSet.getLong(1);
826  if (value > biggestPK) {
827  biggestPK = value;
828  }
829  String sql = "INSERT INTO tsk_files_derived_method (derived_id, tool_name, tool_version, other) VALUES (" //NON-NLS
830  + value + ", '"
831  + inputResultSet.getString(2) + "','"
832  + inputResultSet.getString(3) + "',"
833  + " ? )"; //NON-NLS
834  PreparedStatement pst = postgreSQLConnection.prepareStatement(sql);
835  populateNullableString(pst, inputResultSet, 4, 1);
836  pst.executeUpdate();
837 
838  } catch (SQLException ex) {
839  if (ex.getErrorCode() != 0) { // 0 if the entry already exists
840  throw new SQLException(ex);
841  }
842  }
843  }
844  numberingPK = postgreSQLConnection.createStatement();
845  numberingPK.execute("ALTER SEQUENCE tsk_files_derived_method_derived_id_seq RESTART WITH " + (biggestPK + 1)); //NON-NLS
846 
847  // content_tags
848  biggestPK = 0;
849  inputStatement = sqliteConnection.createStatement();
850  inputResultSet = inputStatement.executeQuery("SELECT * FROM content_tags"); //NON-NLS
851 
852  while (inputResultSet.next()) {
853  outputStatement = postgreSQLConnection.createStatement();
854  try {
855  long value = inputResultSet.getLong(1);
856  if (value > biggestPK) {
857  biggestPK = value;
858  }
859  outputStatement.executeUpdate("INSERT INTO content_tags (tag_id, obj_id, tag_name_id, comment, begin_byte_offset, end_byte_offset) VALUES (" //NON-NLS
860  + value + ","
861  + inputResultSet.getLong(2) + ","
862  + inputResultSet.getLong(3) + ",'"
863  + inputResultSet.getString(4) + "',"
864  + inputResultSet.getLong(5) + ","
865  + inputResultSet.getLong(6) + ")"); //NON-NLS
866 
867  } catch (SQLException ex) {
868  if (ex.getErrorCode() != 0) { // 0 if the entry already exists
869  throw new SQLException(ex);
870  }
871  }
872  }
873  numberingPK = postgreSQLConnection.createStatement();
874  numberingPK.execute("ALTER SEQUENCE content_tags_tag_id_seq RESTART WITH " + (biggestPK + 1)); //NON-NLS
875 
876  // blackboard_artifact_tags
877  biggestPK = 0;
878  inputStatement = sqliteConnection.createStatement();
879  inputResultSet = inputStatement.executeQuery("SELECT * FROM blackboard_artifact_tags"); //NON-NLS
880 
881  while (inputResultSet.next()) {
882  outputStatement = postgreSQLConnection.createStatement();
883  try {
884  long value = inputResultSet.getLong(1);
885  if (value > biggestPK) {
886  biggestPK = value;
887  }
888  outputStatement.executeUpdate("INSERT INTO blackboard_artifact_tags (tag_id, artifact_id, tag_name_id, comment) VALUES (" //NON-NLS
889  + value + ","
890  + inputResultSet.getLong(2) + ","
891  + inputResultSet.getLong(3) + ",'"
892  + inputResultSet.getString(4) + "')"); //NON-NLS
893 
894  } catch (SQLException ex) {
895  if (ex.getErrorCode() != 0) { // 0 if the entry already exists
896  throw new SQLException(ex);
897  }
898  }
899  }
900  numberingPK = postgreSQLConnection.createStatement();
901  numberingPK.execute("ALTER SEQUENCE blackboard_artifact_tags_tag_id_seq RESTART WITH " + (biggestPK + 1)); //NON-NLS
902 
903  sqliteConnection.close();
904  postgreSQLConnection.close();
905  }
906 
918  private static void deconflictDatabaseName(ImportCaseData icd) throws ClassNotFoundException, SQLException, Exception {
919 
920  Connection postgreSQLConnection = getPostgreSQLConnection(icd, POSTGRES_DEFAULT_DB_NAME);
921 
922  int number = 1;
923  boolean unique = false;
924  String sanitizedDbName = icd.getPostgreSQLDbName();
925  if (sanitizedDbName.length() > MAX_DB_NAME_LENGTH) {
926  sanitizedDbName = sanitizedDbName.substring(0, MAX_DB_NAME_LENGTH);
927  }
928 
929  if (postgreSQLConnection != null) {
930  while (unique == false) {
931  Statement st = postgreSQLConnection.createStatement();
932  ResultSet answer = st.executeQuery("SELECT datname FROM pg_catalog.pg_database WHERE LOWER(datname) LIKE LOWER('" + sanitizedDbName + "%')"); //NON-NLS
933 
934  if (!answer.next()) {
935  unique = true;
936  } else {
937  // not unique. add numbers to db name.
938  if (number == Integer.MAX_VALUE) {
939  // oops. it never became unique. give up.
940  throw new Exception(NbBundle.getMessage(SingleUserCaseConverter.class, "SingleUserCaseConverter.NonUniqueDatabaseName")); //NON-NLS
941  }
942  sanitizedDbName = "db_" + Integer.toString(number) + "_" + icd.getPostgreSQLDbName(); //NON-NLS
943 
944  // Chop full db name to 63 characters (max for PostgreSQL)
945  if (sanitizedDbName.length() > MAX_DB_NAME_LENGTH) {
946  sanitizedDbName = sanitizedDbName.substring(0, MAX_DB_NAME_LENGTH);
947  }
948  ++number;
949  }
950  }
951  postgreSQLConnection.close();
952  } else {
953  // Could be caused by database credentials, using user accounts that
954  // can not check if other databases exist, so allow it to continue
955  }
956 
957  icd.setPostgreSQLDbName(sanitizedDbName);
958  }
959 
968  private static void copyImages(ImportCaseData icd) throws Exception {
969  if (icd.getCopySourceImages()) {
970  File imageSource = findInputFolder(icd); // Find the folder for the input images
971  File imageDestination = new File(icd.getImageOutputFolder().toString());
972 
973  // If we can find the input images, copy if needed.
974  if (imageSource.exists()) {
975  FileUtils.copyDirectory(imageSource, imageDestination);
976 
977  } else {
978  throw new Exception(NbBundle.getMessage(SingleUserCaseConverter.class, "SingleUserCaseConverter.UnableToCopySourceImages")); //NON-NLS
979  }
980  }
981  }
982 
992  private static void fixPaths(ImportCaseData icd) throws SQLException, Exception {
994 
995  String input = icd.getImageInputFolder().toString();
996  String output = icd.getImageOutputFolder().toString();
997 
998  Connection postgresqlConnection = getPostgreSQLConnection(icd);
999 
1000  if (postgresqlConnection != null) {
1001  String hostName = NetworkUtils.getLocalHostName();
1002 
1003  // add hostname to reports
1004  Statement updateStatement = postgresqlConnection.createStatement();
1005  updateStatement.executeUpdate("UPDATE reports SET path=CONCAT('" + hostName + "/', path) WHERE path IS NOT NULL AND path != ''"); //NON-NLS
1006 
1007  // add hostname to tsk_files_path
1008  updateStatement = postgresqlConnection.createStatement();
1009  updateStatement.executeUpdate("UPDATE tsk_files_path SET path=CONCAT('" + hostName + "\\', path) WHERE path IS NOT NULL AND path != ''"); //NON-NLS
1010 
1011  String caseName = icd.getRawFolderName().toLowerCase();
1012 
1013  if (icd.getCopySourceImages()) {
1014  // update path for images
1015  Statement inputStatement = postgresqlConnection.createStatement();
1016  ResultSet inputResultSet = inputStatement.executeQuery("SELECT * FROM tsk_image_names"); //NON-NLS
1017 
1018  while (inputResultSet.next()) {
1019  Path oldPath = Paths.get(inputResultSet.getString(2));
1020 
1021  for (int x = 0; x < oldPath.getNameCount(); ++x) {
1022  if (oldPath.getName(x).toString().toLowerCase().equals(caseName)) {
1023  Path newPath = Paths.get(output, oldPath.subpath(x + 1, oldPath.getNameCount()).toString());
1024  updateStatement = postgresqlConnection.createStatement();
1025  updateStatement.executeUpdate("UPDATE tsk_image_names SET name='" + newPath.toString() + "' WHERE obj_id = " + inputResultSet.getInt(1)); //NON-NLS
1026  break;
1027  }
1028  }
1029  }
1030  }
1031  postgresqlConnection.close();
1032  } else {
1033  throw new Exception(NbBundle.getMessage(SingleUserCaseConverter.class, "SingleUserCaseConverter.CanNotOpenDatabase")); //NON-NLS
1034  }
1035  }
1036 
1048  private static String getNullableInt(ResultSet rs, int index) throws SQLException {
1049  int value = rs.getInt(index);
1050  if (rs.wasNull()) {
1051  return "NULL"; //NON-NLS
1052  } else {
1053  return Integer.toString(value);
1054  }
1055  }
1056 
1068  private static String getNullableLong(ResultSet rs, int index) throws SQLException {
1069  long value = rs.getLong(index);
1070  if (rs.wasNull()) {
1071  return "NULL"; //NON-NLS
1072  } else {
1073  return Long.toString(value);
1074  }
1075  }
1076 
1088  private static void populateNullableString(PreparedStatement pst, ResultSet rs, int rsIndex, int psIndex) throws SQLException {
1089  String nullableString = rs.getString(rsIndex);
1090  if (rs.wasNull()) {
1091  pst.setNull(psIndex, java.sql.Types.NULL);
1092  } else {
1093  pst.setString(psIndex, SleuthkitCase.escapeSingleQuotes(nullableString));
1094  }
1095  }
1096 
1108  private static void populateNullableByteArray(PreparedStatement pst, ResultSet rs, int rsIndex, int psIndex) throws SQLException {
1109  byte[] nullableBytes = rs.getBytes(rsIndex);
1110  if (rs.wasNull()) {
1111  pst.setNull(psIndex, java.sql.Types.NULL);
1112  } else {
1113  pst.setBytes(psIndex, nullableBytes);
1114  }
1115  }
1116 
1128  private static void populateNullableNumeric(PreparedStatement pst, ResultSet rs, int rsIndex, int psIndex) throws SQLException {
1129  double nullableNumeric = rs.getDouble(rsIndex);
1130  if (rs.wasNull()) {
1131  pst.setNull(psIndex, java.sql.Types.NULL);
1132  } else {
1133  pst.setDouble(psIndex, nullableNumeric);
1134  }
1135  }
1136 
1146  private static Connection getPostgreSQLConnection(ImportCaseData icd) throws SQLException {
1147  return getPostgreSQLConnection(icd, icd.getPostgreSQLDbName());
1148  }
1149 
1160  private static Connection getPostgreSQLConnection(ImportCaseData icd, String dbName) throws SQLException {
1161  return DriverManager.getConnection("jdbc:postgresql://" //NON-NLS
1162  + icd.getDb().getHost() + ":"
1163  + icd.getDb().getPort() + "/"
1164  + dbName,
1165  icd.getDb().getUserName(),
1166  icd.getDb().getPassword()); //NON-NLS
1167  }
1168 
1178  private static Connection getSQLiteConnection(ImportCaseData icd) throws SQLException {
1179  return DriverManager.getConnection("jdbc:sqlite:" + icd.getCaseInputFolder().resolve(AUTOPSY_DB_FILE).toString(), "", ""); //NON-NLS
1180  }
1181 
1182 }
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-2016 Basis Technology. Generated on: Tue Jun 13 2017
This work is licensed under a Creative Commons Attribution-Share Alike 3.0 United States License.