findings.go

/*
Copyright 2020 Adevinta
*/

package store

import (
	"context"
	"database/sql"
	"encoding/json"
	"errors"
	"fmt"
	"sort"
	"strings"
	"time"

	"github.com/jmoiron/sqlx"
	"github.com/lib/pq"
	log "github.com/sirupsen/logrus"
)

const (
	FindingStatusOpen          = "OPEN"
	FindingStatusFixed         = "FIXED"
	FindingStatusExpired       = "EXPIRED"
	FindingStatusFalsePositive = "FALSE_POSITIVE"
	FindingStatusNew           = "NEW"
	FindingStatusInvalidated   = "INVALIDATED"
	FindingDefaultFingerprint  = "NOT_PROVIDED"

	dateTimeFmt = "2006-01-02 15:04:05"
)

var (
	// ErrParsingFinding indicates an error when parsing finding data retrieved from database.
	ErrParsingFinding = errors.New("error parsing finding data")
)

// Finding represents the finding of a
// vulnerability in a target.
type Finding struct {
	ID                     string  `json:"id" db:"id"`
	IssueID                string  `json:"-" db:"issue_id"`
	TargetID               string  `json:"-" db:"target_id"`
	AffectedResource       string  `json:"affected_resource" db:"affected_resource"`
	AffectedResourceString string  `json:"-" db:"affected_resource_string"`
	Fingerprint            string  `json:"-" db:"fingerprint"`
	Score                  float64 `json:"score" db:"score"`
	Status                 string  `json:"status" db:"status"`
	Details                string  `json:"details" db:"details"`
	ImpactDetails          string  `json:"impact_details" db:"impact_details"`
	// Resources contains the vulnerability resources tables mashalled into a
	// json.
	Resources *[]byte `json:"-"`
}

// FindingEvent is an event related to a finding
// which can indicate the finding has been found
// or it has been fixed.
type FindingEvent struct {
	ID                     string  `db:"id"`
	FindingID              string  `db:"finding_id"`
	SourceID               string  `db:"source_id"`
	Score                  float64 `db:"score"`
	Details                *string `db:"details"`
	Fingerprint            string  `db:"fingerprint"`
	AffectedResourceString *string `db:"affected_resource_string"`
	// Resources contains the vulnerability resources tables mashalled into a
	// json.
	Resources *[]byte   `db:"resources"`
	Time      time.Time `db:"time"`
}

// FindingExposure represents a period of time in which a finding has been
// continuosly detected.
type FindingExposure struct {
	FindingID string     `db:"finding_id"`
	FoundAT   time.Time  `db:"found_at"`
	FixedAT   *time.Time `db:"fixed_at"`
	TTR       *int       `db:"fixed_at"`
}

// FindingExpanded represents a finding expanding the associated target, issue
// and source data.
type FindingExpanded struct {
	Finding
	Issue           IssueLabels `json:"issue"`
	Target          TargetTeams `json:"target"`
	Source          Source      `json:"source"`
	Resources       Resources   `json:"resources"`
	TotalExposure   int64       `json:"total_exposure"`
	CurrentExposure int64       `json:"current_exposure,omitempty"`
}

// Resources defines the structure of a the resources of a finding.
type Resources []ResourceGroup

// ResourceGroup reprents a resource in a finding.
type ResourceGroup struct {
	Name       string              `json:"name"`
	Attributes []string            `json:"attributes"`
	Resources  []map[string]string `json:"resources"`
}

// FindingState represents a state update for a finding.
type FindingState struct {
	ID                     string
	Exposure               []FindingExposure
	Status                 string
	Score                  float64
	Fingerprint            string
	AffectedResourceString *string
	Details                *string
	Resources              *[]byte
}

type sourceFindings struct {
	FindingID              *string   `db:"finding_id"`
	SourceID               string    `db:"source_id"`
	SourceTime             time.Time `db:"source_time"`
	Resources              *[]byte   `db:"resources"`
	Details                *string   `db:"details"`
	Score                  *float64  `db:"score"`
	Fingerprint            *string   `db:"fingerprint"`
	AffectedResourceString *string   `db:"affected_resource_string"`
	CurrentStatus          *string   `db:"status"`
}

type findingData struct {
	Events   []FindingEvent
	Found    map[string]struct{}
	NotFound []Source
}

type timeEvent struct {
	T                      time.Time
	score                  float64
	fingerprint            string
	affectedResourceString *string
	details                *string
	resources              *[]byte
	found                  bool
}

type timeline []timeEvent

func (t timeline) findNext(i int, cond func(timeEvent) bool) (int, bool) {
	for ; i < len(t); i++ {
		if cond(t[i]) {
			break
		}
	}
	found := i < len(t)
	return i, found
}

