Query the Database

Database Queries

This page is for people who are developing their own Autopsy plugin modules that require SQL queries. If you are not developing a module requiring SQL queries, you can skip this page. Autopsy currently allows either SQLite or PostgreSQL as the back-end database system for a case. Any module you write could be used with either as the backend database, at the user's discretion.

If you are writing code actually for Autopsy, not just an Autopsy module, you may need to be able to INSERT and UPDATE into the database as well. Please see INSERT and UPDATE.

Which Database is my Module Accessing?

In an Autopsy Module, you can check the database type currently in use with the following code snippet:

Case currentCase = Case.getCurrentCase();
if (currentCase.getCaseType() == Case.CaseType.MULTI_USER_CASE) 
{
    // PostgreSQL in use
}
else
{
    // SQLite in use
}

TSK methods to Query the Database With User-Supplied SQL

The following SleuthkitCase methods are available for the user to supply all of, or a portion of, a SQL query.

ArrayList<BlackboardAttribute> getMatchingAttributes(String whereClause)

ArrayList<BlackboardArtifact> getMatchingArtifacts(String whereClause)

long countFilesWhere(String sqlWhereClause)

List<AbstractFile> findAllFilesWhere(String sqlWhereClause)

List<Long> findAllFileIdsWhere(String sqlWhereClause)

CaseDbQuery executeQuery(String query)

List<FsContent> findFilesWhere(String sqlWhereClause) [deprecated]

ResultSet runQuery(String query) [deprecated]

void closeRunQuery(ResultSet resultSet) [deprecated]

The majority of them only allow the user to specify a WHERE clause, determining which records to SELECT.

The following example finds all the .txt files in the case:

List<AbstractFile> files = sk.findAllFilesWhere("LOWER(name) LIKE '%.txt'");

How to Avoid Pitfalls When Using the Query Methods

Because there are multiple backend databases, care must be taken to use strict SQL. When things must be different between database types, use Which Database is my Module Accessing? to determine which database type is currently in use and create the proper SQL statements. Be sure to test your module with both types of databases. They behave differently and will give you different resulting output order.

WHERE Clause Syntax

• Do not use backticks. PostgreSQL does not use them like SQLite does.
  
  
• Use only single quotes to quote values. Do not use double quotes for this. Quoting values is not required.
  

  SELECT * FROM tsk_files WHERE has_path = "1"    // Bad example
  SELECT * FROM tsk_files WHERE has_path = '1'    // Good example
  SELECT * FROM tsk_files WHERE has_path = 1      // Good example

  
  
• Use only double quotes to quote column names. Do not use single quotes for this. Quoting column names is not required.
  

  SELECT 'obj_id' FROM tsk_files WHERE has_path = 1  // Bad example
  SELECT "obj_id" FROM tsk_files WHERE has_path = 1  // Good example
  SELECT obj_id FROM tsk_files WHERE has_path = 1    // Good example

  
  
• Do not use || and && to connect logical clauses. This does not exist in PostgreSQL. Use OR and AND instead.

  SELECT COUNT(*) FROM tsk_files WHERE dir_type = '5' &&  md5 IS NULL || size > '0'    // Bad Example
  SELECT COUNT(*) FROM tsk_files WHERE dir_type = '5' AND md5 IS NULL OR size > '0'    // Good Example

  
  
• PostgreSQL compares are case-sensitive. Always specify what type of compare you want. UPPER() and LOWER() can help with that.

  SELECT * from people WHERE first_name LIKE '%somename%'                 // Will be case sensitive in PostgreSQL, not in SQLite
  SELECT * from people WHERE first_name ILIKE '%somename%'                // Works in PostgreSQL, does not exist in SQLite
  SELECT * from people WHERE LOWER(first_name) LIKE LOWER('%somename%')   // Not case sensitive in either database

  
  
• When generating WHERE queries via code, some folks include an AND(1) or OR(0) clause in the query as a placeholder that does not effect the outcome of the query but simplifies the query-generation logic. PostgreSQL does not allow true or false comparisons with integers. The PostgreSql syntax is AND(true) or OR(false). SQLite does not allow the PostgreSQL syntax and PostgreSQL does not allow the SQLite syntax. Do not use this trick to generate queries. Instead, have your code handle the edge cases of if there are no entries for the AND or OR portion of a clause.

  WHERE id=12 AND(1)                // SQLite example, will not work in PostgreSQL
  WHERE id=12 AND(true)             // PostgreSQL example, will not work in SQLite
  WHERE id=12                       // Will work in both, just a bit harder to handle all the cases in query-generation code

  
  
• SQLite allows non-standard usage of the IS keyword. Standard usage of IS checks if something IS NULL or IS NOT NULL. It does not compare against specific values. Remember when comparing values to use = instead of the IS keyword. If you want to check for NULL, then IS NULL is the right tool. Example:

  WHERE value IS '4'        // Bad example. Works in SQLite, does not work in PostgreSQL
  WHERE value = '4'         // Good example. Works in both SQLite and PostgreSQL
  WHERE value != '4'        // Good example. Works in both SQLite and PostgreSQL
  WHERE value IS NULL       // Good example. Works in both SQLite and PostgreSQL
  WHERE value IS NOT NULL   // Good example. Works in both SQLite and PostgreSQL

  
  
  

How to ORDER BY Consistently

• SQLite and PostgreSQL have different default sort orders for returned records, so you want to fully specify ORDER BY clauses for both database types. Example:

  Case currentCase = Case.getCurrentCase();
  String orderByClause;
  if (currentCase.getCaseType() == Case.CaseType.MULTI_USER_CASE) 
  {
      orderByClause = "ORDER BY att.value_text, ASC NULLS FIRST"; //PostgreSQL
  }
  else
  {
      orderByClause = "ORDER BY att.value_text ASC"; //SQLite
  }

  
  
• Do not use COLLATE NOCASE to order output. This does not exist in PostgreSQL. Use LOWER() or UPPER() instead.

  ORDER BY tsk_files.dir_type, tsk_files.name COLLATE NOCASE  // Bad Example
  ORDER BY tsk_files.dir_type, LOWER(tsk_files.name)          // Good Example

  
  
• In ORDER BY clauses, PostgreSQL ignores leading dashes. Given the following data, you will see the following two sort orders for the different databases.
  

  Data	PostgreSQL sort order	SQLite sort order
  Alpha	Alpha	-Bravo
  -Bravo	-Bravo	Alpha
  Charlie	Charlie	Charlie

  
  To force PostgreSQL to not ignore leading dashes, convert strings to SQL_ASCII before sorting by them. This is done with convert_to(), but it only exists in PostgreSQL.
  

  ORDER BY some_value                            // Bad example
  ORDER BY convert_to(some_value, 'SQL_ASCII')   // Good example

  
  With the code above, using SQL_ASCII encoding, the following results are seen:
  

  Data	PostgreSQL sort order	SQLite sort order
  Alpha	-Bravo	-Bravo
  -Bravo	Alpha	Alpha
  Charlie	Charlie	Charlie

  
  
• PostgreSQL sorts NULLs last for ASC and first for DESC. SQLite does the opposite. PostgreSQL allows you to control the NULL sort order with NULLS FIRST or NULLS LAST

  ORDER BY att.value_text ASC                                        // SQLite example, will give different ordering in PostgreSQL
  ORDER BY convert_to(att.value_text, 'SQL_ASCII') ASC NULLS FIRST   // PostgreSQL example. The command NULLS FIRST does not exist in SQLite, but SQLite will sort nulls first by default.