Autopsy  4.7.0
Graphical digital forensics platform for The Sleuth Kit and other tools.
SQLiteViewer.java
Go to the documentation of this file.
1 /*
2  * Autopsy Forensic Browser
3  *
4  * Copyright 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.contentviewers;
20 
21 import java.awt.BorderLayout;
22 import java.awt.Component;
23 import java.awt.Cursor;
24 import java.io.File;
25 import java.io.FileOutputStream;
26 import java.io.IOException;
27 import java.sql.Connection;
28 import java.sql.DriverManager;
29 import java.sql.ResultSet;
30 import java.sql.ResultSetMetaData;
31 import java.sql.SQLException;
32 import java.sql.Statement;
33 import java.util.ArrayList;
34 import java.util.Arrays;
35 import java.util.Collections;
36 import java.util.LinkedHashMap;
37 import java.util.List;
38 import java.util.Map;
39 import java.util.Objects;
40 import java.util.TreeMap;
41 import java.util.logging.Level;
42 import javax.swing.JComboBox;
43 import javax.swing.JFileChooser;
44 import javax.swing.JOptionPane;
45 import javax.swing.filechooser.FileNameExtensionFilter;
46 import org.apache.commons.io.FilenameUtils;
47 import org.openide.util.NbBundle;
48 import org.openide.windows.WindowManager;
49 import org.sleuthkit.autopsy.casemodule.Case;
50 import org.sleuthkit.autopsy.casemodule.NoCurrentCaseException;
51 import org.sleuthkit.autopsy.casemodule.services.FileManager;
52 import org.sleuthkit.autopsy.casemodule.services.Services;
53 import org.sleuthkit.autopsy.coreutils.Logger;
54 import org.sleuthkit.autopsy.datamodel.ContentUtils;
55 import org.sleuthkit.datamodel.AbstractFile;
56 import org.sleuthkit.datamodel.SleuthkitCase;
57 import org.sleuthkit.datamodel.TskCoreException;
58 import org.sleuthkit.autopsy.coreutils.MessageNotifyUtil;
59 import org.sleuthkit.autopsy.coreutils.TimeStampUtils;
60 
64 @SuppressWarnings("PMD.SingularField") // UI widgets cause lots of false positives
65 class SQLiteViewer extends javax.swing.JPanel implements FileTypeViewer {
66 
67  private static final long serialVersionUID = 1L;
68  public static final String[] SUPPORTED_MIMETYPES = new String[]{"application/x-sqlite3"};
69  private static final int ROWS_PER_PAGE = 100;
70  private static final Logger logger = Logger.getLogger(FileViewer.class.getName());
71  private final SQLiteTableView selectedTableView = new SQLiteTableView();
72  private AbstractFile sqliteDbFile;
73  private File tmpDbFile;
74  private Connection connection;
75  private int numRows; // num of rows in the selected table
76  private int currPage = 0; // curr page of rows being displayed
77 
81  public SQLiteViewer() {
82  initComponents();
83  jTableDataPanel.add(selectedTableView, BorderLayout.CENTER);
84  }
85 
91  @SuppressWarnings("unchecked")
92  // <editor-fold defaultstate="collapsed" desc="Generated Code">//GEN-BEGIN:initComponents
93  private void initComponents() {
94 
95  jHdrPanel = new javax.swing.JPanel();
96  tablesDropdownList = new javax.swing.JComboBox<>();
97  jLabel1 = new javax.swing.JLabel();
98  numEntriesField = new javax.swing.JTextField();
99  jLabel2 = new javax.swing.JLabel();
100  currPageLabel = new javax.swing.JLabel();
101  jLabel3 = new javax.swing.JLabel();
102  numPagesLabel = new javax.swing.JLabel();
103  prevPageButton = new javax.swing.JButton();
104  nextPageButton = new javax.swing.JButton();
105  exportCsvButton = new javax.swing.JButton();
106  jTableDataPanel = new javax.swing.JPanel();
107 
108  jHdrPanel.setPreferredSize(new java.awt.Dimension(536, 40));
109 
110  tablesDropdownList.setModel(new javax.swing.DefaultComboBoxModel<>(new String[] { "Item 1", "Item 2", "Item 3", "Item 4" }));
111  tablesDropdownList.addActionListener(new java.awt.event.ActionListener() {
112  public void actionPerformed(java.awt.event.ActionEvent evt) {
113  tablesDropdownListActionPerformed(evt);
114  }
115  });
116 
117  org.openide.awt.Mnemonics.setLocalizedText(jLabel1, org.openide.util.NbBundle.getMessage(SQLiteViewer.class, "SQLiteViewer.jLabel1.text")); // NOI18N
118 
119  numEntriesField.setEditable(false);
120  numEntriesField.setText(org.openide.util.NbBundle.getMessage(SQLiteViewer.class, "SQLiteViewer.numEntriesField.text")); // NOI18N
121  numEntriesField.setBorder(null);
122 
123  org.openide.awt.Mnemonics.setLocalizedText(jLabel2, org.openide.util.NbBundle.getMessage(SQLiteViewer.class, "SQLiteViewer.jLabel2.text")); // NOI18N
124 
125  org.openide.awt.Mnemonics.setLocalizedText(currPageLabel, org.openide.util.NbBundle.getMessage(SQLiteViewer.class, "SQLiteViewer.currPageLabel.text")); // NOI18N
126 
127  org.openide.awt.Mnemonics.setLocalizedText(jLabel3, org.openide.util.NbBundle.getMessage(SQLiteViewer.class, "SQLiteViewer.jLabel3.text")); // NOI18N
128 
129  org.openide.awt.Mnemonics.setLocalizedText(numPagesLabel, org.openide.util.NbBundle.getMessage(SQLiteViewer.class, "SQLiteViewer.numPagesLabel.text")); // NOI18N
130 
131  prevPageButton.setIcon(new javax.swing.ImageIcon(getClass().getResource("/org/sleuthkit/autopsy/corecomponents/btn_step_back.png"))); // NOI18N
132  org.openide.awt.Mnemonics.setLocalizedText(prevPageButton, org.openide.util.NbBundle.getMessage(SQLiteViewer.class, "SQLiteViewer.prevPageButton.text")); // NOI18N
133  prevPageButton.setBorderPainted(false);
134  prevPageButton.setContentAreaFilled(false);
135  prevPageButton.setDisabledSelectedIcon(new javax.swing.ImageIcon(getClass().getResource("/org/sleuthkit/autopsy/corecomponents/btn_step_back_disabled.png"))); // NOI18N
136  prevPageButton.setMargin(new java.awt.Insets(2, 0, 2, 0));
137  prevPageButton.setPreferredSize(new java.awt.Dimension(23, 23));
138  prevPageButton.addActionListener(new java.awt.event.ActionListener() {
139  public void actionPerformed(java.awt.event.ActionEvent evt) {
140  prevPageButtonActionPerformed(evt);
141  }
142  });
143 
144  nextPageButton.setIcon(new javax.swing.ImageIcon(getClass().getResource("/org/sleuthkit/autopsy/corecomponents/btn_step_forward.png"))); // NOI18N
145  org.openide.awt.Mnemonics.setLocalizedText(nextPageButton, org.openide.util.NbBundle.getMessage(SQLiteViewer.class, "SQLiteViewer.nextPageButton.text")); // NOI18N
146  nextPageButton.setBorderPainted(false);
147  nextPageButton.setContentAreaFilled(false);
148  nextPageButton.setDisabledSelectedIcon(new javax.swing.ImageIcon(getClass().getResource("/org/sleuthkit/autopsy/corecomponents/btn_step_forward_disabled.png"))); // NOI18N
149  nextPageButton.setMargin(new java.awt.Insets(2, 0, 2, 0));
150  nextPageButton.setPreferredSize(new java.awt.Dimension(23, 23));
151  nextPageButton.addActionListener(new java.awt.event.ActionListener() {
152  public void actionPerformed(java.awt.event.ActionEvent evt) {
153  nextPageButtonActionPerformed(evt);
154  }
155  });
156 
157  org.openide.awt.Mnemonics.setLocalizedText(exportCsvButton, org.openide.util.NbBundle.getMessage(SQLiteViewer.class, "SQLiteViewer.exportCsvButton.text")); // NOI18N
158  exportCsvButton.addActionListener(new java.awt.event.ActionListener() {
159  public void actionPerformed(java.awt.event.ActionEvent evt) {
160  exportCsvButtonActionPerformed(evt);
161  }
162  });
163 
164  javax.swing.GroupLayout jHdrPanelLayout = new javax.swing.GroupLayout(jHdrPanel);
165  jHdrPanel.setLayout(jHdrPanelLayout);
166  jHdrPanelLayout.setHorizontalGroup(
167  jHdrPanelLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
168  .addGroup(jHdrPanelLayout.createSequentialGroup()
169  .addContainerGap()
170  .addComponent(jLabel1)
171  .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
172  .addComponent(tablesDropdownList, javax.swing.GroupLayout.PREFERRED_SIZE, 130, javax.swing.GroupLayout.PREFERRED_SIZE)
173  .addGap(18, 18, 18)
174  .addComponent(numEntriesField, javax.swing.GroupLayout.PREFERRED_SIZE, 71, javax.swing.GroupLayout.PREFERRED_SIZE)
175  .addGap(15, 15, 15)
176  .addComponent(jLabel2)
177  .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
178  .addComponent(currPageLabel)
179  .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
180  .addComponent(jLabel3)
181  .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
182  .addComponent(numPagesLabel)
183  .addGap(18, 18, 18)
184  .addComponent(prevPageButton, javax.swing.GroupLayout.PREFERRED_SIZE, 23, javax.swing.GroupLayout.PREFERRED_SIZE)
185  .addGap(0, 0, 0)
186  .addComponent(nextPageButton, javax.swing.GroupLayout.PREFERRED_SIZE, 23, javax.swing.GroupLayout.PREFERRED_SIZE)
187  .addGap(29, 29, 29)
188  .addComponent(exportCsvButton)
189  .addContainerGap(javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))
190  );
191  jHdrPanelLayout.setVerticalGroup(
192  jHdrPanelLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
193  .addGroup(jHdrPanelLayout.createSequentialGroup()
194  .addContainerGap()
195  .addGroup(jHdrPanelLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
196  .addComponent(exportCsvButton)
197  .addComponent(nextPageButton, javax.swing.GroupLayout.PREFERRED_SIZE, 23, javax.swing.GroupLayout.PREFERRED_SIZE)
198  .addComponent(prevPageButton, javax.swing.GroupLayout.PREFERRED_SIZE, 23, javax.swing.GroupLayout.PREFERRED_SIZE)
199  .addGroup(jHdrPanelLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
200  .addComponent(tablesDropdownList, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
201  .addComponent(jLabel1)
202  .addComponent(numEntriesField, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
203  .addComponent(jLabel2)
204  .addComponent(currPageLabel)
205  .addComponent(jLabel3)
206  .addComponent(numPagesLabel)))
207  .addContainerGap())
208  );
209 
210  jTableDataPanel.setLayout(new java.awt.BorderLayout());
211 
212  javax.swing.GroupLayout layout = new javax.swing.GroupLayout(this);
213  this.setLayout(layout);
214  layout.setHorizontalGroup(
215  layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
216  .addComponent(jHdrPanel, javax.swing.GroupLayout.DEFAULT_SIZE, 569, Short.MAX_VALUE)
217  .addComponent(jTableDataPanel, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
218  );
219  layout.setVerticalGroup(
220  layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
221  .addGroup(layout.createSequentialGroup()
222  .addComponent(jHdrPanel, javax.swing.GroupLayout.PREFERRED_SIZE, 53, javax.swing.GroupLayout.PREFERRED_SIZE)
223  .addGap(0, 0, 0)
224  .addComponent(jTableDataPanel, javax.swing.GroupLayout.DEFAULT_SIZE, 317, Short.MAX_VALUE))
225  );
226  }// </editor-fold>//GEN-END:initComponents
227 
228  private void nextPageButtonActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_nextPageButtonActionPerformed
229  WindowManager.getDefault().getMainWindow().setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
230  currPage++;
231  if (currPage * ROWS_PER_PAGE > numRows) {
232  nextPageButton.setEnabled(false);
233  }
234  currPageLabel.setText(Integer.toString(currPage));
235  prevPageButton.setEnabled(true);
236 
237  // read and display a page of rows
238  String tableName = (String) this.tablesDropdownList.getSelectedItem();
239  readTable(tableName, (currPage - 1) * ROWS_PER_PAGE + 1, ROWS_PER_PAGE);
240  WindowManager.getDefault().getMainWindow().setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
241  }//GEN-LAST:event_nextPageButtonActionPerformed
242 
243  private void prevPageButtonActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_prevPageButtonActionPerformed
244 
245  WindowManager.getDefault().getMainWindow().setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
246  currPage--;
247  if (currPage == 1) {
248  prevPageButton.setEnabled(false);
249  }
250  currPageLabel.setText(Integer.toString(currPage));
251  nextPageButton.setEnabled(true);
252 
253  // read and display a page of rows
254  String tableName = (String) this.tablesDropdownList.getSelectedItem();
255  readTable(tableName, (currPage - 1) * ROWS_PER_PAGE + 1, ROWS_PER_PAGE);
256  WindowManager.getDefault().getMainWindow().setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
257  }//GEN-LAST:event_prevPageButtonActionPerformed
258 
259  private void tablesDropdownListActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_tablesDropdownListActionPerformed
260  JComboBox<?> cb = (JComboBox<?>) evt.getSource();
261  String tableName = (String) cb.getSelectedItem();
262  if (null == tableName) {
263  return;
264  }
265  WindowManager.getDefault().getMainWindow().setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
266  selectTable(tableName);
267  WindowManager.getDefault().getMainWindow().setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
268  }//GEN-LAST:event_tablesDropdownListActionPerformed
269 
277  @NbBundle.Messages({"SQLiteViewer.csvExport.fileName.empty=Please input a file name for exporting.",
278  "SQLiteViewer.csvExport.title=Export to csv file",
279  "SQLiteViewer.csvExport.confirm.msg=Do you want to overwrite the existing file?"})
280  private void exportCsvButtonActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_exportCsvButtonActionPerformed
281  Case openCase = Case.getCurrentCase();
282  File caseDirectory = new File(openCase.getExportDirectory());
283  JFileChooser fileChooser = new JFileChooser();
284  fileChooser.setDragEnabled(false);
285  fileChooser.setCurrentDirectory(caseDirectory);
286  //Set a filter to let the filechooser only work for csv files
287  FileNameExtensionFilter csvFilter = new FileNameExtensionFilter("*.csv", "csv");
288  fileChooser.addChoosableFileFilter(csvFilter);
289  fileChooser.setAcceptAllFileFilterUsed(true);
290  fileChooser.setFileFilter(csvFilter);
291  fileChooser.setFileSelectionMode(JFileChooser.FILES_ONLY);
292  int choice = fileChooser.showSaveDialog((Component) evt.getSource()); //TODO
293  if (JFileChooser.APPROVE_OPTION == choice) {
294  boolean overwrite = false;
295  File file = fileChooser.getSelectedFile();
296  if (file == null) {
297  JOptionPane.showMessageDialog(this,
298  Bundle.SQLiteViewer_csvExport_fileName_empty(),
299  Bundle.SQLiteViewer_csvExport_title(),
300  JOptionPane.WARNING_MESSAGE);
301  return;
302  } else if (file.exists() && FilenameUtils.getExtension(file.getName()).equalsIgnoreCase("csv")) {
303  if (JOptionPane.YES_OPTION == JOptionPane.showConfirmDialog(this,
304  Bundle.SQLiteViewer_csvExport_confirm_msg(),
305  Bundle.SQLiteViewer_csvExport_title(),
306  JOptionPane.YES_NO_OPTION)) {
307  overwrite = true;
308  } else {
309  return;
310  }
311  }
312 
313  exportTableToCsv(file, overwrite);
314  }
315  }//GEN-LAST:event_exportCsvButtonActionPerformed
316 
317  // Variables declaration - do not modify//GEN-BEGIN:variables
318  private javax.swing.JLabel currPageLabel;
319  private javax.swing.JButton exportCsvButton;
320  private javax.swing.JPanel jHdrPanel;
321  private javax.swing.JLabel jLabel1;
322  private javax.swing.JLabel jLabel2;
323  private javax.swing.JLabel jLabel3;
324  private javax.swing.JPanel jTableDataPanel;
325  private javax.swing.JButton nextPageButton;
326  private javax.swing.JTextField numEntriesField;
327  private javax.swing.JLabel numPagesLabel;
328  private javax.swing.JButton prevPageButton;
329  private javax.swing.JComboBox<String> tablesDropdownList;
330  // End of variables declaration//GEN-END:variables
331 
332  @Override
333  public List<String> getSupportedMIMETypes() {
334  return Arrays.asList(SUPPORTED_MIMETYPES);
335  }
336 
337  @Override
338  public void setFile(AbstractFile file) {
339  WindowManager.getDefault().getMainWindow().setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
340  sqliteDbFile = file;
341  processSQLiteFile();
342  WindowManager.getDefault().getMainWindow().setCursor(Cursor.getPredefinedCursor(Cursor.DEFAULT_CURSOR));
343  }
344 
345  @Override
346  public Component getComponent() {
347  return this;
348  }
349 
350  @Override
351  public void resetComponent() {
352  tablesDropdownList.setEnabled(true);
353  tablesDropdownList.removeAllItems();
354  numEntriesField.setText("");
355 
356  // close DB connection to file
357  if (null != connection) {
358  try {
359  connection.close();
360  connection = null;
361  } catch (SQLException ex) {
362  logger.log(Level.SEVERE, "Failed to close DB connection to file.", ex); //NON-NLS
363  }
364  }
365 
366  sqliteDbFile = null;
367  }
368 
372  @NbBundle.Messages({
373  "SQLiteViewer.comboBox.noTableEntry=No tables found",
374  "SQLiteViewer.errorMessage.interrupted=The processing of the file was interrupted.",
375  "SQLiteViewer.errorMessage.noCurrentCase=The case has been closed.",
376  "SQLiteViewer.errorMessage.failedToExtractFile=The file could not be extracted from the data source.",
377  "SQLiteViewer.errorMessage.failedToQueryDatabase=The database tables in the file could not be read.",
378  "SQLiteViewer.errorMessage.failedToinitJDBCDriver=The JDBC driver for SQLite could not be loaded.",
379  "# {0} - exception message", "SQLiteViewer.errorMessage.unexpectedError=An unexpected error occurred:\n{0).",})
380  private void processSQLiteFile() {
381 
382  tablesDropdownList.removeAllItems();
383 
384  // Copy the file to temp folder
385  String tmpDBPathName;
386  try {
387  tmpDBPathName = Case.getCurrentCaseThrows().getTempDirectory() + File.separator + sqliteDbFile.getName();
388  } catch (NoCurrentCaseException ex) {
389  logger.log(Level.SEVERE, "Current case has been closed", ex); //NON-NLS
390  MessageNotifyUtil.Message.error(Bundle.SQLiteViewer_errorMessage_noCurrentCase());
391  return;
392  }
393 
394  tmpDbFile = new File(tmpDBPathName);
395  if (! tmpDbFile.exists()) {
396  try {
397  ContentUtils.writeToFile(sqliteDbFile, tmpDbFile);
398 
399  // Look for any meta files associated with this DB - WAL, SHM, etc.
400  findAndCopySQLiteMetaFile(sqliteDbFile, sqliteDbFile.getName() + "-wal");
401  findAndCopySQLiteMetaFile(sqliteDbFile, sqliteDbFile.getName() + "-shm");
402  } catch (IOException | NoCurrentCaseException | TskCoreException ex) {
403  logger.log(Level.SEVERE, String.format("Failed to create temp copy of DB file '%s' (objId=%d)", sqliteDbFile.getName(), sqliteDbFile.getId()), ex); //NON-NLS
404  MessageNotifyUtil.Message.error(Bundle.SQLiteViewer_errorMessage_failedToExtractFile());
405  return;
406  }
407  }
408 
409  try {
410  // Load the SQLite JDBC driver, if necessary.
411  Class.forName("org.sqlite.JDBC"); //NON-NLS
412  connection = DriverManager.getConnection("jdbc:sqlite:" + tmpDBPathName); //NON-NLS
413 
414  Map<String, String> dbTablesMap = getTables();
415  if (dbTablesMap.isEmpty()) {
416  tablesDropdownList.addItem(Bundle.SQLiteViewer_comboBox_noTableEntry());
417  tablesDropdownList.setEnabled(false);
418  } else {
419  dbTablesMap.keySet().forEach((tableName) -> {
420  tablesDropdownList.addItem(tableName);
421  });
422  }
423  } catch (ClassNotFoundException ex) {
424  logger.log(Level.SEVERE, String.format("Failed to initialize JDBC SQLite '%s' (objId=%d)", sqliteDbFile.getName(), sqliteDbFile.getId()), ex); //NON-NLS
425  MessageNotifyUtil.Message.error(Bundle.SQLiteViewer_errorMessage_failedToinitJDBCDriver());
426  } catch (SQLException ex) {
427  logger.log(Level.SEVERE, String.format("Failed to get tables from DB file '%s' (objId=%d)", sqliteDbFile.getName(), sqliteDbFile.getId()), ex); //NON-NLS
428  MessageNotifyUtil.Message.error(Bundle.SQLiteViewer_errorMessage_failedToQueryDatabase());
429  }
430  }
431 
439  private void findAndCopySQLiteMetaFile(AbstractFile sqliteFile, String metaFileName) throws NoCurrentCaseException, TskCoreException, IOException {
440  Case openCase = Case.getCurrentCaseThrows();
441  SleuthkitCase sleuthkitCase = openCase.getSleuthkitCase();
442  Services services = new Services(sleuthkitCase);
443  FileManager fileManager = services.getFileManager();
444  List<AbstractFile> metaFiles = fileManager.findFiles(sqliteFile.getDataSource(), metaFileName, sqliteFile.getParent().getName());
445  if (metaFiles != null) {
446  for (AbstractFile metaFile : metaFiles) {
447  String tmpMetafilePathName = openCase.getTempDirectory() + File.separator + metaFile.getName();
448  File tmpMetafile = new File(tmpMetafilePathName);
449  ContentUtils.writeToFile(metaFile, tmpMetafile);
450  }
451  }
452  }
453 
459  private Map<String, String> getTables() throws SQLException {
460  Map<String, String> dbTablesMap = new TreeMap<>();
461  Statement statement = null;
462  ResultSet resultSet = null;
463  try {
464  statement = connection.createStatement();
465  resultSet = statement.executeQuery(
466  "SELECT name, sql FROM sqlite_master "
467  + " WHERE type= 'table' "
468  + " ORDER BY name;"); //NON-NLS
469  while (resultSet.next()) {
470  String tableName = resultSet.getString("name"); //NON-NLS
471  String tableSQL = resultSet.getString("sql"); //NON-NLS
472  dbTablesMap.put(tableName, tableSQL);
473  }
474  } finally {
475  if (null != resultSet) {
476  resultSet.close();
477  }
478  if (null != statement) {
479  statement.close();
480  }
481  }
482  return dbTablesMap;
483  }
484 
485  @NbBundle.Messages({"# {0} - tableName",
486  "SQLiteViewer.selectTable.errorText=Error getting row count for table: {0}"
487  })
488  private void selectTable(String tableName) {
489 
490  try (Statement statement = connection.createStatement();
491  ResultSet resultSet = statement.executeQuery(
492  "SELECT count (*) as count FROM " + tableName)) { //NON-NLS{
493 
494  numRows = resultSet.getInt("count");
495  numEntriesField.setText(numRows + " entries");
496 
497  currPage = 1;
498  currPageLabel.setText(Integer.toString(currPage));
499  numPagesLabel.setText(Integer.toString((numRows / ROWS_PER_PAGE) + 1));
500 
501  prevPageButton.setEnabled(false);
502 
503  if (numRows > 0) {
504  exportCsvButton.setEnabled(true);
505  nextPageButton.setEnabled(((numRows > ROWS_PER_PAGE)));
506  readTable(tableName, (currPage - 1) * ROWS_PER_PAGE + 1, ROWS_PER_PAGE);
507  } else {
508  exportCsvButton.setEnabled(false);
509  nextPageButton.setEnabled(false);
510  selectedTableView.setupTable(Collections.emptyList());
511  }
512 
513  } catch (SQLException ex) {
514  logger.log(Level.SEVERE, String.format("Failed to load table %s from DB file '%s' (objId=%d)", tableName, sqliteDbFile.getName(), sqliteDbFile.getId()), ex); //NON-NLS
515  MessageNotifyUtil.Message.error(Bundle.SQLiteViewer_selectTable_errorText(tableName));
516  }
517  }
518 
519  @NbBundle.Messages({"# {0} - tableName",
520  "SQLiteViewer.readTable.errorText=Error getting rows for table: {0}"})
521  private void readTable(String tableName, int startRow, int numRowsToRead) {
522 
523  try (
524  Statement statement = connection.createStatement();
525  ResultSet resultSet = statement.executeQuery(
526  "SELECT * FROM " + tableName
527  + " LIMIT " + Integer.toString(numRowsToRead)
528  + " OFFSET " + Integer.toString(startRow - 1))) {
529 
530  ArrayList<Map<String, Object>> rows = resultSetToArrayList(resultSet);
531  if (Objects.nonNull(rows)) {
532  selectedTableView.setupTable(rows);
533  } else {
534  selectedTableView.setupTable(Collections.emptyList());
535  }
536  } catch (SQLException ex) {
537  logger.log(Level.SEVERE, String.format("Failed to read table %s from DB file '%s' (objId=%d)", tableName, sqliteDbFile.getName(), sqliteDbFile.getId()), ex); //NON-NLS
538  MessageNotifyUtil.Message.error(Bundle.SQLiteViewer_readTable_errorText(tableName));
539  }
540  }
541 
542  @NbBundle.Messages("SQLiteViewer.BlobNotShown.message=BLOB Data not shown")
543  private ArrayList<Map<String, Object>> resultSetToArrayList(ResultSet rs) throws SQLException {
544  ResultSetMetaData metaData = rs.getMetaData();
545  int columns = metaData.getColumnCount();
546  ArrayList<Map<String, Object>> rowlist = new ArrayList<>();
547  while (rs.next()) {
548  Map<String, Object> row = new LinkedHashMap<>(columns);
549  for (int i = 1; i <= columns; ++i) {
550  if (rs.getObject(i) == null) {
551  row.put(metaData.getColumnName(i), "");
552  } else {
553  if (metaData.getColumnTypeName(i).compareToIgnoreCase("blob") == 0) {
554  row.put(metaData.getColumnName(i), Bundle.SQLiteViewer_BlobNotShown_message());
555  } else {
556  row.put(metaData.getColumnName(i), rs.getObject(i));
557  }
558  }
559  }
560  rowlist.add(row);
561  }
562 
563  return rowlist;
564  }
565 
566  @NbBundle.Messages({"SQLiteViewer.exportTableToCsv.write.errText=Failed to export table content to csv file.",
567  "SQLiteViewer.exportTableToCsv.FileName=File name: ",
568  "SQLiteViewer.exportTableToCsv.TableName=Table name: "
569  })
570  private void exportTableToCsv(File file, boolean overwrite) {
571  String tableName = (String) this.tablesDropdownList.getSelectedItem();
572  String csvFileSuffix = "_" + tableName + "_" + TimeStampUtils.createTimeStamp() + ".csv";
573  try (
574  Statement statement = connection.createStatement();
575  ResultSet resultSet = statement.executeQuery("SELECT * FROM " + tableName)) {
576  List<Map<String, Object>> currentTableRows = resultSetToArrayList(resultSet);
577 
578  if (Objects.isNull(currentTableRows) || currentTableRows.isEmpty()) {
579  logger.log(Level.INFO, String.format("The table %s is empty. (objId=%d)", tableName, sqliteDbFile.getId())); //NON-NLS
580  } else {
581  String fileName = file.getName();
582  File csvFile;
583  if (overwrite) {
584  csvFile = file;
585  } else if (FilenameUtils.getExtension(fileName).equalsIgnoreCase("csv")) {
586  csvFile = new File(file.getParentFile(), FilenameUtils.removeExtension(fileName) + csvFileSuffix);
587  } else {
588  csvFile = new File(file.toString() + csvFileSuffix);
589  }
590  FileOutputStream out = new FileOutputStream(csvFile, false);
591 
592  out.write((Bundle.SQLiteViewer_exportTableToCsv_FileName() + csvFile.getName() + "\n").getBytes());
593  out.write((Bundle.SQLiteViewer_exportTableToCsv_TableName() + tableName + "\n").getBytes());
594  // Set up the column names
595  Map<String, Object> row = currentTableRows.get(0);
596  StringBuffer header = new StringBuffer();
597  for (Map.Entry<String, Object> col : row.entrySet()) {
598  String colName = col.getKey();
599  if (header.length() > 0) {
600  header.append(',').append(colName);
601  } else {
602  header.append(colName);
603  }
604  }
605  out.write(header.append('\n').toString().getBytes());
606 
607  for (Map<String, Object> maps : currentTableRows) {
608  StringBuffer valueLine = new StringBuffer();
609  maps.values().forEach((value) -> {
610  if (valueLine.length() > 0) {
611  valueLine.append(',').append(value.toString());
612  } else {
613  valueLine.append(value.toString());
614  }
615  });
616  out.write(valueLine.append('\n').toString().getBytes());
617  }
618  }
619  } catch (SQLException ex) {
620  logger.log(Level.SEVERE, String.format("Failed to read table %s from DB file '%s' (objId=%d)", tableName, sqliteDbFile.getName(), sqliteDbFile.getId()), ex); //NON-NLS
621  MessageNotifyUtil.Message.error(Bundle.SQLiteViewer_readTable_errorText(tableName));
622  } catch (IOException ex) {
623  logger.log(Level.SEVERE, String.format("Failed to export table %s to file '%s'", tableName, file.getName()), ex); //NON-NLS
624  MessageNotifyUtil.Message.error(Bundle.SQLiteViewer_exportTableToCsv_write_errText());
625  }
626  }
627 
628 
629 }
org.sleuthkit.autopsy.datamodel.ContentUtils
Definition: ContentUtils.java:55

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