func (t timeline) LastFoundAttributes() (float64, *string, *[]byte, string, *string) {
	var score float64
	var fingerprint string
	var affectedResourceString *string
	var details *string
	var resources *[]byte
	for _, s := range t {
		if s.found {
			score = s.score
			details = s.details
			resources = s.resources
			fingerprint = s.fingerprint
			affectedResourceString = s.affectedResourceString
		}
	}
	return score, details, resources, fingerprint, affectedResourceString
}

// CreateFinding creates a new finding and also a new fiding event for that finding.
func (db *psqlxStore) CreateFinding(eventTime time.Time, f Finding, sourceID string) (*Finding, error) {
	tx, err := db.DB.Beginx()
	if err != nil {
		return nil, err
	}

	// Create finding.
	_, err = tx.Exec("INSERT INTO findings (issue_id, target_id, affected_resource, affected_resource_string, fingerprint, score, status, details, resources) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)",
		f.IssueID, f.TargetID, f.AffectedResource, f.AffectedResourceString, f.Fingerprint, f.Score, f.Status, f.Details, sanitizeJSONB(f.Resources))
	if err != nil {
		tx.Rollback()
		return nil, err
	}

	// Retrieve.
	finding := Finding{}
	err = tx.Get(&finding, "SELECT * FROM findings WHERE issue_id = $1 AND target_id = $2 AND affected_resource = $3 ", f.IssueID, f.TargetID, f.AffectedResource)
	if err != nil {
		tx.Rollback()
		return nil, err
	}

	// Create finding event.
	_, err = tx.Exec("INSERT INTO finding_events (finding_id, source_id, time) VALUES ($1, $2, $3)",
		finding.ID, sourceID, eventTime)
	if err != nil {
		tx.Rollback()
		return nil, err
	}

	if err = tx.Commit(); err != nil {
		tx.Rollback()
		return nil, err
	}

	return &finding, nil
}

func (db *psqlxStore) FindFinding(f Finding) (*Finding, error) {
	foundFinding := Finding{}
	err := db.DB.Get(&foundFinding, "SELECT * FROM findings WHERE issue_id = $1 AND target_id = $2 AND affected_resource = $3", f.IssueID, f.TargetID, f.AffectedResource)
	if err != nil {
		return nil, err
	}

	return &foundFinding, nil
}

func (db *psqlxStore) GetLastFindingEvent(findingID string) (*FindingEvent, error) {
	event := FindingEvent{}
	err := db.DB.Get(&event, "SELECT * FROM finding_events WHERE finding_id = $1 ORDER BY TIME DESC LIMIT 1", findingID)
	if err != nil {
		return nil, err
	}

	return &event, nil
}

func (db *psqlxStore) GetFindingsExpanded(ids []string) ([]FindingExpanded, error) {
	query := `SELECT f.id AS finding_id, s.id AS source_id,
	f.issue_id, f.target_id, f.affected_resource, f.affected_resource_string, f.status, f.details, f.impact_details, f.resources, f.score,
	s.name, s.component, s.instance, s.options, s.time,
	i.*, t.*,
	(SELECT ARRAY_AGG(tt.team_id) FROM target_teams tt WHERE t.id = tt.target_id) teams,
	(SELECT ARRAY_AGG(fexp.found_at) FROM finding_exposures fexp WHERE fexp.finding_id = f.id) as found_at,
	(SELECT ARRAY_AGG(fexp.fixed_at) FROM finding_exposures fexp WHERE fexp.finding_id = f.id) as fixed_at,
	(SELECT ARRAY_AGG(fexp.expired_at) FROM finding_exposures fexp WHERE fexp.finding_id = f.id) as expired_at,
	(SELECT ARRAY_AGG(il.label) FROM issue_labels il WHERE il.issue_id = f.issue_id) labels
	FROM findings f
	INNER JOIN issues i ON f.issue_id = i.id
	INNER JOIN targets t ON f.target_id = t.id
	INNER JOIN finding_events fe ON fe.finding_id = f.id
	INNER JOIN sources s ON fe.source_id = s.id
	WHERE f.id = ANY ($1)
	AND fe.id IN (
		SELECT id FROM finding_events
		WHERE finding_id = f.id ORDER BY time DESC LIMIT 1
	)`
	rows, err := db.DB.Queryx(query, pq.Array(ids))
	if err == sql.ErrNoRows {
		return []FindingExpanded{}, nil
	}
	if err != nil {
		return nil, err
	}

	var findings []FindingExpanded
	for rows.Next() {
		row := make(map[string]any)
		err = rows.MapScan(row)
		if err != nil {
			return nil, err
		}
		f, err := buildFindingExpanded(row)
		if err != nil {
			return nil, err
		}
		findings = append(findings, f)
	}

	return findings, nil
}

