/*
* Autopsy Forensic Browser
*
* Copyright 2015 Basis Technology Corp.
* Contact: carrier <at> sleuthkit <dot> org
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package org.sleuthkit.autopsy.casemodule;
import java.io.File;
import java.io.IOException;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Date;
import org.apache.commons.io.FileUtils;
import org.openide.util.NbBundle;
import org.sleuthkit.autopsy.casemodule.Case.CaseType;
import static org.sleuthkit.autopsy.casemodule.Case.MODULE_FOLDER;
import org.sleuthkit.autopsy.core.UserPreferences;
import org.sleuthkit.autopsy.core.UserPreferencesException;
import org.sleuthkit.datamodel.CaseDbConnectionInfo;
import org.sleuthkit.datamodel.SleuthkitCase;
import org.sleuthkit.autopsy.coreutils.NetworkUtils;
import org.sleuthkit.datamodel.TskData;
/**
* Import a case from single-user to multi-user.
*/
public class SingleUserCaseConverter {
private static final String AUTOPSY_DB_FILE = "autopsy.db"; //NON-NLS
private static final String DOTAUT = CaseMetadata.getFileExtension(); //NON-NLS
private static final String TIMELINE_FOLDER = "Timeline"; //NON-NLS
private static final String TIMELINE_FILE = "events.db"; //NON-NLS
private static final String POSTGRES_DEFAULT_DB_NAME = "postgres"; //NON-NLS
private static final int MAX_DB_NAME_LENGTH = 63;
public class ImportCaseData {
private final Path imageInputFolder;
private final Path caseInputFolder;
private final Path imageOutputFolder;
private final Path caseOutputFolder;
private final String oldCaseName;
private final String newCaseName;
private final boolean copySourceImages;
private final boolean deleteCase;
private String postgreSQLDbName;
private final String autFileName;
private final String rawFolderName;
private final CaseDbConnectionInfo db;
public ImportCaseData(
Path imageInput,
Path caseInput,
Path imageOutput,
Path caseOutput,
String oldCaseName,
String newCaseName,
String autFileName,
String rawFolderName,
boolean copySourceImages,
boolean deleteCase) throws UserPreferencesException {
this.imageInputFolder = imageInput;
this.caseInputFolder = caseInput;
this.imageOutputFolder = imageOutput;
this.caseOutputFolder = caseOutput;
this.oldCaseName = oldCaseName;
this.newCaseName = newCaseName;
this.autFileName = autFileName;
this.rawFolderName = rawFolderName;
this.copySourceImages = copySourceImages;
this.deleteCase = deleteCase;
this.db = UserPreferences.getDatabaseConnectionInfo();
}
public Path getCaseInputFolder() {
return this.caseInputFolder;
}
public Path getCaseOutputFolder() {
return this.caseOutputFolder;
}
Path getImageInputFolder() {
return this.imageInputFolder;
}
Path getImageOutputFolder() {
return this.imageOutputFolder;
}
String getOldCaseName() {
return this.oldCaseName;
}
String getNewCaseName() {
return this.newCaseName;
}
boolean getCopySourceImages() {
return this.copySourceImages;
}
boolean getDeleteCase() {
return this.deleteCase;
}
String getPostgreSQLDbName() {
return this.postgreSQLDbName;
}
String getAutFileName() {
return this.autFileName;
}
String getRawFolderName() {
return this.rawFolderName;
}
CaseDbConnectionInfo getDb() {
return this.db;
}
void setPostgreSQLDbName(String dbName) {
this.postgreSQLDbName = dbName;
}
}
/**
* Handles the heavy lifting for importing a case from single-user to
* multi-user. Creates new .aut file, moves folders to the right place,
* imports the database, and updates paths within the database.
*
* @param icd the Import Case Data for the current case
*
* @throws java.lang.Exception
*/
public static void importCase(ImportCaseData icd) throws Exception {
Class.forName("org.postgresql.Driver"); //NON-NLS
// Make sure there is a SQLite databse file
Path oldDatabasePath = icd.getCaseInputFolder().resolve(AUTOPSY_DB_FILE);
if (false == oldDatabasePath.toFile().exists()) {
throw new Exception(NbBundle.getMessage(SingleUserCaseConverter.class, "SingleUserCaseConverter.BadDatabaseFileName")); //NON-NLS
}
// Read old xml config
CaseMetadata oldCaseMetadata = new CaseMetadata(icd.getCaseInputFolder().resolve(icd.getAutFileName()));
if (oldCaseMetadata.getCaseType() == CaseType.MULTI_USER_CASE) {
throw new Exception(NbBundle.getMessage(SingleUserCaseConverter.class, "SingleUserCaseConverter.AlreadyMultiUser")); //NON-NLS
}
// Create sanitized names for PostgreSQL and Solr
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMdd_HHmmss"); //NON-NLS
Date date = new Date();
String dbName = Case.sanitizeCaseName(icd.getNewCaseName()) + "_" + dateFormat.format(date); //NON-NLS
String solrName = dbName;
icd.setPostgreSQLDbName(dbName);
// Copy items to new hostname folder structure
copyResults(icd);
// Convert from SQLite to PostgreSQL
importDb(icd);
// Update paths inside databse
fixPaths(icd);
// Copy images
copyImages(icd);
// Create new .aut file
CaseMetadata newCaseMetadata = new CaseMetadata(icd.getCaseOutputFolder().toString(),
CaseType.MULTI_USER_CASE,
icd.getNewCaseName(),
oldCaseMetadata.getCaseNumber(),
oldCaseMetadata.getExaminer(),
dbName, solrName);
// Set created date. This calls writefile, no need to call it again
newCaseMetadata.setCreatedDate(oldCaseMetadata.getCreatedDate());
newCaseMetadata.setCreatedByVersion(oldCaseMetadata.getCreatedByVersion());
// At this point the import has been finished successfully so we can delete the original case
// (if requested). This *should* be fairly safe - at this point we know there was an autopsy file
// and database in the given directory so the user shouldn't be able to accidently blow away
// their C drive.
if (icd.getDeleteCase()) {
FileUtils.deleteDirectory(icd.getCaseInputFolder().toFile());
}
}
/**
* Figure out the input folder for images and return it.
*
* @param icd the Import Case Data for the current case
*
* @return the name of the proper Image input folder
*/
private static File findInputFolder(ImportCaseData icd) {
File thePath = icd.getImageInputFolder().resolve(icd.getOldCaseName()).toFile();
if (thePath.isDirectory()) {
/// we've found it
return thePath;
}
thePath = icd.getImageInputFolder().resolve(icd.getRawFolderName()).toFile();
if (thePath.isDirectory()) {
/// we've found it
return thePath;
}
return icd.getImageInputFolder().toFile();
}
/**
* Copy all folders at the base level to the new scheme involving hostname.
* Also take care of a few files such as logs, timeline database, etc.
*
* @param icd the Import Case Data for the current case
*
* @throws IOException
*/
private static void copyResults(ImportCaseData icd) throws IOException {
/// get hostname
String hostName = NetworkUtils.getLocalHostName();
Path destination;
Path source = icd.getCaseInputFolder();
if (source.toFile().exists()) {
destination = icd.getCaseOutputFolder().resolve(hostName);
FileUtils.copyDirectory(source.toFile(), destination.toFile());
}
source = icd.getCaseInputFolder().resolve(TIMELINE_FILE);
if (source.toFile().exists()) {
destination = Paths.get(icd.getCaseOutputFolder().toString(), hostName, MODULE_FOLDER, TIMELINE_FOLDER, TIMELINE_FILE);
FileUtils.copyFile(source.toFile(), destination.toFile());
}
// Remove the single-user .aut file from the multi-user folder
File oldAutopsyFile = Paths.get(icd.getCaseOutputFolder().toString(), hostName, icd.getOldCaseName() + DOTAUT).toFile();
if (oldAutopsyFile.exists()) {
oldAutopsyFile.delete();
}
// Remove the single-user database file from the multi-user folder
File oldDatabaseFile = Paths.get(icd.getCaseOutputFolder().toString(), hostName, AUTOPSY_DB_FILE).toFile();
if (oldDatabaseFile.exists()) {
oldDatabaseFile.delete();
}
// Remove the single-user Timeline file from the multi-user folder
File oldTimelineFile = Paths.get(icd.getCaseOutputFolder().toString(), hostName, TIMELINE_FILE).toFile();
if (oldTimelineFile.exists()) {
oldTimelineFile.delete();
}
}
/**
* Import the database from SQLite to PostgreSQL. Do not change any of the
* data while loading it over. Fixing paths is done once the database is
* completely imported.
*
* @param icd the Import Case Data for the current case
*
* @throws Exception
* @throws SQLException
* @throws ClassNotFoundException
*/
private static void importDb(ImportCaseData icd) throws SQLException, ClassNotFoundException, Exception {
// deconflict the database name
deconflictDatabaseName(icd);
// Create a new database via SleuthkitCase
SleuthkitCase newCase = SleuthkitCase.newCase(icd.getPostgreSQLDbName(), icd.getDb(), icd.getCaseOutputFolder().toString());
newCase.close();
/// Migrate from SQLite to PostgreSQL
Class.forName("org.sqlite.JDBC"); //NON-NLS
Connection sqliteConnection = getSQLiteConnection(icd);
Connection postgreSQLConnection = getPostgreSQLConnection(icd);
// blackboard_artifact_types
Statement inputStatement = sqliteConnection.createStatement();
ResultSet inputResultSet = inputStatement.executeQuery("SELECT * FROM blackboard_artifact_types"); //NON-NLS
Statement outputStatement;
Statement numberingPK;
long biggestPK = 0;
while (inputResultSet.next()) {
try {
long value = inputResultSet.getLong(1);
if (value > biggestPK) {
biggestPK = value;
}
Statement check = postgreSQLConnection.createStatement();
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
if (!checkResult.isBeforeFirst()) { // only insert if it doesn't exist
String sql = "INSERT INTO blackboard_artifact_types (artifact_type_id, type_name, display_name) VALUES (" //NON-NLS
+ value + ", '"
+ SleuthkitCase.escapeSingleQuotes(inputResultSet.getString(2)) + "',"
+ " ? )"; //NON-NLS
PreparedStatement pst = postgreSQLConnection.prepareStatement(sql);
populateNullableString(pst, inputResultSet, 3, 1);
pst.executeUpdate();
}
} catch (SQLException ex) {
if (ex.getErrorCode() != 0) { // 0 if the entry already exists
throw new SQLException(ex);
}
}
}
numberingPK = postgreSQLConnection.createStatement();
numberingPK.execute("ALTER SEQUENCE blackboard_artifact_types_artifact_type_id_seq RESTART WITH " + (biggestPK + 1)); //NON-NLS
// blackboard_attribute_types
biggestPK = 0;
inputStatement = sqliteConnection.createStatement();
inputResultSet = inputStatement.executeQuery("SELECT * FROM blackboard_attribute_types"); //NON-NLS
while (inputResultSet.next()) {
try {
long value = inputResultSet.getLong(1);
if (value > biggestPK) {
biggestPK = value;
}
Statement check = postgreSQLConnection.createStatement();
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
if (!checkResult.isBeforeFirst()) { // only insert if it doesn't exist
String sql = "INSERT INTO blackboard_attribute_types (attribute_type_id, type_name, display_name) VALUES (" //NON-NLS
+ value + ", '"
+ SleuthkitCase.escapeSingleQuotes(inputResultSet.getString(2)) + "',"
+ " ? )"; //NON-NLS
PreparedStatement pst = postgreSQLConnection.prepareStatement(sql);
populateNullableString(pst, inputResultSet, 3, 1);
pst.executeUpdate();
}
} catch (SQLException ex) {
if (ex.getErrorCode() != 0) { // 0 if the entry already exists
throw new SQLException(ex);
}
}
}
numberingPK = postgreSQLConnection.createStatement();
numberingPK.execute("ALTER SEQUENCE blackboard_attribute_types_attribute_type_id_seq RESTART WITH " + (biggestPK + 1)); //NON-NLS
// tsk_objects
biggestPK = 0;
inputStatement = sqliteConnection.createStatement();
inputResultSet = inputStatement.executeQuery("SELECT * FROM tsk_objects"); //NON-NLS
while (inputResultSet.next()) {
outputStatement = postgreSQLConnection.createStatement();
try {
long value = inputResultSet.getLong(1);
if (value > biggestPK) {
biggestPK = value;
}
outputStatement.executeUpdate("INSERT INTO tsk_objects (obj_id, par_obj_id, type) VALUES (" //NON-NLS
+ value + ","
+ getNullableLong(inputResultSet, 2) + ","
+ inputResultSet.getInt(3) + ")"); //NON-NLS
} catch (SQLException ex) {
if (ex.getErrorCode() != 0) { // 0 if the entry already exists
throw new SQLException(ex);
}
}
}
numberingPK = postgreSQLConnection.createStatement();
numberingPK.execute("ALTER SEQUENCE tsk_objects_obj_id_seq RESTART WITH " + (biggestPK + 1)); //NON-NLS
// tsk_image_names, no primary key
inputStatement = sqliteConnection.createStatement();
inputResultSet = inputStatement.executeQuery("SELECT * FROM tsk_image_names"); //NON-NLS
while (inputResultSet.next()) {
outputStatement = postgreSQLConnection.createStatement();
try {
outputStatement.executeUpdate("INSERT INTO tsk_image_names (obj_id, name, sequence) VALUES (" //NON-NLS
+ inputResultSet.getLong(1) + ",'"
+ inputResultSet.getString(2) + "',"
+ inputResultSet.getInt(3) + ")"); //NON-NLS
} catch (SQLException ex) {
if (ex.getErrorCode() != 0) { // 0 if the entry already exists
throw new SQLException(ex);
}
}
}
// tsk_image_info
biggestPK = 0;
inputStatement = sqliteConnection.createStatement();
inputResultSet = inputStatement.executeQuery("SELECT * FROM tsk_image_info"); //NON-NLS
while (inputResultSet.next()) {
try {
long value = inputResultSet.getLong(1);
if (value > biggestPK) {
biggestPK = value;
}
String sql = "INSERT INTO tsk_image_info (obj_id, type, ssize, tzone, size, md5, display_name) VALUES (" //NON-NLS
+ value + ","
+ getNullableInt(inputResultSet, 2) + ","
+ getNullableInt(inputResultSet, 3) + ","
+ " ? ,"
+ getNullableLong(inputResultSet, 5) + ","
+ " ? ,"
+ " ? )"; //NON-NLS
PreparedStatement pst = postgreSQLConnection.prepareStatement(sql);
populateNullableString(pst, inputResultSet, 4, 1);
populateNullableString(pst, inputResultSet, 6, 2);
populateNullableString(pst, inputResultSet, 7, 3);
pst.executeUpdate();
} catch (SQLException ex) {
if (ex.getErrorCode() != 0) { // 0 if the entry already exists
throw new SQLException(ex);
}
}
}
numberingPK = postgreSQLConnection.createStatement();
numberingPK.execute("ALTER SEQUENCE tsk_image_info_obj_id_seq RESTART WITH " + (biggestPK + 1)); //NON-NLS
// tsk_fs_info
biggestPK = 0;
inputStatement = sqliteConnection.createStatement();
inputResultSet = inputStatement.executeQuery("SELECT * FROM tsk_fs_info"); //NON-NLS
while (inputResultSet.next()) {
try {
long value = inputResultSet.getLong(1);
if (value > biggestPK) {
biggestPK = value;
}
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
+ value + ","
+ inputResultSet.getLong(2) + ","
+ inputResultSet.getInt(3) + ","
+ inputResultSet.getLong(4) + ","
+ inputResultSet.getLong(5) + ","
+ inputResultSet.getLong(6) + ","
+ inputResultSet.getLong(7) + ","
+ inputResultSet.getLong(8) + ","
+ " ? )"; //NON-NLS
PreparedStatement pst = postgreSQLConnection.prepareStatement(sql);
populateNullableString(pst, inputResultSet, 9, 1);
pst.executeUpdate();
} catch (SQLException ex) {
if (ex.getErrorCode() != 0) { // 0 if the entry already exists
throw new SQLException(ex);
}
}
}
numberingPK = postgreSQLConnection.createStatement();
numberingPK.execute("ALTER SEQUENCE tsk_fs_info_obj_id_seq RESTART WITH " + (biggestPK + 1)); //NON-NLS
// tsk_files_path
biggestPK = 0;
inputStatement = sqliteConnection.createStatement();
inputResultSet = inputStatement.executeQuery("SELECT * FROM tsk_files_path"); //NON-NLS
while (inputResultSet.next()) {
outputStatement = postgreSQLConnection.createStatement();
try {
long value = inputResultSet.getLong(1);
if (value > biggestPK) {
biggestPK = value;
}
// If the entry contains an encoding type, copy it. Otherwise use NONE.
// The test on column count can be removed if we upgrade the database before conversion.
int encoding = TskData.EncodingType.NONE.getType();
ResultSetMetaData rsMetaData = inputResultSet.getMetaData();
if(rsMetaData.getColumnCount() == 3){
encoding = inputResultSet.getInt(3);
}
outputStatement.executeUpdate("INSERT INTO tsk_files_path (obj_id, path, encoding_type) VALUES (" //NON-NLS
+ value + ", '"
+ SleuthkitCase.escapeSingleQuotes(inputResultSet.getString(2)) + "', "
+ encoding + ")"); //NON-NLS
} catch (SQLException ex) {
if (ex.getErrorCode() != 0) { // 0 if the entry already exists
throw new SQLException(ex);
}
}
}
numberingPK = postgreSQLConnection.createStatement();
numberingPK.execute("ALTER SEQUENCE tsk_files_path_obj_id_seq RESTART WITH " + (biggestPK + 1)); //NON-NLS
// tsk_files
biggestPK = 0;
inputStatement = sqliteConnection.createStatement();
inputResultSet = inputStatement.executeQuery("SELECT * FROM tsk_files"); //NON-NLS
while (inputResultSet.next()) {
try {
long value = inputResultSet.getLong(1);
if (value > biggestPK) {
biggestPK = value;
}
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
+ value + ","
+ getNullableLong(inputResultSet, 2) + ","
+ getNullableInt(inputResultSet, 3) + ","
+ getNullableInt(inputResultSet, 4) + ",'"
+ SleuthkitCase.escapeSingleQuotes(inputResultSet.getString(5)) + "',"
+ getNullableLong(inputResultSet, 6) + ","
+ getNullableLong(inputResultSet, 7) + ","
+ getNullableInt(inputResultSet, 8) + ","
+ getNullableInt(inputResultSet, 9) + ","
+ getNullableInt(inputResultSet, 10) + ","
+ getNullableInt(inputResultSet, 11) + ","
+ getNullableInt(inputResultSet, 12) + ","
+ getNullableInt(inputResultSet, 13) + ","
+ getNullableInt(inputResultSet, 14) + ","
+ getNullableLong(inputResultSet, 15) + ","
+ getNullableLong(inputResultSet, 16) + ","
+ getNullableLong(inputResultSet, 17) + ","
+ getNullableLong(inputResultSet, 18) + ","
+ getNullableLong(inputResultSet, 19) + ","
+ getNullableInt(inputResultSet, 20) + ","
+ getNullableInt(inputResultSet, 21) + ","
+ getNullableInt(inputResultSet, 22) + ","
+ " ? ,"
+ getNullableInt(inputResultSet, 24) + ","
+ " ? )"; //NON-NLS
PreparedStatement pst = postgreSQLConnection.prepareStatement(sql);
populateNullableString(pst, inputResultSet, 23, 1);
populateNullableString(pst, inputResultSet, 25, 2);
pst.executeUpdate();
} catch (SQLException ex) {
if (ex.getErrorCode() != 0) { // 0 if the entry already exists
throw new SQLException(ex);
}
}
}
numberingPK = postgreSQLConnection.createStatement();
numberingPK.execute("ALTER SEQUENCE tsk_files_obj_id_seq RESTART WITH " + (biggestPK + 1)); //NON-NLS
// tsk_file_layout, no primary key
inputStatement = sqliteConnection.createStatement();
inputResultSet = inputStatement.executeQuery("SELECT * FROM tsk_file_layout"); //NON-NLS
while (inputResultSet.next()) {
outputStatement = postgreSQLConnection.createStatement();
try {
outputStatement.executeUpdate("INSERT INTO tsk_file_layout (obj_id, byte_start, byte_len, sequence) VALUES (" //NON-NLS
+ inputResultSet.getLong(1) + ","
+ inputResultSet.getLong(2) + ","
+ inputResultSet.getLong(3) + ","
+ inputResultSet.getInt(4) + ")"); //NON-NLS
} catch (SQLException ex) {
if (ex.getErrorCode() != 0) { // 0 if the entry already exists
throw new SQLException(ex);
}
}
}
// tsk_db_info, no primary key
inputStatement = sqliteConnection.createStatement();
inputResultSet = inputStatement.executeQuery("SELECT * FROM tsk_db_info"); //NON-NLS
while (inputResultSet.next()) {
outputStatement = postgreSQLConnection.createStatement();
try {
Statement check = postgreSQLConnection.createStatement();
ResultSet checkResult = check.executeQuery("SELECT * FROM tsk_db_info WHERE schema_ver=" + inputResultSet.getInt(1) + " AND tsk_ver=" + inputResultSet.getInt(2)); //NON-NLS
if (!checkResult.isBeforeFirst()) { // only insert if it doesn't exist
outputStatement.executeUpdate("INSERT INTO tsk_db_info (schema_ver, tsk_ver) VALUES (" //NON-NLS
+ getNullableInt(inputResultSet, 1) + ","
+ getNullableInt(inputResultSet, 2) + ")"); //NON-NLS
}
} catch (SQLException ex) {
if (ex.getErrorCode() != 0) { // 0 if the entry already exists
throw new SQLException(ex);
}
}
}
// tag_names
biggestPK = 0;
inputStatement = sqliteConnection.createStatement();
inputResultSet = inputStatement.executeQuery("SELECT * FROM tag_names"); //NON-NLS
while (inputResultSet.next()) {
try {
long value = inputResultSet.getLong(1);
if (value > biggestPK) {
biggestPK = value;
}
String sql = "INSERT INTO tag_names (tag_name_id, display_name, description, color) VALUES (" //NON-NLS
+ value + ","
+ " ? ,'"
+ SleuthkitCase.escapeSingleQuotes(inputResultSet.getString(3)) + "','"
+ SleuthkitCase.escapeSingleQuotes(inputResultSet.getString(4)) + "')"; //NON-NLS
PreparedStatement pst = postgreSQLConnection.prepareStatement(sql);
populateNullableString(pst, inputResultSet, 2, 1);
pst.executeUpdate();
} catch (SQLException ex) {
if (ex.getErrorCode() != 0) { // 0 if the entry already exists
throw new SQLException(ex);
}
}
}
numberingPK = postgreSQLConnection.createStatement();
numberingPK.execute("ALTER SEQUENCE tag_names_tag_name_id_seq RESTART WITH " + (biggestPK + 1)); //NON-NLS
// reports
biggestPK = 0;
inputStatement = sqliteConnection.createStatement();
inputResultSet = inputStatement.executeQuery("SELECT * FROM reports"); //NON-NLS
while (inputResultSet.next()) {
outputStatement = postgreSQLConnection.createStatement();
try {
long value = inputResultSet.getLong(1);
if (value > biggestPK) {
biggestPK = value;
}
outputStatement.executeUpdate("INSERT INTO reports (report_id, path, crtime, src_module_name, report_name) VALUES (" //NON-NLS
+ value + ", '"
+ SleuthkitCase.escapeSingleQuotes(inputResultSet.getString(2)) + "',"
+ inputResultSet.getInt(3) + ",'"
+ SleuthkitCase.escapeSingleQuotes(inputResultSet.getString(4)) + "','"
+ SleuthkitCase.escapeSingleQuotes(inputResultSet.getString(5)) + "')"); //NON-NLS
} catch (SQLException ex) {
if (ex.getErrorCode() != 0) { // 0 if the entry already exists
throw new SQLException(ex);
}
}
}
numberingPK = postgreSQLConnection.createStatement();
numberingPK.execute("ALTER SEQUENCE reports_report_id_seq RESTART WITH " + (biggestPK + 1)); //NON-NLS
// blackboard_artifacts
biggestPK = Long.MIN_VALUE; // This table uses very large negative primary key values, so start at Long.MIN_VALUE
inputStatement = sqliteConnection.createStatement();
inputResultSet = inputStatement.executeQuery("SELECT * FROM blackboard_artifacts"); //NON-NLS
while (inputResultSet.next()) {
outputStatement = postgreSQLConnection.createStatement();
try {
long value = inputResultSet.getLong(1);
if (value > biggestPK) {
biggestPK = value;
}
outputStatement.executeUpdate("INSERT INTO blackboard_artifacts (artifact_id, obj_id, artifact_type_id) VALUES (" //NON-NLS
+ value + ","
+ inputResultSet.getLong(2) + ","
+ inputResultSet.getLong(3) + ")"); //NON-NLS
} catch (SQLException ex) {
if (ex.getErrorCode() != 0) { // 0 if the entry already exists
throw new SQLException(ex);
}
}
}
numberingPK = postgreSQLConnection.createStatement();
numberingPK.execute("ALTER SEQUENCE blackboard_artifacts_artifact_id_seq RESTART WITH " + (biggestPK + 1)); //NON-NLS
// blackboard_attributes, no primary key
inputStatement = sqliteConnection.createStatement();
inputResultSet = inputStatement.executeQuery("SELECT * FROM blackboard_attributes"); //NON-NLS
while (inputResultSet.next()) {
try {
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
+ inputResultSet.getLong(1) + ","
+ inputResultSet.getLong(2) + ","
+ " ? ,"
+ " ? ,"
+ inputResultSet.getLong(5) + ","
+ inputResultSet.getInt(6) + ","
+ " ? ,"
+ " ? ,"
+ getNullableInt(inputResultSet, 9) + ","
+ getNullableLong(inputResultSet, 10) + ","
+ " ? )"; //NON-NLS
PreparedStatement pst = postgreSQLConnection.prepareStatement(sql);
populateNullableString(pst, inputResultSet, 3, 1);
populateNullableString(pst, inputResultSet, 4, 2);
populateNullableByteArray(pst, inputResultSet, 7, 3);
populateNullableString(pst, inputResultSet, 8, 4);
populateNullableNumeric(pst, inputResultSet, 11, 5);
pst.executeUpdate();
} catch (SQLException ex) {
if (ex.getErrorCode() != 0) { // 0 if the entry already exists
throw new SQLException(ex);
}
}
}
// tsk_vs_parts
biggestPK = 0;
inputStatement = sqliteConnection.createStatement();
inputResultSet = inputStatement.executeQuery("SELECT * FROM tsk_vs_parts"); //NON-NLS
while (inputResultSet.next()) {
try {
long value = inputResultSet.getLong(1);
if (value > biggestPK) {
biggestPK = value;
}
String sql = "INSERT INTO tsk_vs_parts (obj_id, addr, start, length, descr, flags) VALUES (" //NON-NLS
+ value + ","
+ inputResultSet.getLong(2) + ","
+ inputResultSet.getLong(3) + ","
+ inputResultSet.getLong(4) + ","
+ " ? ,"
+ inputResultSet.getInt(6) + ")"; //NON-NLS
PreparedStatement pst = postgreSQLConnection.prepareStatement(sql);
populateNullableString(pst, inputResultSet, 5, 1);
pst.executeUpdate();
} catch (SQLException ex) {
if (ex.getErrorCode() != 0) { // 0 if the entry already exists
throw new SQLException(ex);
}
}
}
numberingPK = postgreSQLConnection.createStatement();
numberingPK.execute("ALTER SEQUENCE tsk_vs_parts_obj_id_seq RESTART WITH " + (biggestPK + 1)); //NON-NLS
// tsk_vs_info
biggestPK = 0;
inputStatement = sqliteConnection.createStatement();
inputResultSet = inputStatement.executeQuery("SELECT * FROM tsk_vs_info"); //NON-NLS
while (inputResultSet.next()) {
outputStatement = postgreSQLConnection.createStatement();
try {
long value = inputResultSet.getLong(1);
if (value > biggestPK) {
biggestPK = value;
}
outputStatement.executeUpdate("INSERT INTO tsk_vs_info (obj_id, vs_type, img_offset, block_size) VALUES (" //NON-NLS
+ value + ","
+ inputResultSet.getInt(2) + ","
+ inputResultSet.getLong(3) + ","
+ inputResultSet.getLong(4) + ")"); //NON-NLS
} catch (SQLException ex) {
if (ex.getErrorCode() != 0) { // 0 if the entry already exists
throw new SQLException(ex);
}
}
}
numberingPK = postgreSQLConnection.createStatement();
numberingPK.execute("ALTER SEQUENCE tsk_vs_info_obj_id_seq RESTART WITH " + (biggestPK + 1)); //NON-NLS
// tsk_files_derived
biggestPK = 0;
inputStatement = sqliteConnection.createStatement();
inputResultSet = inputStatement.executeQuery("SELECT * FROM tsk_files_derived"); //NON-NLS
while (inputResultSet.next()) {
try {
long value = inputResultSet.getLong(1);
if (value > biggestPK) {
biggestPK = value;
}
String sql = "INSERT INTO tsk_files_derived (obj_id, derived_id, rederive) VALUES (" //NON-NLS
+ value + ","
+ inputResultSet.getLong(2) + ","
+ " ? )"; //NON-NLS
PreparedStatement pst = postgreSQLConnection.prepareStatement(sql);
populateNullableString(pst, inputResultSet, 3, 1);
pst.executeUpdate();
} catch (SQLException ex) {
if (ex.getErrorCode() != 0) { // 0 if the entry already exists
throw new SQLException(ex);
}
}
}
numberingPK = postgreSQLConnection.createStatement();
numberingPK.execute("ALTER SEQUENCE tsk_files_derived_obj_id_seq RESTART WITH " + (biggestPK + 1)); //NON-NLS
// tsk_files_derived_method
biggestPK = 0;
inputStatement = sqliteConnection.createStatement();
inputResultSet = inputStatement.executeQuery("SELECT * FROM tsk_files_derived_method"); //NON-NLS
while (inputResultSet.next()) {
try {
long value = inputResultSet.getLong(1);
if (value > biggestPK) {
biggestPK = value;
}
String sql = "INSERT INTO tsk_files_derived_method (derived_id, tool_name, tool_version, other) VALUES (" //NON-NLS
+ value + ", '"
+ inputResultSet.getString(2) + "','"
+ inputResultSet.getString(3) + "',"
+ " ? )"; //NON-NLS
PreparedStatement pst = postgreSQLConnection.prepareStatement(sql);
populateNullableString(pst, inputResultSet, 4, 1);
pst.executeUpdate();
} catch (SQLException ex) {
if (ex.getErrorCode() != 0) { // 0 if the entry already exists
throw new SQLException(ex);
}
}
}
numberingPK = postgreSQLConnection.createStatement();
numberingPK.execute("ALTER SEQUENCE tsk_files_derived_method_derived_id_seq RESTART WITH " + (biggestPK + 1)); //NON-NLS
// content_tags
biggestPK = 0;
inputStatement = sqliteConnection.createStatement();
inputResultSet = inputStatement.executeQuery("SELECT * FROM content_tags"); //NON-NLS
while (inputResultSet.next()) {
outputStatement = postgreSQLConnection.createStatement();
try {
long value = inputResultSet.getLong(1);
if (value > biggestPK) {
biggestPK = value;
}
outputStatement.executeUpdate("INSERT INTO content_tags (tag_id, obj_id, tag_name_id, comment, begin_byte_offset, end_byte_offset) VALUES (" //NON-NLS
+ value + ","
+ inputResultSet.getLong(2) + ","
+ inputResultSet.getLong(3) + ",'"
+ inputResultSet.getString(4) + "',"
+ inputResultSet.getLong(5) + ","
+ inputResultSet.getLong(6) + ")"); //NON-NLS
} catch (SQLException ex) {
if (ex.getErrorCode() != 0) { // 0 if the entry already exists
throw new SQLException(ex);
}
}
}
numberingPK = postgreSQLConnection.createStatement();
numberingPK.execute("ALTER SEQUENCE content_tags_tag_id_seq RESTART WITH " + (biggestPK + 1)); //NON-NLS
// blackboard_artifact_tags
biggestPK = 0;
inputStatement = sqliteConnection.createStatement();
inputResultSet = inputStatement.executeQuery("SELECT * FROM blackboard_artifact_tags"); //NON-NLS
while (inputResultSet.next()) {
outputStatement = postgreSQLConnection.createStatement();
try {
long value = inputResultSet.getLong(1);
if (value > biggestPK) {
biggestPK = value;
}
outputStatement.executeUpdate("INSERT INTO blackboard_artifact_tags (tag_id, artifact_id, tag_name_id, comment) VALUES (" //NON-NLS
+ value + ","
+ inputResultSet.getLong(2) + ","
+ inputResultSet.getLong(3) + ",'"
+ inputResultSet.getString(4) + "')"); //NON-NLS
} catch (SQLException ex) {
if (ex.getErrorCode() != 0) { // 0 if the entry already exists
throw new SQLException(ex);
}
}
}
numberingPK = postgreSQLConnection.createStatement();
numberingPK.execute("ALTER SEQUENCE blackboard_artifact_tags_tag_id_seq RESTART WITH " + (biggestPK + 1)); //NON-NLS
sqliteConnection.close();
postgreSQLConnection.close();
}
/**
* Checks that the database name is unique. If it is not, attempts to add
* numbers to it until it is unique. Gives up if it goes through all
* positive integers without finding a unique name.
*
* @param icd the Import Case Data for the current case
*
* @throws Exception
* @throws SQLException
* @throws ClassNotFoundException
*/
private static void deconflictDatabaseName(ImportCaseData icd) throws ClassNotFoundException, SQLException, Exception {
Connection postgreSQLConnection = getPostgreSQLConnection(icd, POSTGRES_DEFAULT_DB_NAME);
int number = 1;
boolean unique = false;
String sanitizedDbName = icd.getPostgreSQLDbName();
if (sanitizedDbName.length() > MAX_DB_NAME_LENGTH) {
sanitizedDbName = sanitizedDbName.substring(0, MAX_DB_NAME_LENGTH);
}
if (postgreSQLConnection != null) {
while (unique == false) {
Statement st = postgreSQLConnection.createStatement();
ResultSet answer = st.executeQuery("SELECT datname FROM pg_catalog.pg_database WHERE LOWER(datname) LIKE LOWER('" + sanitizedDbName + "%')"); //NON-NLS
if (!answer.next()) {
unique = true;
} else {
// not unique. add numbers to db name.
if (number == Integer.MAX_VALUE) {
// oops. it never became unique. give up.
throw new Exception(NbBundle.getMessage(SingleUserCaseConverter.class, "SingleUserCaseConverter.NonUniqueDatabaseName")); //NON-NLS
}
sanitizedDbName = "db_" + Integer.toString(number) + "_" + icd.getPostgreSQLDbName(); //NON-NLS
// Chop full db name to 63 characters (max for PostgreSQL)
if (sanitizedDbName.length() > MAX_DB_NAME_LENGTH) {
sanitizedDbName = sanitizedDbName.substring(0, MAX_DB_NAME_LENGTH);
}
++number;
}
}
postgreSQLConnection.close();
} else {
// Could be caused by database credentials, using user accounts that
// can not check if other databases exist, so allow it to continue
}
icd.setPostgreSQLDbName(sanitizedDbName);
}
/**
* Get the images from the old case and stage them for the new case, if the
* user chose to copy images over.
*
* @param icd the Import Case Data for the current case
*
* @throws IOException
*/
private static void copyImages(ImportCaseData icd) throws Exception {
if (icd.getCopySourceImages()) {
File imageSource = findInputFolder(icd); // Find the folder for the input images
File imageDestination = new File(icd.getImageOutputFolder().toString());
// If we can find the input images, copy if needed.
if (imageSource.exists()) {
FileUtils.copyDirectory(imageSource, imageDestination);
} else {
throw new Exception(NbBundle.getMessage(SingleUserCaseConverter.class, "SingleUserCaseConverter.UnableToCopySourceImages")); //NON-NLS
}
}
}
/**
* Fix up any paths in the database that refer to items that have moved.
* Candidates include events.db, input images, reports, file paths, etc.
*
* @param icd the Import Case Data for the current case
*
* @throws Exception
* @throws SQLExceptionException
*/
private static void fixPaths(ImportCaseData icd) throws SQLException, Exception {
/// Fix paths in reports, tsk_files_path, and tsk_image_names tables
String input = icd.getImageInputFolder().toString();
String output = icd.getImageOutputFolder().toString();
Connection postgresqlConnection = getPostgreSQLConnection(icd);
if (postgresqlConnection != null) {
String hostName = NetworkUtils.getLocalHostName();
// add hostname to reports
Statement updateStatement = postgresqlConnection.createStatement();
updateStatement.executeUpdate("UPDATE reports SET path=CONCAT('" + hostName + "/', path) WHERE path IS NOT NULL AND path != ''"); //NON-NLS
// add hostname to tsk_files_path
updateStatement = postgresqlConnection.createStatement();
updateStatement.executeUpdate("UPDATE tsk_files_path SET path=CONCAT('" + hostName + "\\', path) WHERE path IS NOT NULL AND path != ''"); //NON-NLS
String caseName = icd.getRawFolderName().toLowerCase();
if (icd.getCopySourceImages()) {
// update path for images
Statement inputStatement = postgresqlConnection.createStatement();
ResultSet inputResultSet = inputStatement.executeQuery("SELECT * FROM tsk_image_names"); //NON-NLS
while (inputResultSet.next()) {
Path oldPath = Paths.get(inputResultSet.getString(2));
for (int x = 0; x < oldPath.getNameCount(); ++x) {
if (oldPath.getName(x).toString().toLowerCase().equals(caseName)) {
Path newPath = Paths.get(output, oldPath.subpath(x + 1, oldPath.getNameCount()).toString());
updateStatement = postgresqlConnection.createStatement();
updateStatement.executeUpdate("UPDATE tsk_image_names SET name='" + newPath.toString() + "' WHERE obj_id = " + inputResultSet.getInt(1)); //NON-NLS
break;
}
}
}
}
postgresqlConnection.close();
} else {
throw new Exception(NbBundle.getMessage(SingleUserCaseConverter.class, "SingleUserCaseConverter.CanNotOpenDatabase")); //NON-NLS
}
}
/**
* Return an integer from the ResultSet converted to String or NULL, by
* checking ResultSet.wasNull()
*
* @param rs the ResultSet to work with
* @param index the index into the ResultSet to work with
*
* @return the proper value, the integer, or NULL
*
* @throws SQLException
*/
private static String getNullableInt(ResultSet rs, int index) throws SQLException {
int value = rs.getInt(index);
if (rs.wasNull()) {
return "NULL"; //NON-NLS
} else {
return Integer.toString(value);
}
}
/**
* Return a long from the ResultSet converted to String or NULL, by checking
* ResultSet.wasNull()
*
* @param rs the ResultSet to work with
* @param index the index into the ResultSet to work with
*
* @return the proper value, the long, or NULL
*
* @throws SQLException
*/
private static String getNullableLong(ResultSet rs, int index) throws SQLException {
long value = rs.getLong(index);
if (rs.wasNull()) {
return "NULL"; //NON-NLS
} else {
return Long.toString(value);
}
}
/**
* Place a NULL inside a prepared statement if needed, otherwise, place the
* String that was in the ResultSet.
*
* @param pst the prepared statement
* @param rs the ResultSet to work with
* @param rsIndex index for the result set
* @param psIndex index for the prepared statement
*
* @throws SQLException
*/
private static void populateNullableString(PreparedStatement pst, ResultSet rs, int rsIndex, int psIndex) throws SQLException {
String nullableString = rs.getString(rsIndex);
if (rs.wasNull()) {
pst.setNull(psIndex, java.sql.Types.NULL);
} else {
pst.setString(psIndex, SleuthkitCase.escapeSingleQuotes(nullableString));
}
}
/**
* Place a NULL inside a prepared statement if needed, otherwise, place the
* byte array that was in the ResultSet.
*
* @param pst the prepared statement
* @param rs the ResultSet to work with
* @param rsIndex index for the result set
* @param psIndex index for the prepared statement
*
* @throws SQLException
*/
private static void populateNullableByteArray(PreparedStatement pst, ResultSet rs, int rsIndex, int psIndex) throws SQLException {
byte[] nullableBytes = rs.getBytes(rsIndex);
if (rs.wasNull()) {
pst.setNull(psIndex, java.sql.Types.NULL);
} else {
pst.setBytes(psIndex, nullableBytes);
}
}
/**
* Place a NULL inside a prepared statement if needed, otherwise, place the
* double that was in the ResultSet.
*
* @param pst the prepared statement
* @param rs the ResultSet to work with
* @param rsIndex index for the result set
* @param psIndex index for the prepared statement
*
* @throws SQLException
*/
private static void populateNullableNumeric(PreparedStatement pst, ResultSet rs, int rsIndex, int psIndex) throws SQLException {
double nullableNumeric = rs.getDouble(rsIndex);
if (rs.wasNull()) {
pst.setNull(psIndex, java.sql.Types.NULL);
} else {
pst.setDouble(psIndex, nullableNumeric);
}
}
/**
* Open the PostgreSQL database
*
* @param icd Import Case Data holding connection credentials
*
* @return returns a Connection
*
* @throws SQLException if unable to open
*/
private static Connection getPostgreSQLConnection(ImportCaseData icd) throws SQLException {
return getPostgreSQLConnection(icd, icd.getPostgreSQLDbName());
}
/**
* Open the PostgreSQL database
*
* @param icd Import Case Data holding connection credentials
* @param dbName the name of the database to open
*
* @return returns a Connection
*
* @throws SQLException if unable to open
*/
private static Connection getPostgreSQLConnection(ImportCaseData icd, String dbName) throws SQLException {
return DriverManager.getConnection("jdbc:postgresql://" //NON-NLS
+ icd.getDb().getHost() + ":"
+ icd.getDb().getPort() + "/"
+ dbName,
icd.getDb().getUserName(),
icd.getDb().getPassword()); //NON-NLS
}
/**
* Open the SQLite database
*
* @param icd Import Case Data holding database path details
*
* @return returns a Connection
*
* @throws SQLException if unable to open
*/
private static Connection getSQLiteConnection(ImportCaseData icd) throws SQLException {
return DriverManager.getConnection("jdbc:sqlite:" + icd.getCaseInputFolder().resolve(AUTOPSY_DB_FILE).toString(), "", ""); //NON-NLS
}
}