Autopsy  4.9.1
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  Case openCase = Case.getCurrentCaseThrows();
430  SleuthkitCase sleuthkitCase = openCase.getSleuthkitCase();
431  Services services = new Services(sleuthkitCase);
432  FileManager fileManager = services.getFileManager();
433 
434  List<AbstractFile> metaFiles = fileManager.findFiles(
435  sqliteFile.getDataSource(), metaFileName,
436  sqliteFile.getParent().getName());
437 
438  if (metaFiles != null) {
439  for (AbstractFile metaFile : metaFiles) {
440  copyFileToTempDirectory(metaFile, sqliteFile.getId());
441  }
442  }
443  }
444 
457  private String copyFileToTempDirectory(AbstractFile file, long fileId)
458  throws IOException, NoCurrentCaseException {
459 
460  String localDiskPath = Case.getCurrentCaseThrows().getTempDirectory()
461  + File.separator + fileId + file.getName();
462  File localDatabaseFile = new File(localDiskPath);
463  if (!localDatabaseFile.exists()) {
464  ContentUtils.writeToFile(file, localDatabaseFile);
465  }
466  return localDiskPath;
467  }
468 
476  private void openTableResources(String query) throws SQLiteTableReaderException {
477  try {
478  ensureOpen();
479  statement = conn.prepareStatement(query);
480  queryResults = statement.executeQuery();
481  currentMetadata = queryResults.getMetaData();
482  totalColumnCount = currentMetadata.getColumnCount();
483  liveResultSet = true;
484  } catch (SQLException ex) {
485  throw new SQLiteTableReaderException(ex);
486  }
487  }
488 
492  private void closeTableResources() {
493  try {
494  if (Objects.nonNull(statement)) {
495  statement.close();
496  }
497  if (Objects.nonNull(queryResults)) {
498  queryResults.close();
499  }
500  liveResultSet = false;
501  } catch (SQLException ex) {
502  logger.log(Level.SEVERE, "Failed to close table resources", ex);
503  }
504  }
505 
511  @Override
512  public void close() throws SQLiteTableReaderException {
513  try {
514  if (Objects.nonNull(conn)) {
515  conn.close();
516  }
517  } catch (SQLException ex) {
518  throw new SQLiteTableReaderException(ex);
519  }
520  }
521 
527  public boolean isFinished() {
528  return !liveResultSet;
529  }
530 
536  @Override
537  protected void finalize() throws Throwable {
538  try {
539  close();
540  } catch (SQLiteTableReaderException ex) {
541  logger.log(Level.SEVERE, "Failed to close reader in finalizer", ex);
542  }
543  super.finalize();
544  }
545 }
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 List< AbstractFile > findFiles(String fileName)
synchronized static Logger getLogger(String name)
Definition: Logger.java:124

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