// CreateFindingEvent creates a new finding event . Returns finding after event
// insert.
func (db *psqlxStore) CreateFindingEvent(eventTime time.Time, findingID, sourceID string, score float64, fingerprint, affectedResourceString string) (*Finding, error) {
	tx, err := db.DB.Beginx()
	if err != nil {
		return nil, err
	}

	_, err = tx.Exec("INSERT INTO finding_events (finding_id, source_id, time, score, fingerprint, affected_resource_string) VALUES ($1, $2, $3, $4, $5, $6)",
		findingID, sourceID, eventTime, score, fingerprint, affectedResourceString)
	if err != nil {
		tx.Rollback()
		return nil, err
	}

	// Get finding after update.
	finding := Finding{}
	err = tx.Get(&finding, "SELECT * FROM findings WHERE id = $1", findingID)
	if err != nil {
		tx.Rollback()
		return nil, err
	}

	tx.Commit()

	return &finding, nil
}

// ExpireFindings sets status as EXPIRED for findings that are
// currently open and haven't been found for the last ttl hours.
// Also sets expired_at field in finding_exposures table for the
// finding's last exposure row to NOW() time.
// Returns the number of findings expired.
func (db *psqlxStore) ExpireFindings(source string, ttl int) (int64, error) {
	ctx := context.Background()
	tx, err := db.DB.BeginTxx(ctx, nil)
	if err != nil {
		return 0, err
	}

	// Set findings status to EXPIRED, for vulcan
	// discovered findings which are currently OPEN
	// and their last found event was > ttl days ago.
	findingsQuery := `
	WITH last_events AS (
		SELECT f.id AS finding_id, MAX(fe.time) AS time
		FROM findings f
		INNER JOIN finding_events fe
		ON fe.finding_id = f.id
		INNER JOIN sources s
		ON fe.source_id = s.id
		WHERE f.status = 'OPEN'
		AND s.name = $1
		GROUP BY f.id
	)
	UPDATE findings f
	SET status = 'EXPIRED'
	WHERE f.id IN (
		SELECT  le.finding_id
		FROM last_events le
		WHERE age(le.time) > $2
	)`
	ttlDays := fmt.Sprintf("%d days", ttl)
	findingsRes, err := tx.Exec(findingsQuery, source, ttlDays)
	if err != nil {
		tx.Rollback()
		return 0, err
	}

	// Set finding_exposure expired_at field to
	// NOW() for all findings that have just been
	// expired.
	fexposuresQuery := `
	WITH expired_findings AS (
		SELECT f.id AS finding_id
		FROM findings f
		INNER JOIN finding_exposures fexp
		ON f.id = fexp.finding_id
		WHERE f.status = 'EXPIRED'
		AND fexp.expired_at IS NULL
	)
	UPDATE finding_exposures
	SET expired_at = NOW()
	WHERE finding_id IN (
		SELECT  ef.finding_id
		FROM expired_findings ef
	)
	`
	_, err = tx.Exec(fexposuresQuery)
	if err != nil {
		tx.Rollback()
		return 0, err
	}

	if err = tx.Commit(); err != nil {
		return 0, err
	}

	return findingsRes.RowsAffected()
}

func (db *psqlxStore) RecalculateFindingsStatus(s SourceFamily) error {
	ctx := context.Background()
	tx, err := db.DB.BeginTxx(ctx, nil)
	if err != nil {
		return err
	}

	relatedFamilies, err := db.relatedFamilies(s)
	if err != nil {
		return err
	}

	err = db.lockTxBySources(tx, relatedFamilies)
	if err != nil {
		return err
	}

	findingStates, err := findingsStates(ctx, tx, db.logger, s)
	if err != nil {
		tx.Rollback()
		return err
	}

	err = replaceFindingsState(ctx, tx, db.logger, findingStates)
	if err != nil {
		tx.Rollback()
		return err
	}
	return tx.Commit()
}

// createFindingEvent creates a finding and/or the associated finding event if they do not exist.
func createFindingEvent(ctx context.Context, tx *sqlx.Tx, f Finding, s Source, score float32, resources *[]byte, details string, fingerprint, affectedResourceString string) (*FindingEvent, error) {
	// Ensure finding for the finding event exists.
	q := `
	WITH  q as (
	SELECT * FROM findings
	WHERE issue_id=$1 AND target_id=$2 AND affected_resource=$3
	),
	c AS (
	INSERT INTO findings (issue_id, target_id, affected_resource, affected_resource_string, fingerprint, score, status, details, impact_details, resources)
	SELECT $1, $2, $3, $4, $5, $6, $7, $8, $9, $10
	WHERE NOT EXISTS (SELECT 1 FROM q)
	RETURNING *
	)
	SELECT id FROM c
	UNION ALL
	SELECT id FROM q
	`
	err := tx.Get(&f, q, f.IssueID, f.TargetID, f.AffectedResource, affectedResourceString, f.Fingerprint, score, f.Status, f.Details, f.ImpactDetails, sanitizeJSONB(f.Resources))
	if err != nil {
		return nil, err
	}

	// Create the finding event if not exists.
	var findingEvent FindingEvent
	r := tx.QueryRowxContext(ctx, `
		WITH ins AS (
			INSERT INTO finding_events
			(finding_id, source_id, time, score, resources, details, fingerprint, affected_resource_string)
			VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
			ON CONFLICT ON CONSTRAINT unique_finding_events DO UPDATE
			SET finding_id = NULL -- never executed
			WHERE FALSE
			RETURNING *
		)
		SELECT *
		FROM ins
		UNION ALL
		SELECT *
		FROM finding_events
		WHERE finding_id = $1 AND source_id = $2 and time = $3`,
		f.ID, s.ID, s.Time, score, sanitizeJSONB(f.Resources), f.Details, f.Fingerprint, affectedResourceString)
	err = r.StructScan(&findingEvent)
	return &findingEvent, err
}

