Autopsy  4.5.0
Graphical digital forensics platform for The Sleuth Kit and other tools.
SQLHelper.java
Go to the documentation of this file.
1 /*
2  * Autopsy Forensic Browser
3  *
4  * Copyright 2013-16 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.timeline.db;
20 
21 import java.util.Collections;
22 import java.util.List;
23 import java.util.function.Function;
24 import java.util.stream.Collectors;
25 import java.util.stream.Stream;
26 import javax.annotation.Nonnull;
27 import org.apache.commons.lang3.StringUtils;
54 import org.sleuthkit.datamodel.TskData;
55 
60 class SQLHelper {
61 
62  static String useHashHitTablesHelper(RootFilter filter) {
63  HashHitsFilter hashHitFilter = filter.getHashHitsFilter();
64  return hashHitFilter.isActive() ? " LEFT JOIN hash_set_hits " : " "; //NON-NLS
65  }
66 
67  static String useTagTablesHelper(RootFilter filter) {
68  TagsFilter tagsFilter = filter.getTagsFilter();
69  return tagsFilter.isActive() ? " LEFT JOIN tags " : " "; //NON-NLS
70  }
71 
84  static <X> List<X> unGroupConcat(String groupConcat, Function<String, X> mapper) {
85  return StringUtils.isBlank(groupConcat) ? Collections.emptyList()
86  : Stream.of(groupConcat.split(","))
87  .map(mapper::apply)
88  .collect(Collectors.toList());
89  }
90 
100  private static String getSQLWhere(IntersectionFilter<?> filter) {
101  String join = String.join(" and ", filter.getSubFilters().stream()
102  .filter(Filter::isActive)
103  .map(SQLHelper::getSQLWhere)
104  .collect(Collectors.toList()));
105  return "(" + StringUtils.defaultIfBlank(join, "1") + ")";
106  }
107 
117  private static String getSQLWhere(UnionFilter<?> filter) {
118  String join = String.join(" or ", filter.getSubFilters().stream()
119  .filter(Filter::isActive)
120  .map(SQLHelper::getSQLWhere)
121  .collect(Collectors.toList()));
122  return "(" + StringUtils.defaultIfBlank(join, "1") + ")";
123  }
124 
125  static String getSQLWhere(RootFilter filter) {
126  return getSQLWhere((Filter) filter);
127  }
128 
141  private static String getSQLWhere(Filter filter) {
142  String result = "";
143  if (filter == null) {
144  return "1";
145  } else if (filter instanceof DescriptionFilter) {
146  result = getSQLWhere((DescriptionFilter) filter);
147  } else if (filter instanceof TagsFilter) {
148  result = getSQLWhere((TagsFilter) filter);
149  } else if (filter instanceof HashHitsFilter) {
150  result = getSQLWhere((HashHitsFilter) filter);
151  } else if (filter instanceof DataSourceFilter) {
152  result = getSQLWhere((DataSourceFilter) filter);
153  } else if (filter instanceof DataSourcesFilter) {
154  result = getSQLWhere((DataSourcesFilter) filter);
155  } else if (filter instanceof HideKnownFilter) {
156  result = getSQLWhere((HideKnownFilter) filter);
157  } else if (filter instanceof HashHitsFilter) {
158  result = getSQLWhere((HashHitsFilter) filter);
159  } else if (filter instanceof TextFilter) {
160  result = getSQLWhere((TextFilter) filter);
161  } else if (filter instanceof TypeFilter) {
162  result = getSQLWhere((TypeFilter) filter);
163  } else if (filter instanceof IntersectionFilter) {
164  result = getSQLWhere((IntersectionFilter) filter);
165  } else if (filter instanceof UnionFilter) {
166  result = getSQLWhere((UnionFilter) filter);
167  } else {
168  throw new IllegalArgumentException("getSQLWhere not defined for " + filter.getClass().getCanonicalName());
169  }
170  result = StringUtils.deleteWhitespace(result).equals("(1and1and1)") ? "1" : result; //NON-NLS
171  result = StringUtils.deleteWhitespace(result).equals("()") ? "1" : result;
172  return result;
173  }
174 
175  private static String getSQLWhere(HideKnownFilter filter) {
176  if (filter.isActive()) {
177  return "(known_state IS NOT '" + TskData.FileKnown.KNOWN.getFileKnownValue() + "')"; // NON-NLS
178  } else {
179  return "1";
180  }
181  }
182 
183  private static String getSQLWhere(DescriptionFilter filter) {
184  if (filter.isActive()) {
185  String likeOrNotLike = (filter.getFilterMode() == DescriptionFilter.FilterMode.INCLUDE ? "" : " NOT") + " LIKE '"; //NON-NLS
186  return "(" + getDescriptionColumn(filter.getDescriptionLoD()) + likeOrNotLike + filter.getDescription() + "' )"; // NON-NLS
187  } else {
188  return "1";
189  }
190  }
191 
192  private static String getSQLWhere(TagsFilter filter) {
193  if (filter.isActive()
194  && (filter.getSubFilters().isEmpty() == false)) {
195  String tagNameIDs = filter.getSubFilters().stream()
196  .filter((TagNameFilter t) -> t.isSelected() && !t.isDisabled())
197  .map((TagNameFilter t) -> String.valueOf(t.getTagName().getId()))
198  .collect(Collectors.joining(", ", "(", ")"));
199  return "(events.event_id == tags.event_id AND " //NON-NLS
200  + "tags.tag_name_id IN " + tagNameIDs + ") "; //NON-NLS
201  } else {
202  return "1";
203  }
204 
205  }
206 
207  private static String getSQLWhere(HashHitsFilter filter) {
208  if (filter.isActive()
209  && (filter.getSubFilters().isEmpty() == false)) {
210  String hashSetIDs = filter.getSubFilters().stream()
211  .filter((HashSetFilter t) -> t.isSelected() && !t.isDisabled())
212  .map((HashSetFilter t) -> String.valueOf(t.getHashSetID()))
213  .collect(Collectors.joining(", ", "(", ")"));
214  return "(hash_set_hits.hash_set_id IN " + hashSetIDs + " AND hash_set_hits.event_id == events.event_id)"; //NON-NLS
215  } else {
216  return "1";
217  }
218  }
219 
220  private static String getSQLWhere(DataSourceFilter filter) {
221  if (filter.isActive()) {
222  return "(datasource_id = '" + filter.getDataSourceID() + "')"; //NON-NLS
223  } else {
224  return "1";
225  }
226  }
227 
228  private static String getSQLWhere(DataSourcesFilter filter) {
229  return (filter.isActive()) ? "(datasource_id in (" //NON-NLS
230  + filter.getSubFilters().stream()
231  .filter(AbstractFilter::isActive)
232  .map((dataSourceFilter) -> String.valueOf(dataSourceFilter.getDataSourceID()))
233  .collect(Collectors.joining(", ")) + "))" : "1";
234  }
235 
236  private static String getSQLWhere(TextFilter filter) {
237  if (filter.isActive()) {
238  if (StringUtils.isBlank(filter.getText())) {
239  return "1";
240  }
241  String strippedFilterText = StringUtils.strip(filter.getText());
242  return "((med_description like '%" + strippedFilterText + "%')" //NON-NLS
243  + " or (full_description like '%" + strippedFilterText + "%')" //NON-NLS
244  + " or (short_description like '%" + strippedFilterText + "%'))"; //NON-NLS
245  } else {
246  return "1";
247  }
248  }
249 
258  private static String getSQLWhere(TypeFilter typeFilter) {
259  if (typeFilter.isSelected() == false) {
260  return "0";
261  } else if (typeFilter.getEventType() instanceof RootEventType) {
262  if (typeFilter.getSubFilters().stream()
263  .allMatch(subFilter -> subFilter.isActive() && subFilter.getSubFilters().stream().allMatch(Filter::isActive))) {
264  return "1"; //then collapse clause to true
265  }
266  }
267  return "(sub_type IN (" + StringUtils.join(getActiveSubTypes(typeFilter), ",") + "))"; //NON-NLS
268  }
269 
270  private static List<Integer> getActiveSubTypes(TypeFilter filter) {
271  if (filter.isActive()) {
272  if (filter.getSubFilters().isEmpty()) {
273  return Collections.singletonList(RootEventType.allTypes.indexOf(filter.getEventType()));
274  } else {
275  return filter.getSubFilters().stream().flatMap((Filter t) -> getActiveSubTypes((TypeFilter) t).stream()).collect(Collectors.toList());
276  }
277  } else {
278  return Collections.emptyList();
279  }
280  }
281 
294  static String getStrfTimeFormat(@Nonnull TimeUnits timeUnit) {
295  switch (timeUnit) {
296  case YEARS:
297  return "%Y-01-01T00:00:00"; // NON-NLS
298  case MONTHS:
299  return "%Y-%m-01T00:00:00"; // NON-NLS
300  case DAYS:
301  return "%Y-%m-%dT00:00:00"; // NON-NLS
302  case HOURS:
303  return "%Y-%m-%dT%H:00:00"; // NON-NLS
304  case MINUTES:
305  return "%Y-%m-%dT%H:%M:00"; // NON-NLS
306  case SECONDS:
307  default: //seconds - should never happen
308  return "%Y-%m-%dT%H:%M:%S"; // NON-NLS
309  }
310  }
311 
312  static String getDescriptionColumn(DescriptionLoD lod) {
313  switch (lod) {
314  case FULL:
315  return "full_description"; //NON-NLS
316  case MEDIUM:
317  return "med_description"; //NON-NLS
318  case SHORT:
319  default:
320  return "short_description"; //NON-NLS
321  }
322  }
323 
324  private SQLHelper() {
325  }
326 }

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