Autopsy  4.20.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 }
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)
List< AbstractFile > findFilesExactName(long parentId, String name)
void findAndCopySQLiteMetaFile(AbstractFile sqliteFile, String metaFileName)
synchronized static Logger getLogger(String name)
Definition: Logger.java:124

Copyright © 2012-2022 Basis Technology. Generated on: Tue Aug 1 2023
This work is licensed under a Creative Commons Attribution-Share Alike 3.0 United States License.