func findingsStates(ctx context.Context, tx *sqlx.Tx, log *log.Logger, s SourceFamily) ([]FindingState, error) {
	findingsQ := `
		WITH relevant_sources AS(
			SELECT s.*
			FROM sources s
			WHERE s.target_id = $3 AND s.component IN (
				SELECT s.component
				FROM sources s INNER JOIN source_issues si ON si.source_id = s.id
				WHERE s.target_id = $3 AND si.issue_id IN (
					SELECT si.issue_id FROM source_issues si
					INNER JOIN sources s ON si.source_id = s.id
					WHERE name=$1 AND component=$2  AND target_id = $3
				)
			)
		)
		SELECT fe.finding_id as finding_id, fe.score as score, fe.fingerprint as fingerprint,
		fe.affected_resource_string as affected_resource_string, fe.details as details,
		fe.resources as resources, s.id as source_id, s.time as source_time,
		f.status as status
		FROM relevant_sources s
		LEFT JOIN finding_events fe ON fe.source_id=s.id
		LEFT JOIN findings f ON f.id=fe.finding_id
		ORDER BY fe.finding_id, s.time;
	`

	// The query returns all the sources of any family that are capable of detecting any issue
	// ever found for the target by a source belonging to the family of the source we are processing.
	// Along with the source data, it retrieves the finding events, if any, sorted by finding_id and time.
	//
	// The 'relevant_sources' query gets all the sources that have been executed against the specified
	// target which are in the list of all the sources that have ever detected an issue from the whole
	// list of issues ever detected by the source we are processing.
	//
	// Example of data returned:
	// 		           finding_id              | score |              source_id               |     source_time
	// 	--------------------------------------+-------+--------------------------------------+---------------------
	// 	 509b7663-7467-4bf4-95df-495aadcf70ac |   3.9 | ee51f3eb-cb59-474e-b6bd-449bf08990c8 | 2019-06-08 09:32:08
	// 	 509b7663-7467-4bf4-95df-495aadcf70ac |   3.9 | 8b912b11-b66b-4b79-9acd-d1a47305162b | 2019-10-08 08:22:30
	// 	 85ccdcc8-edbb-4691-be7b-32d9dd24b696 |   3.9 | 8b912b11-b66b-4b79-9acd-d1a47305162b | 2019-10-08 08:22:30
	// 	 9ce52ab3-35ff-4c54-ad1c-e8b2b9d73e71 |   3.9 | ee51f3eb-cb59-474e-b6bd-449bf08990c8 | 2019-06-08 09:32:08
	// 	 9ce52ab3-35ff-4c54-ad1c-e8b2b9d73e71 |   3.9 | 8b912b11-b66b-4b79-9acd-d1a47305162b | 2019-10-08 08:22:30
	// 	                                      |       | 8b912b11-b66b-4b79-9acd-d1a47305162b | 2019-10-08 08:22:30
	// 	                                      |       | ee51f3eb-cb59-474e-b6bd-449bf08990c8 | 2019-06-08 09:32:08

	args := []interface{}{s.Name, s.Component, s.Target}
	logQuery(log, "FindingStates", findingsQ, args...)
	sourceF := []sourceFindings{}
	err := tx.Select(&sourceF, findingsQ, args...)
	if err != nil && !IsNotFoundErr(err) {
		return nil, err
	}

	states := buildFindingStates(sourceF)
	return states, nil
}

