Autopsy  4.19.1
Graphical digital forensics platform for The Sleuth Kit and other tools.
AppSQLiteDB.java
Go to the documentation of this file.
1 /*
2  * Autopsy Forensic Browser
3  *
4  * Copyright 2019-2020 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.coreutils;
20 
21 import java.io.File;
22 import java.io.IOException;
23 import java.sql.Connection;
24 import java.sql.DriverManager;
25 import java.sql.ResultSet;
26 import java.sql.SQLException;
27 import java.sql.Statement;
28 import java.util.ArrayList;
29 import java.util.Collection;
30 import java.util.List;
31 import java.util.logging.Level;
37 import org.sleuthkit.datamodel.AbstractFile;
38 import org.sleuthkit.datamodel.DataSource;
39 import org.sleuthkit.datamodel.ReadContentInputStream;
40 import org.sleuthkit.datamodel.SleuthkitCase;
41 import org.sleuthkit.datamodel.TskCoreException;
42 
48 public final class AppSQLiteDB {
49 
50  private final Logger logger = Logger.getLogger(AppSQLiteDB.class.getName());
51 
52  private final AbstractFile dbAbstractFile; // AbstractFile for the DB file
53 
54  private final Connection connection;
55  private final Statement statement;
56 
61  private static final class AppSQLiteDBFileBundle {
62 
63  private final AbstractFile dbAbstractFile;
64  private final File dbFileCopy;
65 
66  AppSQLiteDBFileBundle(AbstractFile dbAbstractFile, File dbFileCopy) {
67  this.dbAbstractFile = dbAbstractFile;
68  this.dbFileCopy = dbFileCopy;
69  }
70 
71  AbstractFile getAbstractFile() {
72  return dbAbstractFile;
73  }
74 
75  File getFileCopy() {
76  return dbFileCopy;
77  }
78 
79  }
80 
81  private AppSQLiteDB(AppSQLiteDBFileBundle appSQLiteDBFileBundle) throws ClassNotFoundException, SQLException {
82  this.dbAbstractFile = appSQLiteDBFileBundle.getAbstractFile();
83 
84  Class.forName("org.sqlite.JDBC"); //NON-NLS //load JDBC driver
85  connection = DriverManager.getConnection("jdbc:sqlite:" + appSQLiteDBFileBundle.getFileCopy().getPath()); //NON-NLS
86  statement = connection.createStatement();
87  }
88 
108  public static Collection<AppSQLiteDB> findAppDatabases(DataSource dataSource,
109  String dbFileName, boolean matchExactName, String parentPathSubstr) {
110 
111  List<AppSQLiteDB> appDbs = new ArrayList<>();
112  try {
113  Collection<AppSQLiteDBFileBundle> dbFileBundles = findAndCopySQLiteDB(dataSource, dbFileName, matchExactName, parentPathSubstr, false);
114  dbFileBundles.forEach((dbFileBundle) -> {
115  try {
116  AppSQLiteDB appSQLiteDB = new AppSQLiteDB(dbFileBundle);
117  appDbs.add(appSQLiteDB);
118  } catch (ClassNotFoundException | SQLException ex) {
119  Logger.getLogger(AppSQLiteDB.class.getName()).log(Level.SEVERE, String.format("Failed to open a DB connection for file = '%s' and path = '%s'.", dbFileBundle.dbAbstractFile.getName(), dbFileBundle.getFileCopy().getPath()), ex); //NON-NLS
120  }
121  });
122  } catch (TskCoreException ex) {
123  Logger.getLogger(AppSQLiteDB.class.getName()).log(Level.SEVERE, String.format("Error finding App database files with name = '%s' and path = '%s'.", dbFileName, parentPathSubstr), ex); //NON-NLS
124  }
125 
126  return appDbs;
127  }
128 
129  public AbstractFile getDBFile() {
130  return this.dbAbstractFile;
131  }
132 
150  public AbstractFile attachDatabase(DataSource dataSource, String dbName,
151  String dbPath, String dbAlias) throws SQLException {
152  try {
153  // find and copy DB files with exact name and path.
154  Collection<AppSQLiteDBFileBundle> dbFileBundles = findAndCopySQLiteDB(dataSource, dbName, true, dbPath, true);
155  if (!dbFileBundles.isEmpty()) {
156  AppSQLiteDBFileBundle dbFileBundle = dbFileBundles.iterator().next();
157  String attachDbSql = String.format("ATTACH DATABASE '%s' AS '%s'", dbFileBundle.getFileCopy().getPath(), dbAlias); //NON-NLS
158  statement.executeUpdate(attachDbSql);
159 
160  return dbFileBundle.getAbstractFile();
161  }
162  } catch (TskCoreException ex) {
163  Logger.getLogger(AppSQLiteDB.class.getName()).log(Level.SEVERE, String.format("Error attaching to App database files with name = '%s' and path = '%s'.", dbName, dbPath), ex); //NON-NLS
164  }
165 
166  return null;
167  }
168 
186  private static Collection<AppSQLiteDBFileBundle> findAndCopySQLiteDB(DataSource dataSource, String dbName,
187  boolean matchExactName, String dbPath, boolean matchExactPath) throws TskCoreException {
188 
189  Case openCase;
190  try {
191  openCase = Case.getCurrentCaseThrows();
192  } catch (NoCurrentCaseException ex) {
193  throw new TskCoreException("Failed to get current case.", ex);
194  }
195 
196  List<AppSQLiteDBFileBundle> dbFileBundles = new ArrayList<>();
197  long fileId = 0;
198  String localDiskPath = "";
199 
200  SleuthkitCase skCase = openCase.getSleuthkitCase();
201  String parentPath = dbPath.replace("\\", "/");
202  parentPath = SleuthkitCase.escapeSingleQuotes(parentPath);
203 
204  String whereClause;
205  if (matchExactName) {
206  whereClause = String.format("LOWER(name) = LOWER('%s')", dbName);
207  } else {
208  whereClause = String.format("LOWER(name) LIKE LOWER('%%%s%%') AND LOWER(name) NOT LIKE LOWER('%%journal%%')", dbName);
209  }
210  if (matchExactPath) {
211  whereClause += String.format(" AND LOWER(parent_path) = LOWER('%s')", parentPath);
212  } else {
213  whereClause += String.format(" AND LOWER(parent_path) LIKE LOWER('%%%s%%')", parentPath);
214  }
215  whereClause += String.format(" AND data_source_obj_id = %s", dataSource.getId());
216 
217  List<AbstractFile> absFiles = skCase.findAllFilesWhere(whereClause);
218  for (AbstractFile absFile : absFiles) {
219  try {
220  localDiskPath = openCase.getTempDirectory()
221  + File.separator + absFile.getId() + absFile.getName();
222  File jFile = new java.io.File(localDiskPath);
223  fileId = absFile.getId();
224  ContentUtils.writeToFile(absFile, jFile);
225 
226  //Find and copy both WAL and SHM meta files
227  findAndCopySQLiteMetaFile(absFile, absFile.getName() + "-wal");
228  findAndCopySQLiteMetaFile(absFile, absFile.getName() + "-shm");
229 
230  AppSQLiteDBFileBundle dbFileBundle = new AppSQLiteDBFileBundle(absFile, jFile);
231  dbFileBundles.add(dbFileBundle);
232 
233  } catch (ReadContentInputStream.ReadContentInputStreamException ex) {
234  Logger.getLogger(AppSQLiteDB.class.getName()).log(Level.WARNING, String.format("Error reading content from file '%s' (id=%d).", absFile.getName(), fileId), ex); //NON-NLS
235  } catch (IOException | NoCurrentCaseException | TskCoreException ex) {
236  Logger.getLogger(AppSQLiteDB.class.getName()).log(Level.SEVERE, String.format("Error creating AppSQLiteDB for file '%s' (id=%d) to copied to '%s'.", absFile.getName(), fileId, localDiskPath), ex); //NON-NLS
237  }
238  }
239 
240  return dbFileBundles;
241  }
242 
250  public void detachDatabase(String dbAlias) throws SQLException {
251  String detachDbSql = String.format("DETACH DATABASE '%s'", dbAlias);
252  statement.executeUpdate(detachDbSql); //NON-NLS
253  }
254 
265  public ResultSet runQuery(String queryStr) throws SQLException {
266  ResultSet resultSet = null;
267 
268  if (null != queryStr) {
269  resultSet = statement.executeQuery(queryStr); //NON-NLS
270  }
271  return resultSet;
272  }
273 
278  public void close() {
279 
280  // Close the DB connection
281  try {
282  statement.close();
283  connection.close();
284  } catch (SQLException e) {
285  logger.log(Level.SEVERE, "Error closing the database", e); //NON-NLS
286  }
287  }
288 
298  public boolean columnExists(String tableName, String columnName) throws TskCoreException {
299 
300  boolean columnExists = false;
301  Statement colExistsStatement = null;
302  ResultSet resultSet = null;
303  try {
304  colExistsStatement = connection.createStatement();
305  String tableInfoQuery = "PRAGMA table_info(%s)"; //NON-NLS
306  resultSet = colExistsStatement.executeQuery(String.format(tableInfoQuery, tableName));
307  while (resultSet.next()) {
308  if (resultSet.getString("name").equalsIgnoreCase(columnName)) {
309  columnExists = true;
310  break;
311  }
312  }
313  } catch (SQLException ex) {
314  throw new TskCoreException("Error checking if column " + columnName + "exists ", ex);
315  } finally {
316  if (resultSet != null) {
317  try {
318  resultSet.close();
319  } catch (SQLException ex2) {
320  logger.log(Level.WARNING, "Failed to close resultset after checking column", ex2);
321  }
322  }
323  if (colExistsStatement != null) {
324  try {
325  colExistsStatement.close();
326  } catch (SQLException ex2) {
327  logger.log(Level.SEVERE, "Error closing Statement", ex2); //NON-NLS
328  }
329  }
330  }
331  return columnExists;
332  }
333 
342  public boolean tableExists(String tableName) throws TskCoreException {
343 
344  boolean tableExists = false;
345  Statement tableExistsStatement = null;
346  ResultSet resultSet = null;
347  try {
348 
349  tableExistsStatement = connection.createStatement();
350  resultSet = tableExistsStatement.executeQuery("SELECT name FROM sqlite_master WHERE type='table'"); //NON-NLS
351  while (resultSet.next()) {
352  if (resultSet.getString("name").equalsIgnoreCase(tableName)) { //NON-NLS
353  tableExists = true;
354  break;
355  }
356  }
357  } catch (SQLException ex) {
358  throw new TskCoreException("Error checking if table " + tableName + "exists ", ex);
359  } finally {
360  if (resultSet != null) {
361  try {
362  resultSet.close();
363  } catch (SQLException ex2) {
364  logger.log(Level.WARNING, "Failed to close resultset after checking table", ex2);
365  }
366  }
367  if (tableExistsStatement != null) {
368  try {
369  tableExistsStatement.close();
370  } catch (SQLException ex2) {
371  logger.log(Level.SEVERE, "Error closing Statement", ex2); //NON-NLS
372  }
373  }
374  }
375  return tableExists;
376  }
377 
390  private static void findAndCopySQLiteMetaFile(AbstractFile sqliteFile,
391  String metaFileName) throws NoCurrentCaseException, TskCoreException, IOException {
392 
393  // Do not look for metaFile if this is a carved directory
394  if(sqliteFile.getParentPath().equalsIgnoreCase("/$carvedfiles/")) {
395  return;
396  }
397 
398  Case openCase = Case.getCurrentCaseThrows();
399  SleuthkitCase sleuthkitCase = openCase.getSleuthkitCase();
400  Services services = new Services(sleuthkitCase);
401  FileManager fileManager = services.getFileManager();
402 
403  List<AbstractFile> metaFiles = fileManager.findFilesExactName(sqliteFile.getParent().getId(), metaFileName);
404 
405  if (metaFiles != null) {
406  for (AbstractFile metaFile : metaFiles) {
407  String localDiskPath = openCase.getTempDirectory()
408  + File.separator + sqliteFile.getId() + metaFile.getName();
409  File localMetaFile = new File(localDiskPath);
410  if (!localMetaFile.exists()) {
411  ContentUtils.writeToFile(metaFile, localMetaFile);
412  }
413  }
414  }
415  }
416 }
static void findAndCopySQLiteMetaFile(AbstractFile sqliteFile, String metaFileName)
static< T > long writeToFile(Content content, java.io.File outputFile, ProgressHandle progress, Future< T > worker, boolean source)
static Collection< AppSQLiteDB > findAppDatabases(DataSource dataSource, String dbFileName, boolean matchExactName, String parentPathSubstr)
AbstractFile attachDatabase(DataSource dataSource, String dbName, String dbPath, String dbAlias)
AppSQLiteDB(AppSQLiteDBFileBundle appSQLiteDBFileBundle)
static Collection< AppSQLiteDBFileBundle > findAndCopySQLiteDB(DataSource dataSource, String dbName, boolean matchExactName, String dbPath, boolean matchExactPath)
boolean columnExists(String tableName, String columnName)
List< AbstractFile > findFilesExactName(long parentId, String name)
synchronized static Logger getLogger(String name)
Definition: Logger.java:124

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