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