func buildFindingStates(sourceF []sourceFindings) []FindingState {
	if len(sourceF) == 0 {
		return nil
	}
	var (
		findings             = make(map[string]findingData)
		findingCurrentStatus = make(map[string]string)
		sources              = make(map[string]Source, 0)
		fes                  []FindingEvent
		f                    string
		found                = make(map[string]struct{})
		// Data structures to handle migration from
		// old model findings to the new model
		oldModelFindings = make(map[string]struct{})
		newModelFindings = make(map[string]struct{})
		isNewModelSource bool
	)
	// We build a map with all the findings together with their finding
	// events.
	for _, sf := range sourceF {
		if _, ok := sources[sf.SourceID]; !ok {
			sources[sf.SourceID] = Source{
				ID:   sf.SourceID,
				Time: sf.SourceTime,
			}
		}

		// If sourceFinding is reporting a non default fingerprint
		// we know that check has been migrated to new model
		if sf.Fingerprint != nil &&
			*sf.Fingerprint != FindingDefaultFingerprint {
			isNewModelSource = true
		}

		if sf.FindingID == nil {
			continue
		}

		// Keep track of findings that have old and new
		// model finding events associated
		if sf.Fingerprint != nil {
			if *sf.Fingerprint == FindingDefaultFingerprint {
				oldModelFindings[*sf.FindingID] = struct{}{}
			} else {
				newModelFindings[*sf.FindingID] = struct{}{}
			}
		}

		findingCurrentStatus[*sf.FindingID] = "UNKNOWN"
		if sf.CurrentStatus != nil {
			findingCurrentStatus[*sf.FindingID] = *sf.CurrentStatus
		}
		if *sf.FindingID != f {
			if f != "" {
				findings[f] = findingData{
					Found:  found,
					Events: fes,
				}
			}
			f = *sf.FindingID
			fes = make([]FindingEvent, 0)
			found = make(map[string]struct{})
		}
		fes = append(fes, FindingEvent{
			FindingID:              f,
			Time:                   sf.SourceTime,
			SourceID:               sf.SourceID,
			Score:                  *sf.Score,
			Details:                sf.Details,
			Resources:              sf.Resources,
			Fingerprint:            *sf.Fingerprint,
			AffectedResourceString: sf.AffectedResourceString,
		})
		found[sf.SourceID] = struct{}{}
	}

	if len(fes) > 0 {
		findings[f] = findingData{
			Found:  found,
			Events: fes,
		}
	}

	// Fill, per each finding, the list of sources that have not found it.
	for id, f := range findings {
		notFound := make([]Source, 0)
		for _, s := range sources {
			_, ok := f.Found[s.ID]
			if !ok {
				notFound = append(notFound, s)
			}
		}
		sort.Slice(notFound, func(i, j int) bool {
			return notFound[i].Time.Before(notFound[j].Time)
		})
		f.NotFound = notFound
		findings[id] = f
	}

	// Build the findings state from their timeline.
	var findingStates = make([]FindingState, 0)
	notFoundF := func(e timeEvent) bool { return !e.found }
	foundF := func(e timeEvent) bool { return e.found }
	for id, f := range findings {
		var exposures = make([]FindingExposure, 0)
		tl := buildFindingTimeline(f)
		// Skip all the events at the beggining of the timeline that didn't
		// find the issue.
		i, _ := tl.findNext(0, foundF)
		// Create the first finding exposure that must exist always.
		fe := &FindingExposure{
			FindingID: id,
			FoundAT:   tl[i].T,
		}

		i++
		findFor := notFoundF
		for ; i < len(tl); i++ {
			var ok bool
			i, ok = tl.findNext(i, findFor)
			if !ok {
				break
			}
			if !tl[i].found {
				fe.FixedAT = &tl[i].T
				ttr := int(tl[i].T.Sub(fe.FoundAT).Hours())
				fe.TTR = &ttr
				exposures = append(exposures, *fe)
				findFor = foundF
				fe = nil
				continue
			}
			fe = &FindingExposure{
				FindingID: id,
				FoundAT:   tl[i].T,
			}
			findFor = notFoundF
		}
		if fe != nil {
			exposures = append(exposures, *fe)
		}

		status := FindingStatusOpen
		if exposures[len(exposures)-1].FixedAT != nil {
			status = FindingStatusFixed

			// Handle transition from old model findings to new model:
			// - New model source executions report specific affected_resource
			//   for each scanned target.
			// - In the cases for which the newly reported affected_resource
			//   does not match the old finding target, a new source with no
			//   finding event associated will be stored, representing this way
			//   a negative detection for the old finding. This will imply a change
			//   of status to FIXED for the old model finding.
			// - In the cases for which the newly reported affected_resource
			//   matches the old finding target, in case of detection, a new
			//   finding event following new model schema will be stored continuing
			//   with the lifecycle from old model finding.
			//
			// If a finding has associated finding events following old model schema
			// and the source associated with those finding events is reporting data
			// following the new model schema -> Set finding status to INVALIDATED, as
			// that finding should now be represented by multiple new findings referencing
			// specific affected resources within the old finding target.
			//
			// Exceptions to this invalidation process apply for findings that comply
			// with 3rd point mentioned previously on which new model findings affected
			// resource matches with old model target so old model finding lifecycle has
			// been continued with new model finding events.

			_, isOldModelFinding := oldModelFindings[id]
			_, isNewModelFinding := newModelFindings[id]
			currStatus := findingCurrentStatus[id]

			if isOldModelFinding && !isNewModelFinding && isNewModelSource &&
				(currStatus == FindingStatusOpen || currStatus == FindingStatusInvalidated) {
				status = FindingStatusInvalidated
			}
		}

		score, details, resources, fingerprint, affectedResourceString := tl.LastFoundAttributes()
		state := FindingState{
			Exposure:               exposures,
			ID:                     id,
			Score:                  score,
			Fingerprint:            fingerprint,
			AffectedResourceString: affectedResourceString,
			Details:                details,
			Resources:              resources,
			Status:                 status,
		}
		findingStates = append(findingStates, state)
	}
	return findingStates
}

