19 package org.sleuthkit.datamodel;
21 import java.sql.PreparedStatement;
22 import java.sql.ResultSet;
23 import java.sql.SQLException;
24 import java.sql.Statement;
25 import java.util.ArrayList;
26 import java.util.Collections;
27 import java.util.List;
28 import java.util.Optional;
62 List<TagSet> tagSetList =
new ArrayList<>();
65 String getAllTagSetsQuery =
"SELECT * FROM tsk_tag_sets";
66 try (CaseDbConnection connection = skCase.getConnection(); Statement stmt = connection.createStatement(); ResultSet resultSet = stmt.executeQuery(getAllTagSetsQuery);) {
67 while (resultSet.next()) {
68 int setID = resultSet.getInt(
"tag_set_id");
69 String setName = resultSet.getString(
"name");
70 TagSet set =
new TagSet(setID, setName, getTagNamesByTagSetID(setID));
73 }
catch (SQLException ex) {
74 throw new TskCoreException(
"Error occurred getting TagSet list.", ex);
92 if (name == null || name.isEmpty()) {
93 throw new IllegalArgumentException(
"Error adding TagSet, TagSet name must be non-empty string.");
99 try (Statement stmt = trans.getConnection().createStatement()) {
100 String query = String.format(
"INSERT INTO tsk_tag_sets (name) VALUES('%s')", name);
103 stmt.execute(query, Statement.RETURN_GENERATED_KEYS);
108 try (ResultSet resultSet = stmt.getGeneratedKeys()) {
111 int setID = resultSet.getInt(1);
113 List<TagName> updatedTags =
new ArrayList<>();
114 if (tagNames != null) {
117 for (
int index = 0; index < tagNames.size(); index++) {
118 TagName tagName = tagNames.get(index);
119 stmt.executeUpdate(String.format(
"UPDATE tag_names SET tag_set_id = %d, rank = %d WHERE tag_name_id = %d", setID, index, tagName.
getId()));
129 tagSet =
new TagSet(setID, name, updatedTags);
134 }
catch (SQLException ex) {
136 throw new TskCoreException(String.format(
"Error adding tag set %s", name), ex);
153 if (tagSet == null) {
154 throw new IllegalArgumentException(
"Error adding deleting TagSet, TagSet object was null");
157 if (isTagSetInUse(tagSet)) {
158 throw new TskCoreException(
"Unable to delete TagSet (%d). TagSet TagName list contains TagNames that are currently in use.");
162 try (Statement stmt = trans.getConnection().createStatement()) {
163 String queryTemplate =
"DELETE FROM tag_names WHERE tag_name_id IN (SELECT tag_name_id FROM tag_names WHERE tag_set_id = %d)";
164 stmt.execute(String.format(queryTemplate, tagSet.getId()));
166 queryTemplate =
"DELETE FROM tsk_tag_sets WHERE tag_set_id = '%d'";
167 stmt.execute(String.format(queryTemplate, tagSet.getId()));
170 List<Long> tagNameIds =
new ArrayList<>();
171 for (
TagName tagName : tagSet.getTagNames()) {
172 tagNameIds.add(tagName.getId());
177 }
catch (SQLException ex) {
179 throw new TskCoreException(String.format(
"Error deleting tag set where id = %d.", tagSet.getId()), ex);
193 if (tagName == null) {
194 throw new IllegalArgumentException(
"Null tagName argument");
197 if (tagName.getTagSetId() <= 0) {
203 String sqlQuery = String.format(
"SELECT * FROM tsk_tag_sets WHERE tag_set_id = %d", tagName.getTagSetId());
204 try (CaseDbConnection connection = skCase.getConnection(); Statement stmt = connection.createStatement(); ResultSet resultSet = stmt.executeQuery(sqlQuery);) {
205 if (resultSet.next()) {
206 int setID = resultSet.getInt(
"tag_set_id");
207 String setName = resultSet.getString(
"name");
208 tagSet =
new TagSet(setID, setName, getTagNamesByTagSetID(setID));
211 }
catch (SQLException ex) {
212 throw new TskCoreException(String.format(
"Error occurred getting TagSet for TagName '%s' (ID=%d)", tagName.getDisplayName(), tagName.getId()), ex);
230 String preparedQuery =
"Select * FROM tsk_tag_sets WHERE tag_set_id = ?";
232 try (CaseDbConnection connection = skCase.getConnection(); PreparedStatement statement = connection.getPreparedStatement(preparedQuery, Statement.NO_GENERATED_KEYS)) {
233 statement.setLong(1,
id);
234 try (ResultSet resultSet = statement.executeQuery()) {
235 if (resultSet.next()) {
236 int setID = resultSet.getInt(
"tag_set_id");
237 String setName = resultSet.getString(
"name");
238 tagSet =
new TagSet(setID, setName, getTagNamesByTagSetID(setID));
242 }
catch (SQLException ex) {
243 throw new TskCoreException(String.format(
"Error occurred getting TagSet (ID=%d)",
id), ex);
265 if (artifact == null || tagName == null) {
266 throw new IllegalArgumentException(
"NULL argument passed to addArtifactTag");
269 List<BlackboardArtifactTag> removedTags =
new ArrayList<>();
270 List<String> removedTagIds =
new ArrayList<>();
275 long tagSetId = tagName.getTagSetId();
279 String selectQuery = String.format(
"SELECT * from blackboard_artifact_tags JOIN tag_names ON tag_names.tag_name_id = blackboard_artifact_tags.tag_name_id JOIN tsk_examiners on tsk_examiners.examiner_id = blackboard_artifact_tags.examiner_id WHERE artifact_id = %d AND tag_names.tag_set_id = %d", artifact.getArtifactID(), tagSetId);
281 try (Statement stmt = skCase.getConnection().createStatement(); ResultSet resultSet = stmt.executeQuery(selectQuery)) {
282 while (resultSet.next()) {
284 resultSet.getLong(
"tag_name_id"),
285 resultSet.getString(
"display_name"),
286 resultSet.getString(
"description"),
290 resultSet.getInt(
"rank")
298 resultSet.getString(
"comment"),
299 resultSet.getString(
"login_name"));
301 removedTags.add(bat);
302 removedTagIds.add(Long.toString(bat.getId()));
312 CaseDbConnection connection = trans.getConnection();
314 if (!removedTags.isEmpty()) {
316 String removeQuery = String.format(
"DELETE FROM blackboard_artifact_tags WHERE tag_id IN (%s)", String.join(
",", removedTagIds));
317 try (Statement stmt = connection.createStatement()) {
318 stmt.executeUpdate(removeQuery);
324 try (Statement stmt = connection.createStatement()) {
326 String query = String.format(
327 "INSERT INTO blackboard_artifact_tags (artifact_id, tag_name_id, comment, examiner_id) VALUES (%d, %d, '%s', %d)",
328 artifact.getArtifactID(),
331 currentExaminer.
getId());
334 stmt.execute(query, Statement.RETURN_GENERATED_KEYS);
339 try (ResultSet resultSet = stmt.getGeneratedKeys()) {
342 artifact, content, tagName, comment, currentExaminer.
getLoginName());
347 artifact.getId(), artifact.getDataSourceObjectID(), getTagScore(tagName.getKnownStatus()), trans);
352 }
catch (SQLException ex) {
356 throw new TskCoreException(
"Error adding row to blackboard_artifact_tags table (obj_id = " + artifact.getArtifactID() +
", tag_name_id = " + tagName.getId() +
")", ex);
369 switch (knownStatus) {
400 Optional<TskData.FileKnown> getMaxTagKnownStatus(
long objectId, CaseDbTransaction transaction)
throws TskCoreException {
403 String queryString =
"SELECT tag_names.knownStatus AS knownStatus\n"
405 +
" SELECT ctags.tag_name_id AS tag_name_id FROM content_tags ctags WHERE ctags.obj_id = " + objectId +
"\n"
407 +
" SELECT btags.tag_name_id AS tag_name_id FROM blackboard_artifact_tags btags \n"
408 +
" INNER JOIN blackboard_artifacts ba ON btags.artifact_id = ba.artifact_id\n"
409 +
" WHERE ba.artifact_obj_id = " + objectId +
"\n"
410 +
" ) tag_name_ids\n"
411 +
" INNER JOIN tag_names ON tag_name_ids.tag_name_id = tag_names.tag_name_id\n"
412 +
" ORDER BY tag_names.knownStatus DESC\n"
415 try (Statement statement = transaction.getConnection().createStatement();
416 ResultSet resultSet = transaction.getConnection().executeQuery(statement, queryString);) {
418 if (resultSet.next()) {
419 return Optional.ofNullable(TskData.FileKnown.valueOf(resultSet.getByte(
"knownStatus")));
421 return Optional.empty();
424 }
catch (SQLException ex) {
425 throw new TskCoreException(
"Error getting content tag FileKnown status for content with id: " + objectId);
443 List<ContentTag> removedTags =
new ArrayList<>();
444 List<String> removedTagIds =
new ArrayList<>();
447 CaseDbConnection connection = trans.getConnection();
450 long tagSetId = tagName.getTagSetId();
453 String selectQuery = String.format(
"SELECT * from content_tags JOIN tag_names ON tag_names.tag_name_id = content_tags.tag_name_id JOIN tsk_examiners on tsk_examiners.examiner_id = content_tags.examiner_id WHERE obj_id = %d AND tag_names.tag_set_id = %d", content.getId(), tagSetId);
455 try (Statement stmt = connection.createStatement(); ResultSet resultSet = stmt.executeQuery(selectQuery)) {
456 while (resultSet.next()) {
458 resultSet.getLong(
"tag_name_id"),
459 resultSet.getString(
"display_name"),
460 resultSet.getString(
"description"),
464 resultSet.getInt(
"rank")
471 resultSet.getString(
"comment"),
472 resultSet.getLong(
"begin_byte_offset"),
473 resultSet.getLong(
"end_byte_offset"),
474 resultSet.getString(
"login_name"));
476 removedTagIds.add(Long.toString(bat.getId()));
477 removedTags.add(bat);
481 if (!removedTags.isEmpty()) {
482 String removeQuery = String.format(
"DELETE FROM content_tags WHERE tag_id IN (%s)", String.join(
",", removedTagIds));
483 try (Statement stmt = connection.createStatement()) {
484 stmt.executeUpdate(removeQuery);
489 String queryTemplate =
"INSERT INTO content_tags (obj_id, tag_name_id, comment, begin_byte_offset, end_byte_offset, examiner_id) VALUES (%d, %d, '%s', %d, %d, %d)";
491 try (Statement stmt = connection.createStatement()) {
493 String query = String.format(queryTemplate,
499 currentExaminer.getId());
502 stmt.executeUpdate(query, Statement.RETURN_GENERATED_KEYS);
504 stmt.executeUpdate(query);
507 try (ResultSet resultSet = stmt.getGeneratedKeys()) {
509 contentTag =
new ContentTag(resultSet.getLong(1),
510 content, tagName, comment, beginByteOffset, endByteOffset, currentExaminer.getLoginName());
514 Long dataSourceId = content.getDataSource() != null ? content.getDataSource().
getId() : null;
516 content.getId(), dataSourceId, getTagScore(tagName.getKnownStatus()), trans);
520 }
catch (SQLException ex) {
522 throw new TskCoreException(
"Error adding row to content_tags table (obj_id = " + content.getId() +
", tag_name_id = " + tagName.getId() +
")", ex);
541 String insertQuery =
"INSERT INTO tag_names (display_name, description, color, knownStatus) VALUES (?, ?, ?, ?) ON CONFLICT (display_name) DO UPDATE SET description = ?, color = ?, knownStatus = ?";
542 boolean isUpdated =
false;
544 try (CaseDbConnection connection = skCase.getConnection()) {
545 try (PreparedStatement statement = connection.getPreparedStatement(
"SELECT * FROM tag_names WHERE display_name = ?", Statement.NO_GENERATED_KEYS)) {
546 statement.setString(1, displayName);
547 try (ResultSet resultSet = statement.executeQuery()) {
548 isUpdated = resultSet.next();
552 try (PreparedStatement statement = connection.getPreparedStatement(insertQuery, Statement.RETURN_GENERATED_KEYS);) {
553 statement.clearParameters();
554 statement.setString(5, description);
555 statement.setString(6, color.getName());
556 statement.setByte(7, knownStatus.getFileKnownValue());
557 statement.setString(1, displayName);
558 statement.setString(2, description);
559 statement.setString(3, color.getName());
560 statement.setByte(4, knownStatus.getFileKnownValue());
561 statement.executeUpdate();
564 try (PreparedStatement statement = connection.getPreparedStatement(
"SELECT * FROM tag_names where display_name = ?", Statement.NO_GENERATED_KEYS)) {
565 statement.setString(1, displayName);
566 try (ResultSet resultSet = connection.executeQuery(statement)) {
568 TagName newTag =
new TagName(resultSet.getLong(
"tag_name_id"), displayName, description, color, knownStatus, resultSet.getLong(
"tag_set_id"), resultSet.getInt(
"rank"));
579 }
catch (SQLException ex) {
580 throw new TskCoreException(
"Error adding row for " + displayName +
" tag name to tag_names table", ex);
596 String preparedQuery =
"SELECT * FROM tag_names where tag_name_id = ?";
599 try (CaseDbConnection connection = skCase.getConnection()) {
600 try (PreparedStatement statement = connection.getPreparedStatement(preparedQuery, Statement.NO_GENERATED_KEYS)) {
601 statement.clearParameters();
602 statement.setLong(1,
id);
603 try (ResultSet resultSet = statement.executeQuery()) {
604 if (resultSet.next()) {
605 return new TagName(resultSet.getLong(
"tag_name_id"),
606 resultSet.getString(
"display_name"),
607 resultSet.getString(
"description"),
610 resultSet.getLong(
"tag_set_id"),
611 resultSet.getInt(
"rank"));
615 }
catch (SQLException ex) {
636 try (CaseDbConnection connection = skCase.getConnection()) {
637 List<TagName> tagNameList = tagSet.getTagNames();
638 if (tagNameList != null && !tagNameList.isEmpty()) {
639 String statement = String.format(
"SELECT tag_id FROM content_tags WHERE tag_name_id IN (SELECT tag_name_id FROM tag_names WHERE tag_set_id = %d)", tagSet.getId());
640 try (Statement stmt = connection.createStatement(); ResultSet resultSet = stmt.executeQuery(statement)) {
641 if (resultSet.next()) {
644 }
catch (SQLException ex) {
645 throw new TskCoreException(String.format(
"Failed to determine if TagSet is in use (%s)", tagSet.getId()), ex);
648 statement = String.format(
"SELECT tag_id FROM blackboard_artifact_tags WHERE tag_name_id IN (SELECT tag_name_id FROM tag_names WHERE tag_set_id = %d)", tagSet.getId());
649 try (Statement stmt = connection.createStatement(); ResultSet resultSet = stmt.executeQuery(statement)) {
650 if (resultSet.next()) {
653 }
catch (SQLException ex) {
654 throw new TskCoreException(String.format(
"Failed to determine if TagSet is in use (%s)", tagSet.getId()), ex);
673 private List<TagName> getTagNamesByTagSetID(
int tagSetId)
throws TskCoreException {
676 throw new IllegalArgumentException(
"Invalid tagSetID passed to getTagNameByTagSetID");
679 List<TagName> tagNameList =
new ArrayList<>();
682 String query = String.format(
"SELECT * FROM tag_names WHERE tag_set_id = %d", tagSetId);
683 try (CaseDbConnection connection = skCase.getConnection(); Statement stmt = connection.createStatement(); ResultSet resultSet = stmt.executeQuery(query)) {
684 while (resultSet.next()) {
685 tagNameList.add(
new TagName(resultSet.getLong(
"tag_name_id"),
686 resultSet.getString(
"display_name"),
687 resultSet.getString(
"description"),
688 TagName.HTML_COLOR.getColorByName(resultSet.getString(
"color")),
689 TskData.FileKnown.valueOf(resultSet.getByte(
"knownStatus")),
691 resultSet.getInt(
"rank")));
693 }
catch (SQLException ex) {
694 throw new TskCoreException(String.format(
"Error getting tag names for tag set (%d)", tagSetId), ex);
708 private final List<BlackboardArtifactTag> removedTagList;
717 this.addedTag = added;
718 this.removedTagList = removed;
736 return Collections.unmodifiableList(removedTagList);
746 private final List<ContentTag> removedTagList;
755 this.addedTag = added;
756 this.removedTagList = removed;
774 return Collections.unmodifiableList(removedTagList);
static FileKnown valueOf(byte known)
TagName addOrUpdateTagName(String displayName, String description, TagName.HTML_COLOR color, TskData.FileKnown knownStatus)
static final Score SCORE_NOTABLE
CaseDbTransaction beginTransaction()
TagSet getTagSet(TagName tagName)
List< BlackboardArtifactTag > getRemovedTags()
BlackboardArtifactTagChange addArtifactTag(BlackboardArtifact artifact, TagName tagName, String comment)
Content getContentById(long id)
BlackboardArtifactTag getAddedTag()
TskData.FileKnown getKnownStatus()
TagName getTagName(long id)
List< ContentTag > getRemovedTags()
void releaseSingleUserCaseReadLock()
List< TagSet > getTagSets()
static HTML_COLOR getColorByName(String colorName)
void acquireSingleUserCaseWriteLock()
void releaseSingleUserCaseWriteLock()
TagSet getTagSet(long id)
ContentTagChange addContentTag(Content content, TagName tagName, String comment, long beginByteOffset, long endByteOffset)
Examiner getCurrentExaminer()
void acquireSingleUserCaseReadLock()
static final Score SCORE_LIKELY_NOTABLE
void deleteTagSet(TagSet tagSet)
TagSet addTagSet(String name, List< TagName > tagNames)
ScoringManager getScoringManager()