1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
|
/*! \page query_database_page Query the Database
\section types_of_databases 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 \subpage insert_and_update_database_page.
<br>
\subsection which_db Which Database is my Module Accessing?
In an Autopsy Module, you can check the database type currently in use with the following code snippet:
\code{.java}
Case currentCase = Case.getCurrentCase();
if (currentCase.getCaseType() == Case.CaseType.MULTI_USER_CASE)
{
// PostgreSQL in use
}
else
{
// SQLite in use
}
\endcode
<br>
\section db_user_calls 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.
\code{.java}
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]
\endcode
The majority of them only allow the user to specify a WHERE clause, determining which records to SELECT.
<br>
<br>
The following example finds all the .txt files in the case:
\code{.java}
List<AbstractFile> files = sk.findAllFilesWhere("LOWER(name) LIKE '%.txt'");
\endcode
<br>
\section db_pitfalls_to_avoid 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 \ref which_db 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.
<br>
<br>
\subsection general_items WHERE Clause Syntax
- Do not use backticks. PostgreSQL does not use them like SQLite does.
<br>
<br>
- Use only single quotes to quote values. Do not use double quotes for this. Quoting values is not required.
<br>
\code{.java}
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
\endcode
<br>
- Use only double quotes to quote column names. Do not use single quotes for this. Quoting column names is not required.
<br>
\code{.java}
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
\endcode
<br>
- Do not use || and && to connect logical clauses. This does not exist in PostgreSQL. Use OR and AND instead.
\code{.java}
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
\endcode
<br>
- PostgreSQL compares are case-sensitive. Always specify what type of compare you want. UPPER() and LOWER() can help with that.
\code{.java}
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
\endcode
<br>
- 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.
\code{.java}
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
\endcode
<br>
- 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:
\code{.java}
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
\endcode
<br>
<br>
\subsection order_by 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:
\code{.java}
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
}
\endcode
<br>
- Do not use COLLATE NOCASE to order output. This does not exist in PostgreSQL. Use LOWER() or UPPER() instead.
\code{.java}
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
\endcode
<br>
- In ORDER BY clauses, PostgreSQL ignores leading dashes. Given the following data, you will see the following two sort orders for the different databases.
<br>
| Data | PostgreSQL sort order | SQLite sort order|
|:--------:|:------------------------:|:------------------:|
|Alpha | Alpha | -Bravo |
|-Bravo | -Bravo | Alpha |
|Charlie | Charlie | Charlie |
<br>
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.
<br>
\code{.java}
ORDER BY some_value // Bad example
ORDER BY convert_to(some_value, 'SQL_ASCII') // Good example
\endcode
<br>
With the code above, using SQL_ASCII encoding, the following results are seen:
<br>
| Data | PostgreSQL sort order | SQLite sort order|
|:--------:|:------------------------:|:------------------:|
|Alpha | -Bravo | -Bravo |
|-Bravo | Alpha | Alpha |
|Charlie | Charlie | Charlie |
<br>
- 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
\code{.java}
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.
\endcode
<br>
*/
|