// buildFindingTimeline returns the timeline for a finding given its findingData
// The findingData has two slices, one contains the times when the finding was
// found (field f.Events) and the other contains the times when a source that is
// able to detect the finding didn't find it, field f.NotFound. The results is
// just a slice of tuples containing the tuples {time,found} sorted by time.
func buildFindingTimeline(f findingData) timeline {
	var tl = make([]timeEvent, 0)
	var i, j int
	for i < len(f.Events) || j < len(f.NotFound) {
		var tf, tnf *time.Time
		var score float64
		var details *string
		var resources *[]byte
		var fingerprint string
		var affectedResourceString *string
		if i < len(f.Events) {
			tf = &f.Events[i].Time
			score = f.Events[i].Score
			details = f.Events[i].Details
			resources = f.Events[i].Resources
			fingerprint = f.Events[i].Fingerprint
			affectedResourceString = f.Events[i].AffectedResourceString
		}
		if j < len(f.NotFound) {
			tnf = &f.NotFound[j].Time
		}
		if tf != nil && tnf != nil {
			if tf.Before(*tnf) {
				tl = append(tl, timeEvent{
					T:                      *tf,
					found:                  true,
					score:                  score,
					details:                details,
					resources:              resources,
					fingerprint:            fingerprint,
					affectedResourceString: affectedResourceString,
				})
				i++
			} else {
				tl = append(tl, timeEvent{
					T:     *tnf,
					found: false,
				})
				j++
			}
			continue
		}
		if tf != nil {
			tl = append(tl, timeEvent{
				T:                      *tf,
				found:                  true,
				score:                  score,
				details:                details,
				resources:              resources,
				fingerprint:            fingerprint,
				affectedResourceString: affectedResourceString,
			})
			i++
			continue
		}
		tl = append(tl, timeEvent{
			T:     *tnf,
			found: false,
		})
		j++
	}
	return tl
}

func replaceFindingsState(ctx context.Context, tx *sqlx.Tx, l *log.Logger, states []FindingState) error {
	qd := "DELETE FROM finding_exposures where finding_id = $1"
	di := "INSERT INTO finding_exposures(finding_id,found_at,fixed_at,ttr) VALUES ($1, $2, $3, $4)"
	qF := `UPDATE findings SET
			status=(CASE WHEN findings.status IS NOT NULL AND findings.status = 'FALSE_POSITIVE' AND findings.fingerprint = $5 THEN findings.status ELSE $1 END),
			score=$2, resources=COALESCE($3, resources), details=COALESCE($4, details), fingerprint=COALESCE($5, fingerprint),
			affected_resource_string=COALESCE($6, affected_resource_string)
			WHERE id=$7`
	for _, s := range states {
		_, err := tx.Exec(qF, s.Status, s.Score, s.Resources, s.Details, s.Fingerprint, s.AffectedResourceString, s.ID)
		if err != nil {
			return err
		}
		// Update the state of the finding: fingerprint, score, affected_resource_string and status.
		l.WithFields(log.Fields{
			"finding_id":               s.ID,
			"score":                    s.Score,
			"fingerprint":              s.Fingerprint,
			"affected_resource_string": s.AffectedResourceString,
		}).Infof("updating finding state")
		_, err = tx.Exec(qd, s.ID)
		if err != nil {
			return err
		}
		for _, exp := range s.Exposure {
			_, err := tx.Exec(di, s.ID, exp.FoundAT, exp.FixedAT, exp.TTR)
			if err != nil {
				return err
			}
		}
	}
	return nil
}

// sanitizeJSONB sanitizes a JSON string to comply with PostgreSQL
// restrictions for unicode escape sequences, particularly for the
// NULL character (\u0000).
// See doc: https://www.postgresql.org/docs/10/datatype-json.html.
func sanitizeJSONB(jsonb *[]byte) *[]byte {
	if jsonb == nil {
		return jsonb
	}
	sanitized := []byte(strings.Replace(string(*jsonb), "\\u0000", "", -1))
	return &sanitized
}

