Autopsy  4.16.0
Graphical digital forensics platform for The Sleuth Kit and other tools.
SQLiteTableReader.java
Go to the documentation of this file.
1 /*
2  * Autopsy Forensic Browser
3  *
4  * Copyright 2018-2018 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.PreparedStatement;
26 import java.sql.ResultSet;
27 import java.sql.ResultSetMetaData;
28 import java.sql.SQLException;
29 import java.util.ArrayList;
30 import java.util.List;
31 import java.util.Objects;
32 import java.util.function.BooleanSupplier;
33 import java.util.function.Consumer;
34 import java.util.logging.Level;
40 import org.sleuthkit.datamodel.AbstractFile;
41 import org.sleuthkit.datamodel.SleuthkitCase;
42 import org.sleuthkit.datamodel.TskCoreException;
43 
54 public class SQLiteTableReader implements AutoCloseable {
55 
59  public static class Builder {
60 
61  private final AbstractFile file;
62 
63  private Consumer<String> forAllColumnNamesConsumer;
64  private Consumer<String> forAllStringValuesConsumer;
65  private Consumer<Long> forAllLongValuesConsumer;
66  private Consumer<Integer> forAllIntegerValuesConsumer;
67  private Consumer<Double> forAllFloatValuesConsumer;
68  private Consumer<byte[]> forAllBlobValuesConsumer;
69  private Consumer<Object> forAllTableValuesConsumer;
70 
71  static <T> Consumer<T> doNothing() {
72  return NOOP -> {};
73  }
74 
80  public Builder(AbstractFile file) {
81  this.file = file;
82 
83  this.forAllColumnNamesConsumer = Builder.doNothing();
84  this.forAllStringValuesConsumer = Builder.doNothing();
85  this.forAllLongValuesConsumer = Builder.doNothing();
86  this.forAllIntegerValuesConsumer = Builder.doNothing();
87  this.forAllFloatValuesConsumer = Builder.doNothing();
88  this.forAllBlobValuesConsumer = Builder.doNothing();
89  this.forAllTableValuesConsumer = Builder.doNothing();
90  }
91 
100  public Builder forAllColumnNames(Consumer<String> action) {
101  this.forAllColumnNamesConsumer = action;
102  return this;
103  }
104 
113  public Builder forAllStringValues(Consumer<String> action) {
114  this.forAllStringValuesConsumer = action;
115  return this;
116  }
117 
126  public Builder forAllIntegerValues(Consumer<Integer> action) {
127  this.forAllIntegerValuesConsumer = action;
128  return this;
129  }
130 
139  public Builder forAllFloatValues(Consumer<Double> action) {
140  this.forAllFloatValuesConsumer = action;
141  return this;
142  }
143 
152  public Builder forAllLongValues(Consumer<Long> action) {
153  this.forAllLongValuesConsumer = action;
154  return this;
155  }
156 
165  public Builder forAllBlobValues(Consumer<byte[]> action) {
166  this.forAllBlobValuesConsumer = action;
167  return this;
168  }
169 
179  public Builder forAllTableValues(Consumer<Object> action) {
180  this.forAllTableValuesConsumer = action;
181  return this;
182  }
183 
191  return new SQLiteTableReader(this);
192  }
193  }
194 
195  private final AbstractFile file;
196  private final Builder builder;
197 
198  private static final String SELECT_ALL_QUERY = "SELECT * FROM \"%s\"";
199  private static final Logger logger = Logger.getLogger(SQLiteTableReader.class.getName());
200 
201  private Connection conn;
202  private PreparedStatement statement;
203  private ResultSet queryResults;
204  private ResultSetMetaData currentMetadata;
205 
206  //Iteration state
207  private int currRowColumnIndex;
208  private int columnNameIndex;
209  private int totalColumnCount;
210  private boolean unfinishedRow;
211  private boolean liveResultSet;
212  private String prevTableName;
213 
218  private SQLiteTableReader(Builder builder) {
219  this.builder = builder;
220  this.file = builder.file;
221  }
222 
231  public List<String> getTableNames() throws SQLiteTableReaderException {
232  ensureOpen();
233  try (ResultSet tableNameResult = conn.createStatement()
234  .executeQuery("SELECT name FROM sqlite_master "
235  + " WHERE type= 'table' ")) {
236  List<String> tableNames = new ArrayList<>();
237  while (tableNameResult.next()) {
238  tableNames.add(tableNameResult.getString("name")); //NON-NLS
239  }
240  return tableNames;
241  } catch (SQLException ex) {
242  throw new SQLiteTableReaderException(ex);
243  }
244  }
245 
255  public int getRowCount(String tableName) throws SQLiteTableReaderException {
256  ensureOpen();
257  try (ResultSet countResult = conn.createStatement()
258  .executeQuery("SELECT count (*) as count FROM "
259  + "\"" + tableName + "\"")) {
260  return countResult.getInt("count");
261  } catch (SQLException ex) {
262  throw new SQLiteTableReaderException(ex);
263  }
264  }
265 
275  public int getColumnCount(String tableName) throws SQLiteTableReaderException {
276  ensureOpen();
277  try (ResultSet columnCount = conn.createStatement()
278  .executeQuery(String.format(SELECT_ALL_QUERY, tableName))) {
279  return columnCount.getMetaData().getColumnCount();
280  } catch (SQLException ex) {
281  throw new SQLiteTableReaderException(ex);
282  }
283  }
284 
295  public void read(String tableName) throws SQLiteTableReaderException {
296  readHelper(String.format(SELECT_ALL_QUERY, tableName), () -> false);
297  }
298 
312  public void read(String tableName, int limit, int offset) throws SQLiteTableReaderException {
313  readHelper(String.format(SELECT_ALL_QUERY, tableName) + " LIMIT " + limit
314  + " OFFSET " + offset, () -> false);
315  }
316 
327  public void read(String tableName, BooleanSupplier condition) throws SQLiteTableReaderException {
328  if (Objects.isNull(prevTableName) || !prevTableName.equals(tableName)) {
329  prevTableName = tableName;
331  }
332  readHelper(String.format(SELECT_ALL_QUERY, tableName), condition);
333  }
334 
341  private void readHelper(String query, BooleanSupplier condition) throws SQLiteTableReaderException {
342  try {
343  if (!liveResultSet) {
344  openTableResources(query);
345  columnNameIndex = 0;
346  }
347 
348  //Process column names before reading the database table values
349  while (columnNameIndex < totalColumnCount) {
350  if (condition.getAsBoolean()) {
351  return;
352  }
353  builder.forAllColumnNamesConsumer.accept(currentMetadata
354  .getColumnName(++columnNameIndex));
355  }
356 
357  while (unfinishedRow || queryResults.next()) {
358  while (currRowColumnIndex < totalColumnCount) {
359  if (condition.getAsBoolean()) {
360  unfinishedRow = true;
361  return;
362  }
363 
364  Object item = queryResults.getObject(++currRowColumnIndex);
365  if (item instanceof String) {
366  builder.forAllStringValuesConsumer.accept((String) item);
367  } else if (item instanceof Integer) {
368  builder.forAllIntegerValuesConsumer.accept((Integer) item);
369  } else if (item instanceof Double) {
370  builder.forAllFloatValuesConsumer.accept((Double) item);
371  } else if (item instanceof Long) {
372  builder.forAllLongValuesConsumer.accept((Long) item);
373  } else if (item instanceof byte[]) {
374  builder.forAllBlobValuesConsumer.accept((byte[]) item);
375  }
376 
377  builder.forAllTableValuesConsumer.accept(item);
378  }
379  unfinishedRow = false;
380  //Wrap column index back around if we've reached the end of the row
381  currRowColumnIndex = currRowColumnIndex % totalColumnCount;
382  }
384  } catch (SQLException ex) {
386  throw new SQLiteTableReaderException(ex);
387  }
388  }
389 
397  private void ensureOpen() throws SQLiteTableReaderException {
398  if (Objects.isNull(conn)) {
399  try {
400  Class.forName("org.sqlite.JDBC"); //NON-NLS
401  String localDiskPath = copyFileToTempDirectory(file, file.getId());
402 
403  //Find and copy both WAL and SHM meta files
404  findAndCopySQLiteMetaFile(file, file.getName() + "-wal");
405  findAndCopySQLiteMetaFile(file, file.getName() + "-shm");
406  conn = DriverManager.getConnection("jdbc:sqlite:" + localDiskPath);
407  } catch (NoCurrentCaseException | TskCoreException | IOException
408  | ClassNotFoundException | SQLException ex) {
409  throw new SQLiteTableReaderException(ex);
410  }
411  }
412  }
413 
426  private void findAndCopySQLiteMetaFile(AbstractFile sqliteFile,
427  String metaFileName) throws NoCurrentCaseException, TskCoreException, IOException {
428 
429  // Do not look for metaFile if this is a carved directory
430  if(sqliteFile.getParentPath().equalsIgnoreCase("/$carvedfiles/")) {
431  return;
432  }
433 
434  Case openCase = Case.getCurrentCaseThrows();
435  SleuthkitCase sleuthkitCase = openCase.getSleuthkitCase();
436  Services services = new Services(sleuthkitCase);
437  FileManager fileManager = services.getFileManager();
438 
439  List<AbstractFile> metaFiles = fileManager.findFilesExactName(sqliteFile.getParent().getId(), metaFileName);
440 
441  if (metaFiles != null) {
442  for (AbstractFile metaFile : metaFiles) {
443  copyFileToTempDirectory(metaFile, sqliteFile.getId());
444  }
445  }
446  }
447 
460  private String copyFileToTempDirectory(AbstractFile file, long fileId)
461  throws IOException, NoCurrentCaseException {
462 
463  String localDiskPath = Case.getCurrentCaseThrows().getTempDirectory()
464  + File.separator + fileId + file.getName();
465  File localDatabaseFile = new File(localDiskPath);
466  if (!localDatabaseFile.exists()) {
467  ContentUtils.writeToFile(file, localDatabaseFile);
468  }
469  return localDiskPath;
470  }
471 
479  private void openTableResources(String query) throws SQLiteTableReaderException {
480  try {
481  ensureOpen();
482  statement = conn.prepareStatement(query);
483  queryResults = statement.executeQuery();
484  currentMetadata = queryResults.getMetaData();
485  totalColumnCount = currentMetadata.getColumnCount();
486  liveResultSet = true;
487  } catch (SQLException ex) {
488  throw new SQLiteTableReaderException(ex);
489  }
490  }
491 
495  private void closeTableResources() {
496  try {
497  if (Objects.nonNull(statement)) {
498  statement.close();
499  }
500  if (Objects.nonNull(queryResults)) {
501  queryResults.close();
502  }
503  liveResultSet = false;
504  } catch (SQLException ex) {
505  logger.log(Level.SEVERE, "Failed to close table resources", ex);
506  }
507  }
508 
514  @Override
515  public void close() throws SQLiteTableReaderException {
516  try {
517  if (Objects.nonNull(conn)) {
518  conn.close();
519  }
520  } catch (SQLException ex) {
521  throw new SQLiteTableReaderException(ex);
522  }
523  }
524 
530  public boolean isFinished() {
531  return !liveResultSet;
532  }
533 
539  @Override
540  protected void finalize() throws Throwable {
541  try {
542  close();
543  } catch (SQLiteTableReaderException ex) {
544  logger.log(Level.SEVERE, "Failed to close reader in finalizer", ex);
545  }
546  super.finalize();
547  }
548 }
synchronized List< AbstractFile > findFilesExactName(long parentId, String name)
static< T > long writeToFile(Content content, java.io.File outputFile, ProgressHandle progress, Future< T > worker, boolean source)
void read(String tableName, BooleanSupplier condition)
String copyFileToTempDirectory(AbstractFile file, long fileId)
void read(String tableName, int limit, int offset)
void readHelper(String query, BooleanSupplier condition)
void findAndCopySQLiteMetaFile(AbstractFile sqliteFile, String metaFileName)
synchronized static Logger getLogger(String name)
Definition: Logger.java:124

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