// buildFindingExpanded builds a finding notification.
// Note: This function and associated ones are copied and adapted from vulnerability-db-api.
func buildFindingExpanded(row map[string]interface{}) (FindingExpanded, error) {
	var f FindingExpanded
	atTime := time.Now()

	findingID, ok := row["finding_id"].(string)
	if !ok {
		return FindingExpanded{}, ErrParsingFinding
	}
	f.ID = findingID

	issueID, ok := row["issue_id"].(string)
	if !ok {
		return FindingExpanded{}, ErrParsingFinding
	}
	f.Issue.ID = issueID

	summary, ok := row["summary"].(string)
	if !ok {
		return FindingExpanded{}, ErrParsingFinding
	}
	f.Issue.Summary = summary

	CWEID, ok := row["cwe_id"].(int64)
	if !ok {
		return FindingExpanded{}, ErrParsingFinding
	}
	f.Issue.CWEID = uint32(CWEID)

	description, ok := row["description"].(string)
	if !ok {
		return FindingExpanded{}, ErrParsingFinding
	}
	f.Issue.Description = description

	recommendations, err := parseStringArrayNotNil(row["recommendations"])
	if err != nil {
		return FindingExpanded{}, ErrParsingFinding
	}
	f.Issue.Recommendations = recommendations

	referenceLinks, err := parseStringArrayNotNil(row["reference_links"])
	if err != nil {
		return FindingExpanded{}, ErrParsingFinding
	}
	f.Issue.ReferenceLinks = referenceLinks

	labels, ok := row["labels"]
	if !ok {
		return FindingExpanded{}, ErrParsingFinding
	}
	if labels != nil {
		labels, ok := row["labels"].([]uint8)
		if !ok {
			return FindingExpanded{}, ErrParsingFinding
		}
		f.Issue.Labels = parseStringArray(labels)
	}

	targetID, ok := row["target_id"].(string)
	if !ok {
		return FindingExpanded{}, ErrParsingFinding
	}
	f.Target.ID = targetID

	affectedResource, ok := row["affected_resource"].(string)
	if !ok {
		return FindingExpanded{}, ErrParsingFinding
	}
	f.AffectedResource = affectedResource

	affectedResourceString, ok := row["affected_resource_string"]
	if !ok {
		return FindingExpanded{}, ErrParsingFinding
	}
	if affectedResourceString != nil {
		affectedResourceString, ok := affectedResourceString.(string)
		if !ok {
			return FindingExpanded{}, ErrParsingFinding
		}
		// NOTE: The problem with the affected resource is that in some cases
		// it might not be human-readable, and that was the reason to introduce
		// the affected resource string field.
		// Given that clients of the vulnerability-db-api are using the
		// affected resource field just as information to be shown to users,
		// and therefore there are no specific API queries using that field as
		// a parameter, the API will return the human-readable version of the
		// affected resource field when existing.
		// The reason is to avoid propagating the new field to the clients
		// (e.g. vulcan-api or vulcan-ui) if not required.
		if affectedResourceString != "" {
			f.AffectedResource = affectedResourceString
		}
	}

	identifier, ok := row["identifier"].(string)
	if !ok {
		return FindingExpanded{}, ErrParsingFinding
	}
	f.Target.Identifier = identifier

	teams, ok := row["teams"]
	if !ok {
		return FindingExpanded{}, ErrParsingFinding
	}
	if teams != nil {
		teams, ok := row["teams"].([]uint8)
		if !ok {
			return FindingExpanded{}, ErrParsingFinding
		}
		f.Target.Teams = parseStringArray(teams)
	}

	sourceID, ok := row["source_id"].(string)
	if !ok {
		return FindingExpanded{}, ErrParsingFinding
	}
	f.Source.ID = sourceID

	name, ok := row["name"].(string)
	if !ok {
		return FindingExpanded{}, ErrParsingFinding
	}
	f.Source.Name = name

	component, ok := row["component"].(string)
	if !ok {
		return FindingExpanded{}, ErrParsingFinding
	}
	f.Source.Component = component

	instance, ok := row["instance"].(string)
	if !ok {
		return FindingExpanded{}, ErrParsingFinding
	}
	f.Source.Instance = instance

	options, ok := row["options"].(string)
	if !ok {
		return FindingExpanded{}, ErrParsingFinding
	}
	f.Source.Options = options

	time, ok := row["time"].(time.Time)
	if !ok {
		return FindingExpanded{}, ErrParsingFinding
	}
	f.Source.Time = time // Serialized to ISO8601 compliant format 2006-01-02T15:04:05Z

	details, ok := row["details"].(string)
	if !ok {
		return FindingExpanded{}, ErrParsingFinding
	}
	f.Details = details

	impactDetails, ok := row["impact_details"].(string)
	if !ok {
		return FindingExpanded{}, ErrParsingFinding
	}
	f.ImpactDetails = impactDetails

	status, ok := row["status"].(string)
	if !ok {
		return FindingExpanded{}, ErrParsingFinding
	}
	f.Status = status

	score, ok := row["score"].(float64)
	if !ok {
		return FindingExpanded{}, ErrParsingFinding
	}
	f.Score = score

	var resourcesContent []byte
	if row["resources"] != nil {
		var ok bool
		resourcesContent, ok = row["resources"].([]byte)
		if !ok {
			return FindingExpanded{}, errors.New("unexpected resources type")
		}
	}
	resources, err := parseFindingResources(resourcesContent)
	if err != nil {
		return FindingExpanded{}, err
	}
	f.Resources = resources

	found, ok := row["found_at"].([]uint8)
	if !ok {
		return FindingExpanded{}, ErrParsingFinding
	}
	foundAt := parseStringArray(found)

	fixed, ok := row["fixed_at"].([]uint8)
	if !ok {
		return FindingExpanded{}, ErrParsingFinding
	}
	fixedAt := parseStringArray(fixed)

	expired, ok := row["expired_at"].([]uint8)
	if !ok {
		return FindingExpanded{}, ErrParsingFinding
	}
	expiredAt := parseStringArray(expired)

	// Don't calculate finding exposure if the finding was marked as a FALSE POSITIVE.
	// A finding marked as false positive must not impact metrics.
	if f.Status != FindingStatusFalsePositive {
		err = fillFindingExposures(&f, foundAt, fixedAt, expiredAt, atTime)
		if err != nil {
			return FindingExpanded{}, err
		}
	}

	return f, nil
}

// fillFindingExposures calculates and modifies finding exposures for input finding
// at the time specified by atTime parameter.
func fillFindingExposures(f *FindingExpanded, foundAt, fixedAt, expiredAt []string, atTime time.Time) error {
	if f == nil {
		return errors.New("the finding to fill the exposure can not be nil")
	}
	if len(foundAt) != len(fixedAt) {
		return errors.New("finding exposures time bounds expected to have the same len")
	}

	var (
		total   float64
		current float64
	)

	for i, found := range foundAt {
		// Parse found time.
		foundT, err := parseDBTime(found)
		if err != nil {
			return err
		}
		if foundT.After(atTime) {
			// If found time is after reference time
			// do not process this entry.
			continue
		}

		// Parse fixed time.
		var fixedT time.Time
		if fixedAt[i] != "NULL" {
			fixedT, err = parseDBTime(fixedAt[i])
			if err != nil {
				return err
			}
		}

		// Parse expired time.
		var expiredT time.Time
		if expiredAt[i] != "NULL" {
			expiredT, err = parseDBTime(expiredAt[i])
			if err != nil {
				return err
			}
		}

		// If finding is expired and time reference is
		// posterior to expiration date, set current
		// exposure to -1 and do not add to total exposure.
		if !expiredT.IsZero() && atTime.After(expiredT) {
			current = -1
			continue
		}

		// If finding is expired and time reference is before
		// expiration date, or finding is open, or finding is
		// fixed but was fixed after time reference, then only
		// count exposure up to time reference.
		if (!expiredT.IsZero() && atTime.Before(expiredT)) ||
			(expiredT.IsZero() && (fixedT.IsZero() || fixedT.After(atTime))) {
			fixedT = atTime
			current = fixedT.Sub(foundT).Hours()
		}

		// Add to total exposure.
		total = total + fixedT.Sub(foundT).Hours()
	}

	f.TotalExposure = int64(total)
	if current > 0 {
		f.CurrentExposure = int64(current)
	}
	return nil
}

// parseDBTime parses a datetime string retrieved from DB.
func parseDBTime(dbTime string) (time.Time, error) {
	return time.Parse(dateTimeFmt, strings.Replace(dbTime, "\"", "", -1))
}

func parseStringArrayNotNil(row interface{}) ([]string, error) {
	if row == nil {
		return nil, nil
	}
	safe, ok := row.([]uint8)
	if !ok {
		return nil, ErrParsingFinding
	}
	return parseStringArray(safe), nil
}

// parseStringArray parses a string array retrieved from database
// stripping the initial and trailing brackets and splitting elements
// separated by comma.
func parseStringArray(array []uint8) []string {
	// Void array case.
	if len(array) <= 2 {
		return []string{}
	}

	// Remove JSON brackets first.
	auxString := string(array[1 : len(array)-1])

	var splitter string
	if len(auxString) > 2 && auxString[0] == '"' &&
		!strings.Contains(auxString, "NULL") {
		// Strings are separated by "\",\""
		// so remove first and last quote and
		// split by it.
		auxString = auxString[1 : len(auxString)-1]
		splitter = "\",\""
	} else {
		// Strings are separated by ',' with
		// no quotes between them, or it's a string
		// like: ""2019-01-01",NULL".
		splitter = ","
	}
	return strings.Split(auxString, splitter)
}

func parseFindingResources(content []byte) (Resources, error) {
	var resources = make([]ResourceGroup, 0)
	if content == nil {
		return resources, nil
	}
	err := json.Unmarshal(content, &resources)
	if err != nil {
		return nil, err
	}
	return resources, nil
}