cvecase.go

package alertmanager

import (
	"encoding/xml"
	"github.com/huandu/go-sqlbuilder"
	"github.com/jmoiron/sqlx"
	"github.com/pkg/errors"
	"github.com/rs/zerolog/log"
	"github.com/spf13/viper"
	"gopkg.in/yaml.v2"
	"regexp"
	"strconv"
	"strings"
)

const Null = "\xff"

// Model for a CVE Case
//
// swagger:model CveCase
type CveCase struct {
	// The case ID of the case
	//
	// example: 1
	CaseId string `db:"id"`
	// The source of the case
	//
	// example: CERT
	Source string `db:"source"`
	// The RefNum of the case
	//
	// example: CB-K20/0056 Update 3
	RefNum string `db:"refnum"`
	// The original RefNum of the case
	//
	// example: CB-K20/0056
	OriginalRefNum string `db:"orig_refnum"`
	// The status of the case
	//
	// example: ASSIGNED
	Status string `db:"status"`
	// The creation date of the case
	//
	// example: 2020-01-29 13:04:15
	DateCreated string `db:"date_created"`
	// The date of receiving the case
	//
	// example: 2020-03-19 08:27:03
	DateReceived string `db:"date_received"`
	// The risk of the case
	//
	// example: 2
	Risk string `db:"risk"`
	// The subject of the case
	//
	// example: [CERT-Bund] CB-K20/0056 Update 3 - Google Chrome: Mehrere Schwachstellen
	Subject string `db:"subject"`
	// The verified status of the case
	//
	// example: 1
	Verified string `db:"verified"`
	// The consignor of the case
	//
	// example: wid@mail.cert-bund.de
	MsgFrom string `db:"msg_from"`
	// The consignee of the case
	//
	// example: wid@mail.cert-bund.de
	MsgTo string `db:"msg_to"`
	// The description of the case
	//
	// example: Ein entfernter, anonymer Angreifer kann mehrere Schwachstellen in Google Chrome ausnutzen, um einen nicht näher spezifizierten Angriff durchzuführen oder um Sicherheitsmechanismen zu umgehen.
	Description string

	RawData string `json:"-"`

	// The CVE nums of the case
	//
	// example: ["CVE-2020-0601","CVE-2020-6378","CVE-2020-6379","CVE-2020-6380"]
	CveNumList []string
	// The categories of the case
	//
	// example: ["Anwendung/Clients/Browser/Chrome","Anwendung/Clients/Browser","Anwendung/Clients/Browser/Chrome","Anwendung/Clients/Browser/Chrome","Betriebssystem/Linux_Unix/RedHat"],"PlatformList":["Linux","MacOS X","UNIX","Windows"]
	CategoryList []string
	// The platforms of the case
	//
	// example: ["Linux","MacOS X","UNIX","Windows"]
	PlatformList []string
	// The softwares of the case
	//
	// example: ["Google Chrome 79.0.3945.130","Google Chrome for Linux 79.0.3945.130","Google Chrome for Mac 79.0.3945.130","Open Source Arch Linux","Red Hat Enterprise Linux","Microsoft Edge (Chromium-based)"]
	SoftwareList []string
	// The update list of the case
	//
	// example: "CB-K20/0063 Update 2","CB-K20/0063 Update 3","CB-K20/0063 Update 4","CB-K20/0063 Update 5"]
	UpdateList []string
}

// CveAdvisory ...
//
// structure to parse the CERT-BUND XML data
type CveAdvisory struct {
	XMLName      xml.Name `xml:"Advisory"`
	Risk         string   `xml:"Risk"`
	CveList      cveList  `xml:"CVEList"`
	Date         string   `xml:"Date"`
	CategoryList []string `xml:"CategoryTree"`
	RefNum       string   `xml:"Ref_Num"`
	Platform     string   `xml:"Platform"`
	Software     string   `xml:"Software"`
}

type cveList struct {
	CveNum []string `xml:"CVE"`
}

// Model of a comment
//
// swagger:model CveCaseComment
type CveCaseComment struct {
	// The ID of the comment
	//
	// example: 1
	CommentId string `json:"id" db:"id"`
	// The RefNum which the comment is specified to
	//
	// example: CB-K20/0056
	RefNum string `json:"refnum" db:"refnum"`
	// The Status of the CVE case
	//
	// example: ASSIGNED
	NewStatus string `json:"new_status" db:"new_status"`
	// The date on which the comment was created
	//
	// example: 2020-03-19 08:27:05
	DateCreate string `json:"date_created" db:"date_created"`
	// The date on which the comment was last updated
	//
	// example: 2020-03-19 08:27:05
	DateUpdated string `json:"date_updated" db:"date_updated"`
	// The Username of the comment creator
	//
	// example: Unkn0wn User
	Username string `json:"username" db:"username"`
	// The comment itself
	//
	// example: Working on issue
	Comment string `json:"comment" db:"comment"`
	// The filter id the comment is assigned to
	//
	// example: 3
	FilterId string `json:"filter_id" db:"filter_id"`
}

// Model for searching CVE Cases
//
// swagger:model CveCaseSearchCriteria
type CveCaseSearchCriteria struct {
	// The filter which the case is assigned to
	//
	// example: 1
	FilterId string `json:"filterId"`
	// The case ID of the case
	//
	// example: 1
	CaseId string `json:"caseId"`
	// The source of the case
	//
	// example: CERT
	Source string `json:"source"`
	// The RefNum of the case
	//
	// example: CB-K20/0056 Update 3
	RefNum string `json:"refNum"`
	// The status of the case
	//
	// example: ASSIGNED
	Status string `json:"status"`
	// The subject of the case
	//
	// example: [CERT-Bund] CB-K20/0056 Update 3 - Google Chrome: Mehrere Schwachstellen
	Subject string `json:"subject"`
	// The category of the case
	//
	// example: Anwendung/Clients/Browser/Chrome
	Category string `json:"category"`
	// The smallest risk of cases which should be shown
	//
	// example: 4
	Risk string `json:"risk"`
	// The platform of the case
	//
	// example: Linux
	Platform string `json:"platform"`
	// The software of the case
	//
	// example: Google Chrome 79.0.3945.130
	Software string `json:"software"`
	// The date of which the listing should start
	//
	// example: 2020-04-09
	FirstDate string `json:"firstDate"`
	// The date of which the listing should end
	//
	// example: 2020-04-14
	LastDate string `json:"lastDate"`
	// The offset to the first entry shown. Only works if amount is specified
	//
	// example: 134
	Offset string `json:"offset"`
	// The amount of entries shown
	//
	// example: 50
	Amount string `json:"amount"`
}

// Model for a CVE Case Search Result
//
// swagger:model CveCaseSearchResult
type CveCaseSearchResult struct {
	// The total amount of results
	//
	// example: 102
	Total string `json:"total"`
	// The amount of currently shown results
	//
	// example: 50
	CurrentlyShown string `json:"currentlyShown"`
	// The results as an array of CVE cases
	Data []CveCase `json:"data"`
}

// Model for a filter
//
// swagger:model Filter
type Filter struct {
	// The ID of the filter
	//
	// example: 1
	Id string `db:"id" json:"id"`
	// The filter name
	//
	// example: Linux
	Filter string `db:"filter" json:"filter"`
	// The default risk level filter of the filter
	//
	// example: 1
	RiskLevel string `db:"risk_level" json:"risk_level"`
	// The Keywords of the filter.
	//
	// example: Citrix; Cisco
	Keywords string `db:"keywords" json:"keywords"`
}

// Model for a filter category
//
// swagger:model FilterCategory
type FilterCategory struct {
	// The ID of the filter category
	//
	// example: 1
	Id string `db:"id" json:"id"`
	// The ID of the filter the filter category is assigned to
	//
	// example: 1
	FilterId string `db:"filter_id" json:"filter_id"`
	// The category of the Filter
	//
	// example: Anwendung
	Category string `db:"category" json:"category"`
}

// Model for a category
//
// swagger:model Category
type Category struct {
	// The category name
	//
	// example: Anwendung
	Category string `db:"category" json:"category"`
}

type categoriesConfig struct {
	Categories string `yaml:"categories"`
}

// Model for a platform
//
// swagger:model Platform
type Platform struct {
	// The platform name
	//
	// example: Linux
	Platform string `db:"platform" json:"platform"`
}

// Model for exporting filter_config.yaml
//
// swagger:model FilterConfig
type filterConfig struct {
	// List of all filters
	//
	// example:filters: 1, Linux; 2, Windows; 3, Network; 4, Other
	Filters string `yaml:"filters"`
	// List of all filter categories
	//
	// example: filter_categories: |\n 1, Anwendung;\n 2, Anwendung;\n...
	FilterCategories string `yaml:"filter_categories"`
	// List of all excluded filter categories
	//
	// example: filter_categories: |\n 1, Anwendung;\n 2, Anwendung;\n...
	ExcludedFilterCategories string `yaml:"excluded_filter_categories"`
}

var sqlSchemaArr = []string{
	`DROP TABLE IF EXISTS case_comments;`,
	`DROP TABLE IF EXISTS case_rawdatas;`,
	`DROP TABLE IF EXISTS case_categories;`,
	`DROP TABLE IF EXISTS case_cvenums;`,
	`DROP TABLE IF EXISTS case_platforms;`,
	`DROP TABLE IF EXISTS case_softwares;`,
	`DROP TABLE IF EXISTS case_descriptions;`,
	`DROP TABLE IF EXISTS case_status;`,
	`DROP TABLE IF EXISTS cases;`,
	`DROP TABLE IF EXISTS filter_categories;`,
	`DROP TABLE IF EXISTS filter_excluded_categories;`,
	`DROP TABLE IF EXISTS filters;`,
	`DROP TABLE IF EXISTS categories;`,

	`CREATE TABLE cases (
		id int(11) NOT NULL,
		source enum('CERT','NIST') NOT NULL DEFAULT 'CERT',
		refnum varchar(255) NOT NULL,
		orig_refnum varchar(255) NOT NULL,
		date_created datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
		date_received datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
		risk int(11) NOT NULL DEFAULT 3,
		subject varchar(255) DEFAULT NULL,
		verified tinyint(1) NOT NULL DEFAULT 0,
		msg_from varchar(255) DEFAULT NULL,
		msg_to varchar(255) DEFAULT NULL
		) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;`,
	`CREATE TABLE case_rawdatas (
		id int(11) NOT NULL,
		case_id int(11) NOT NULL,
		rawdata blob NOT NULL
		) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;`,
	`CREATE TABLE case_categories (
		id int(11) NOT NULL,
		case_id int(11) NOT NULL,
		category varchar(255) NOT NULL
		) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;`,
	`CREATE TABLE case_cvenums (
		id int(11) NOT NULL,
		case_id int(11) NOT NULL,
		cvenum varchar(255) NOT NULL
		) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;`,
	`CREATE TABLE case_platforms (
		id int(11) NOT NULL,
		case_id int(11) NOT NULL,
		platform varchar(255) NOT NULL
		) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;`,
	`CREATE TABLE case_softwares (
		id int(11) NOT NULL, 
		case_id int(11) NOT NULL,
		software varchar(255) NOT NULL
		) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;`,
	`CREATE TABLE case_comments (
  		id int(11) NOT NULL,
  		refnum varchar(255) NOT NULL,
  		filter_id int(11) NOT NULL,
  		date_created datetime NOT NULL DEFAULT current_timestamp(),
  		date_updated datetime DEFAULT current_timestamp(),
  		new_status enum('NEW','ASSIGNED','CLOSED') DEFAULT NULL,
  		username varchar(255) DEFAULT NULL,
  		comment text NOT NULL
		) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;`,
	`CREATE TABLE case_descriptions (
		id int(11) NOT NULL,
  		case_id int(11) NOT NULL,
  		description text NOT NULL
		) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;`,
	`CREATE TABLE case_status (
		id int(11) NOT NULL,
		refnum varchar(255) NOT NULL,
		filter_id int(11) NOT NULL,
		status enum('NEW','ASSIGNED','CLOSED','') NOT NULL
		) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;`,
	`CREATE TABLE filters (
		id int(11) NOT NULL,
		filter varchar(255) NOT NULL,
		risk_level int(11) DEFAULT 1,
		keywords varchar(255) DEFAULT ""
		) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;`,
	`CREATE TABLE filter_categories (
		id int(11) NOT NULL,
		filter_id int(11) NOT NULL,
		category varchar(255) NOT NULL
		) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;`,
	`CREATE TABLE filter_excluded_categories (
		id int(11) NOT NULL,
		filter_id int(11) NOT NULL,
		category varchar(255) NOT NULL
		) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;`,
	`CREATE TABLE categories (
		id int(11) NOT NULL,
		category varchar(255) NOT NULL
		) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;`,

	`ALTER TABLE cases ADD PRIMARY KEY (id), ADD UNIQUE KEY source_refnum (source,refnum), ADD KEY orig_refnum (orig_refnum);`,
	`ALTER TABLE case_rawdatas ADD PRIMARY KEY (id), ADD KEY case_id (case_id);`,
	`ALTER TABLE case_categories ADD PRIMARY KEY (id), ADD KEY case_id (case_id), ADD KEY category (category);`,
	`ALTER TABLE case_cvenums ADD PRIMARY KEY (id), ADD KEY case_id (case_id), ADD KEY cvenum (cvenum);`,
	`ALTER TABLE case_platforms ADD PRIMARY KEY (id), ADD KEY case_id (case_id), ADD KEY platform (platform);`,
	`ALTER TABLE case_softwares ADD PRIMARY KEY (id), ADD KEY case_id (case_id), ADD KEY software (software);`,
	`ALTER TABLE case_comments ADD PRIMARY KEY (id), ADD KEY refnum (refnum), ADD KEY filter_id (filter_id);`,
	`ALTER TABLE case_descriptions ADD PRIMARY KEY (id), ADD KEY case_id (case_id);`,
	`ALTER TABLE case_status ADD PRIMARY KEY (id), ADD KEY refnum (refnum), ADD KEY filter_id (filter_id);`,
	`ALTER TABLE filters ADD PRIMARY KEY (id), ADD UNIQUE KEY filter (filter), ADD KEY risk_level (risk_level);`,
	`ALTER TABLE filter_categories ADD PRIMARY KEY (id);`,
	`ALTER TABLE filter_excluded_categories ADD PRIMARY KEY (id);`,
	`ALTER TABLE categories ADD PRIMARY KEY (id), ADD KEY category (category);`,

	`ALTER TABLE cases MODIFY id int(11) NOT NULL AUTO_INCREMENT;`,
	`ALTER TABLE case_rawdatas MODIFY id int(11) NOT NULL AUTO_INCREMENT;`,
	`ALTER TABLE case_categories MODIFY id int(11) NOT NULL AUTO_INCREMENT;`,
	`ALTER TABLE case_cvenums MODIFY id int(11) NOT NULL AUTO_INCREMENT;`,
	`ALTER TABLE case_platforms MODIFY id int(11) NOT NULL AUTO_INCREMENT;`,
	`ALTER TABLE case_softwares MODIFY id int(11) NOT NULL AUTO_INCREMENT;`,
	`ALTER TABLE case_comments MODIFY id int(11) NOT NULL AUTO_INCREMENT;`,
	`ALTER TABLE case_descriptions MODIFY id int(11) NOT NULL AUTO_INCREMENT;`,
	`ALTER TABLE case_status MODIFY id int(11) NOT NULL AUTO_INCREMENT;`,
	`ALTER TABLE filters MODIFY id int(11) NOT NULL AUTO_INCREMENT;`,
	`ALTER TABLE filter_categories MODIFY id int(11) NOT NULL AUTO_INCREMENT;`,
	`ALTER TABLE filter_excluded_categories MODIFY id int(11) NOT NULL AUTO_INCREMENT;`,
	`ALTER TABLE categories MODIFY id int(11) NOT NULL AUTO_INCREMENT;`,

	`ALTER TABLE case_categories ADD CONSTRAINT case_categories_ibfk_1 FOREIGN KEY (case_id) REFERENCES cases (id);`,
	`ALTER TABLE case_rawdatas ADD CONSTRAINT case_rawdatas_ibfk_1 FOREIGN KEY (case_id) REFERENCES cases (id);`,
	`ALTER TABLE case_cvenums ADD CONSTRAINT case_cvenums_ibfk_1 FOREIGN KEY (case_id) REFERENCES cases (id);`,
	`ALTER TABLE case_platforms ADD CONSTRAINT case_platforms_ibfk_1 FOREIGN KEY (case_id) REFERENCES cases (id);`,
	`ALTER TABLE case_softwares ADD CONSTRAINT case_softwares_ibfk_1 FOREIGN KEY (case_id) REFERENCES cases (id);`,
	`ALTER TABLE case_comments ADD CONSTRAINT case_comments_ibfk_1 FOREIGN KEY (refnum) REFERENCES cases (orig_refnum);`,
	`ALTER TABLE case_comments ADD CONSTRAINT case_comments_ibfk_2 FOREIGN KEY (filter_id) REFERENCES filters (id);`,
	`ALTER TABLE case_status ADD CONSTRAINT case_status_ibfk_1 FOREIGN KEY (refnum) REFERENCES cases (orig_refnum);`,
	`ALTER TABLE case_status ADD CONSTRAINT case_status_ibfk_2 FOREIGN KEY (filter_id) REFERENCES filters (id);`,
	`ALTER TABLE case_descriptions ADD CONSTRAINT case_descriptions_ibfk_1 FOREIGN KEY (case_id) REFERENCES cases (id);`,
	`ALTER TABLE filter_categories ADD CONSTRAINT filter_categories_ibfk_1 FOREIGN KEY (filter_id) REFERENCES filters (id);`,
	`ALTER TABLE filter_categories ADD CONSTRAINT filter_categories_ibfk_2 FOREIGN KEY (category) REFERENCES categories (category);`,
	`ALTER TABLE filter_excluded_categories ADD CONSTRAINT filter_excluded_categories_ibfk_1 FOREIGN KEY (filter_id) REFERENCES filters (id);`,
}

// InitDB initializes the DB.
func InitDB(db *sqlx.DB) error {
	for i := 0; i < len(sqlSchemaArr); i++ {
		_, err := db.Exec(sqlSchemaArr[i])
		if err != nil {
			log.Error().
				AnErr("Error", err).
				Msg("Could not set up database schema - query:" + sqlSchemaArr[i])
			return err
		}
	}
	err := ImportCategories(db)
	if err != nil {
		log.Error().
			AnErr("Error", err)
		return err
	}
	err = ImportFilterFromConfig(db)
	if err != nil {
		log.Error().
			AnErr("Error", err)
		return err
	}
	return nil
}

// WriteToDB writes a case to the DB.
func (c *CveCase) WriteToDB(db *sqlx.DB) error {
	tx := db.MustBegin()
	dbresult, err := tx.Exec(tx.Rebind("INSERT INTO cases (source, refnum, orig_refnum, date_created, risk, subject, verified, msg_from, msg_to) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"), c.Source, c.RefNum, c.OriginalRefNum, c.DateCreated, c.Risk, c.Subject, c.Verified, c.MsgFrom, c.MsgTo)
	if err != nil {
		log.Error().
			AnErr("Error", err).
			Msg("Could not execute query")
		return err
	}
	dblastid, err := dbresult.LastInsertId()
	if err != nil {
		log.Error().
			AnErr("Error", err).
			Msg("Could not get last insert id")
		return err
	}
	log.Debug().
		Msg("case inserted into database - ID: " + string(dblastid))
	_, err = tx.Exec(tx.Rebind("INSERT INTO case_rawdatas (case_id, rawdata) VALUES (?, ?)"), dblastid, c.RawData)
	if err != nil {
		log.Error().
			AnErr("Error", err).
			Msg("Could not execute query")
		return err
	}
	_, err = tx.Exec(tx.Rebind("INSERT INTO case_descriptions (case_id, description) VALUES (?, ?)"), dblastid, c.Description)
	if err != nil {
		log.Error().
			AnErr("Error", err).
			Msg("Could not execute query")
		return err
	}
	for i := 0; i < len(c.CategoryList); i++ {
		_, err := tx.Exec(tx.Rebind("INSERT INTO case_categories (case_id, category) VALUES (?, ?)"), dblastid, c.CategoryList[i])
		if err != nil {
			log.Error().
				AnErr("Error", err).
				Msg("Could not execute query")
			return err
		}
	}
	for i := 0; i < len(c.CveNumList); i++ {
		_, err := tx.Exec(tx.Rebind("INSERT INTO case_cvenums (case_id, cvenum) VALUES (?, ?)"), dblastid, c.CveNumList[i])
		if err != nil {
			log.Error().
				AnErr("Error", err).
				Msg("Could not execute query")
			return err
		}
	}
	for i := 0; i < len(c.PlatformList); i++ {
		_, err := tx.Exec(tx.Rebind("INSERT INTO case_platforms (case_id, platform) VALUES (?, ?)"), dblastid, c.PlatformList[i])
		if err != nil {
			log.Error().
				AnErr("Error", err).
				Msg("Could not execute query")
			return err
		}
	}
	for i := 0; i < len(c.SoftwareList); i++ {
		_, err := tx.Exec(tx.Rebind("INSERT INTO case_softwares (case_id, software) VALUES (?, ?)"), dblastid, c.SoftwareList[i])
		if err != nil {
			log.Error().
				AnErr("Error", err).
				Msg("Could not execute query")
			return err
		}
	}
	var filters []Filter
	err = db.Select(&filters, "SELECT * FROM filters")
	if err != nil {
		log.Error().
			AnErr("Error", err).
			Msg("Could not execute query")
		return err
	}
	var status []string
	for _, filter := range filters {
		err = db.Select(&status, "SELECT status FROM case_status WHERE refnum = '"+c.OriginalRefNum+"' AND filter_id = '"+filter.Id+"'")
		if err != nil {
			log.Error().
				AnErr("Error", err).
				Msg("Could not execute query")
			return err
		}
		if status == nil {
			_, err := tx.Exec(tx.Rebind("INSERT INTO case_status (refnum, filter_id, status) VALUES (?, ?, ?)"), c.OriginalRefNum, filter.Id, "NEW")
			if err != nil {
				log.Error().
					AnErr("Error", err).
					Msg("Could not execute query")
				return err
			}
		}
	}
	var categories []Category
	for _, category := range c.CategoryList {
		err = db.Select(&categories, "SELECT category FROM categories WHERE category = '"+category+"'")
		if err != nil {
			log.Error().
				AnErr("Error", err).
				Msg("Could not execute query")
			return err
		}
		if categories == nil {
			_, err := tx.Exec(tx.Rebind("INSERT INTO categories (category) VALUES (?)"), category)
			if err != nil {
				log.Error().
					AnErr("Error", err).
					Msg("Could not execute query")
				return err
			}
		}
	}
	err = tx.Commit()
	if err != nil {
		log.Error().
			AnErr("Error", err).
			Msg("Could not commit transaction")
		return err
	}
	c.CaseId = strconv.Itoa(int(dblastid))
	return nil
}

// ReadFromDB reads in a case from the DB.
func (c *CveCase) ReadFromDB(db *sqlx.DB, filterId string) error {
	caseId, err := strconv.Atoi(c.CaseId)
	if err != nil {
		log.Error().
			AnErr("Error", err).
			Msg("Could not parse case id")
		return err
	}
	if caseId < 1 {
		log.Error().
			Msg("CaseId is out of range")
		return errors.New("CaseId out of range")
	}
	err = db.Get(c, db.Rebind("SELECT * FROM cases WHERE id=?"), c.CaseId)
	if err != nil {
		log.Error().
			AnErr("Error", err).
			Msg("Could not execute SELECT query")
		err := errors.Wrap(err, "Could not execute query")
		return err
	}
	err = db.Get(c, db.Rebind("SELECT rawdata FROM case_rawdatas WHERE case_id=?"), c.CaseId)
	if err != nil {
		log.Error().
			AnErr("Error", err).
			Msg("Could not execute SELECT query")
		err := errors.Wrap(err, "Could not execute query")
		return err
	}
	err = db.Get(c, db.Rebind("SELECT description FROM case_descriptions WHERE case_id=?"), c.CaseId)
	if err != nil {
		log.Error().
			AnErr("Error", err).
			Msg("Could not execute SELECT query")
		err := errors.Wrap(err, "Could not execute query")
		return err
	}
	err = db.Select(&c.CategoryList, db.Rebind("SELECT category FROM case_categories WHERE case_id=?"), c.CaseId)
	if err != nil {
		log.Error().
			AnErr("Error", err).
			Msg("Could not execute SELECT query")
		err := errors.Wrap(err, "Could not execute query")
		return err
	}
	err = db.Select(&c.CveNumList, db.Rebind("SELECT cvenum FROM case_cvenums WHERE case_id=?"), c.CaseId)
	if err != nil {
		log.Error().
			AnErr("Error", err).
			Msg("Could not execute SELECT query")
		err := errors.Wrap(err, "Could not execute query")
		return err
	}
	err = db.Select(&c.PlatformList, db.Rebind("SELECT platform FROM case_platforms WHERE case_id=?"), c.CaseId)
	if err != nil {
		log.Error().
			AnErr("Error", err).
			Msg("Could not execute SELECT query")
		err := errors.Wrap(err, "Could not execute query")
		return err
	}
	err = db.Select(&c.SoftwareList, db.Rebind("SELECT software FROM case_softwares WHERE case_id=?"), c.CaseId)
	if err != nil {
		log.Error().
			AnErr("Error", err).
			Msg("Could not execute SELECT query")
		err := errors.Wrap(err, "Could not execute query")
		return err
	}
	err = db.Select(&c.UpdateList, db.Rebind("SELECT refnum FROM cases WHERE orig_refnum = '"+c.OriginalRefNum+"' ORDER BY orig_refnum DESC"))
	if err != nil {
		log.Error().
			AnErr("Error", err).
			Msg("Could not execute query")
		return err
	}
	if filterId != "" {
		err = db.Get(c, db.Rebind("SELECT status FROM case_status WHERE refnum=? AND filter_id=?"), c.OriginalRefNum, filterId)
		if err != nil {
			log.Error().
				AnErr("Error", err).
				Msg("Could not execute SELECT query")
			err := errors.Wrap(err, "Could not execute query")
			return err
		}
	}
	return nil
}

// SearchCases searches all cve cases.
func SearchCases(db *sqlx.DB, searchCriteria CveCaseSearchCriteria) ([]CveCase, error) {
	var cveCaselist []CveCase
	var selectFields = "cases.id, cases.source, cases.refnum, cases.date_created, cases.date_received, cases.risk, cases.subject, cases.verified, cases.msg_from, cases.msg_to"
	sb := sqlbuilder.MySQL.NewSelectBuilder()
	sb.Select(selectFields)
	sb.From("cases")
	var keywords []string
	var filters []Filter
	if searchCriteria.FilterId != "" {
		sb2 := sqlbuilder.MySQL.NewSelectBuilder()
		sb2.Select("*").From("filters").Where(sb2.Equal("id", searchCriteria.FilterId))
		sql, args := sb2.Build()
		query, err := sqlbuilder.MySQL.Interpolate(sql, args)
		if err != nil {
			log.Error().
				AnErr("Error", err).
				Msg("Could not build sql query")
			return nil, err
		}
		err = db.Select(&filters, query)
		if err != nil {
			log.Error().
				AnErr("Error", err).
				Msg("Could not select filter from DB")
			return nil, err
		}
		if filters == nil {
			err := errors.New("filter not in DB")
			return nil, err
		}
		if filters[0].Filter == "Other" {
			sb.Where("cases.id IN (SELECT case_categories.case_id FROM case_categories WHERE case_categories.category NOT IN (SELECT category FROM filter_categories))")
		} else {
			sb.Where(sb.Equal("cases.id IN (SELECT case_categories.case_id FROM case_categories WHERE case_categories.category IN (SELECT category FROM filter_categories WHERE filter_categories.filter_id",
				searchCriteria.FilterId) + "))")
			sb.Where(sb.Equal("cases.id NOT IN (SELECT case_categories.case_id FROM case_categories WHERE case_categories.category IN (SELECT category FROM filter_excluded_categories WHERE filter_excluded_categories.filter_id",
				searchCriteria.FilterId) + "))")
		}
		riskLevel, err := strconv.Atoi(filters[0].RiskLevel)
		if err != nil {
			log.Error().
				AnErr("Error", err).
				Msg("Could not parse risk level")
			return nil, err
		}
		if riskLevel > 5 || riskLevel < 0 {
			log.Error().
				AnErr("Error", err).
				Msg("Invalid risk level")
			return nil, err
		}
		if filters[0].RiskLevel != "1" {
			sb.Where(sb.GreaterEqualThan("risk", filters[0].RiskLevel))
		}
		if filters[0].Keywords != "" {
			regex := regexp.MustCompile(";")
			for _, keyword := range regex.Split(filters[0].Keywords, -1) {
				keyword = strings.TrimSpace(keyword)
				if keyword != "" {
					keywords = append(keywords, keyword)
				}
			}
		}
	}
	if searchCriteria.Category != "" {
		sb.JoinWithOption(sqlbuilder.LeftJoin, "case_categories", "case_categories.case_id = cases.id")
		sb.Where(sb.Equal("case_categories.category", searchCriteria.Category))
	}
	if searchCriteria.CaseId != "" {
		sb.Where(sb.Equal("cases.id", searchCriteria.CaseId))
	}
	if searchCriteria.Subject != "" {
		sb.Where(sb.Equal("cases.subject", searchCriteria.Subject))
	}
	if searchCriteria.RefNum != "" {
		sb.Where(sb.Like("cases.refnum", "%"+searchCriteria.RefNum+"%"))
	}
	if searchCriteria.Source != "" {
		if searchCriteria.Source != "CERT" {
			return nil, errors.New("invalid source (only CERT valid)")
		}
		sb.Where(sb.Equal("cases.source", searchCriteria.Source))
	}
	if searchCriteria.Status != "" {
		if searchCriteria.FilterId == "" {
			err := errors.New("filter needed when searching after status")
			return nil, err
		}
		if !(searchCriteria.Status == "ASSIGNED" || searchCriteria.Status == "NEW" || searchCriteria.Status == "CLOSED") {
			err := errors.New("invalid status (only NEW, ASSIGNED and CLOSED valid)")
			return nil, err
		}
		sb.Where(sb.Equal("cases.orig_refnum IN (SELECT case_status.refnum FROM case_status WHERE case_status.status = '"+searchCriteria.Status+"' AND case_status.filter_id", searchCriteria.FilterId) + ")")
	} else if searchCriteria.FilterId != "" {
		sb.Where(sb.Equal("cases.orig_refnum IN (SELECT case_status.refnum FROM case_status WHERE case_status.filter_id", searchCriteria.FilterId) + ")")
	}
	if searchCriteria.Risk != "" {
		sb.Where(sb.GreaterEqualThan("cases.risk", searchCriteria.Risk))
	}
	if searchCriteria.Platform != "" {
		sb.Where(sb.Equal("cases.id IN (SELECT case_platforms.case_id FROM case_platforms WHERE case_platforms.platform", searchCriteria.Platform) + ")")
	}
	if searchCriteria.Software != "" {
		sb.Where(sb.Like("cases.id IN (SELECT case_softwares.case_id FROM case_softwares WHERE case_softwares.software", "%"+searchCriteria.Software+"%") + ")")
	}
	if searchCriteria.FirstDate != "" {
		regex := regexp.MustCompile("^....-..-..\\z")
		if !regex.MatchString(searchCriteria.FirstDate) {
			err := errors.New("invalid format of firstDate")
			return nil, err
		}
		sb.Where(sb.GreaterEqualThan(`cases.date_received`, searchCriteria.FirstDate))
	}
	if searchCriteria.LastDate != "" {
		regex := regexp.MustCompile("^....-..-..\\z")
		if !regex.MatchString(searchCriteria.LastDate) {
			err := errors.New("invalid format of lastDate")
			return nil, err
		}
		sb.Where(sb.LessEqualThan(`cases.date_received`, searchCriteria.LastDate))
	}
	if keywords == nil {
		sb.OrderBy("id").Desc()
		if searchCriteria.Amount != "" {
			limit, err := strconv.Atoi(searchCriteria.Amount)
			if err != nil {
				log.Error().
					AnErr("Error", err).
					Msg("Couldn't parse amount")
				return nil, err
			}
			sb.Limit(limit)
		}
		if searchCriteria.Offset != "" {
			offset, err := strconv.Atoi(searchCriteria.Offset)
			if err != nil {
				log.Error().
					AnErr("Error", err).
					Msg("Couldn't parse amount")
				return nil, err
			}
			sb.Offset(offset)
		}
	}
	var sql string
	var args []interface{}
	if keywords != nil {
		sbKeywords := sqlbuilder.MySQL.NewSelectBuilder()
		sbKeywordsWhere1 := sqlbuilder.MySQL.NewSelectBuilder()
		sbKeywordsWhere2 := sqlbuilder.MySQL.NewSelectBuilder()
		sbKeywords.Select(selectFields).From("cases")
		sbKeywords.Where(sbKeywords.In("cases.id", sbKeywordsWhere1))
		sbKeywords.Where(sbKeywords.In("cases.orig_refnum", sbKeywordsWhere2))
		var subQuery string
		for i, keyword := range keywords {
			if i != 0 {
				subQuery += " OR "
			}
			subQuery += "cases.subject LIKE '%" + keyword + "%'"
		}
		sbKeywordsWhere1.Select("cases.id").From("cases").Where(subQuery)
		sbKeywordsWhere2.Select("case_status.refnum").From("case_status").Where("case_status.filter_id = " + filters[0].Id)
		sbKeywords.OrderBy("id").Desc()
		if searchCriteria.Amount != "" {
			limit, err := strconv.Atoi(searchCriteria.Amount)
			if err != nil {
				log.Error().
					AnErr("Error", err).
					Msg("Couldn't parse amount")
				return nil, err
			}
			sbKeywords.Limit(limit)
		}
		if searchCriteria.Offset != "" {
			offset, err := strconv.Atoi(searchCriteria.Offset)
			if err != nil {
				log.Error().
					AnErr("Error", err).
					Msg("Couldn't parse amount")
				return nil, err
			}
			sbKeywords.Offset(offset)
		}
		union := sqlbuilder.Buildf("%v UNION %v", sb, sbKeywords)
		sql, args = union.Build()
	} else {
		sql, args = sb.Build()
	}
	query, err := sqlbuilder.MySQL.Interpolate(sql, args)
	if err != nil {
		log.Error().
			AnErr("Error", err).
			Msg("Could not build sql query")
		return nil, err
	}
	err = db.Select(&cveCaselist, query)
	if err != nil {
		log.Error().
			AnErr("Error", err).
			Msg("Could not select id from DB")
		return nil, err
	}
	for i, cve := range cveCaselist {
		err := cve.ReadFromDB(db, searchCriteria.FilterId)
		if err != nil {
			log.Error().
				AnErr("Error", err).
				Msg("Could not get details of cve case")
			return nil, err
		}
		cveCaselist[i] = cve
	}
	return cveCaselist, nil
}

// AddComment adds a comment
func AddComment(db *sqlx.DB, cveComment CveCaseComment) error {
	if (cveComment.RefNum == "") || (cveComment.Comment == "") || (cveComment.NewStatus == "") || (cveComment.Username == "") || (cveComment.FilterId == "") {
		err := errors.New("comment data is defective")
		return err
	}

	cvecase, err := SearchCases(db, CveCaseSearchCriteria{RefNum: cveComment.RefNum})
	if (err != nil) || (cvecase == nil) {
		return errors.New("CaseID is not in DB")
	}

	if !(cveComment.NewStatus == "ASSIGNED" || cveComment.NewStatus == "NEW" || cveComment.NewStatus == "CLOSED") {
		return errors.New("invalid status (only NEW, ASSIGNED and CLOSED valid)")
	}

	_, err = db.Exec(db.Rebind("INSERT INTO case_comments (refnum, new_status, username, comment, filter_id) VALUES (?, ?, ?, ?, ?)"), cveComment.RefNum, cveComment.NewStatus, cveComment.Username, cveComment.Comment, cveComment.FilterId)
	if err != nil {
		log.Error().
			AnErr("Error", err).
			Msg("Could not execute query")
		return err
	}
	_, err = db.Exec(db.Rebind("UPDATE case_status SET status = ? WHERE refnum = ? AND filter_id = ?"), cveComment.NewStatus, cveComment.RefNum, cveComment.FilterId)
	if err != nil {
		log.Error().
			AnErr("Error", err).
			Msg("Could not execute query")
		return err
	}

	return nil
}

// SearchComment searches after a comment.
func SearchComment(db *sqlx.DB, comment CveCaseComment) ([]CveCaseComment, error) {
	var cveComment []CveCaseComment
	sb := sqlbuilder.MySQL.NewSelectBuilder()
	sb.Select("*")
	sb.From("case_comments")
	sb.Where(sb.Equal("case_comments.id", comment.CommentId))
	sql, args := sb.Build()
	query, err := sqlbuilder.MySQL.Interpolate(sql, args)
	if err != nil {
		log.Error().
			AnErr("Error", err).
			Msg("Could not build sql query")
		return nil, err
	}
	err = db.Select(&cveComment, query)
	if err != nil {
		log.Error().
			Msg("Could not select comment")
		return nil, err
	}
	return cveComment, nil
}

// SearchCommentsOfCveCase searches after all comments of a cve case.
func SearchCommentsOfCveCase(db *sqlx.DB, cvecase CveCase, filterId string) ([]CveCaseComment, error) {
	var result []CveCaseComment
	sb := sqlbuilder.MySQL.NewSelectBuilder()
	sb2 := sqlbuilder.MySQL.NewSelectBuilder()
	sb2.Select("orig_refnum").From("cases").Where(sb2.Equal("id", cvecase.CaseId))
	sb.Select("*")
	sb.From("case_comments")
	sb.Where(sb.In("case_comments.refnum", sb2))
	sb.Where(sb.Equal("case_comments.filter_id", filterId))
	sql, args := sb.Build()
	query, err := sqlbuilder.MySQL.Interpolate(sql, args)
	if err != nil {
		log.Error().
			AnErr("Error", err).
			Msg("Could not build sql query")
		return nil, err
	}
	err = db.Select(&result, query)
	if err != nil {
		log.Error().
			Msg("Could not select comment")
		return nil, err
	}
	return result, nil
}

// ChangeComment changes a comment.
func ChangeComment(db *sqlx.DB, comment CveCaseComment) error {
	checkComment, err := SearchComment(db, comment)
	if len(checkComment) == 0 {
		log.Error().
			AnErr("Error: ", err).
			Msg("Could not find comment")
		return errors.New("Could not find comment")
	}
	if (comment.Comment == "") && (comment.Username == "") && (comment.NewStatus != "CLOSED") {
		return errors.New("Comment is empty")
	}
	if comment.Comment != "" {
		_, err = db.Exec(db.Rebind("UPDATE case_comments SET comment = ?, date_updated = CURRENT_TIMESTAMP WHERE id = ?"), comment.Comment, comment.CommentId)
		if err != nil {
			log.Error().
				AnErr("Error", err).
				Msg("Could not update db")
			return err
		}
	}
	if comment.Username != "" {
		_, err = db.Exec(db.Rebind("UPDATE case_comments SET username = ?, date_updated = CURRENT_TIMESTAMP WHERE id = ?"), comment.Username, comment.CommentId)
		if err != nil {
			log.Error().
				AnErr("Error", err).
				Msg("Could not update db")
			return err
		}
	}
	return nil
}

// RemoveComment removes a comment.
func RemoveComment(db *sqlx.DB, id string) error {
	var comments []CveCaseComment
	err := db.Select(&comments, db.Rebind("SELECT id, case_id, date_created, new_status, username, comment FROM case_comments WHERE id = ?"), id)
	if err != nil {
		log.Error().
			AnErr("Error", err).
			Msg("Could not get comment")
		return err
	}
	if comments == nil {
		err = errors.New("Comment not found")
		return err
	}
	_, err = db.Exec(db.Rebind("DELETE FROM case_comments WHERE id = ?"), id)
	if err != nil {
		log.Error().
			AnErr("Error: ", err).
			Msg("Could not execute query")
		return err
	}
	return nil
}

func (f *Filter) readFromDB(db *sqlx.DB) error {
	err := db.Get(f.Filter, db.Rebind("SELECT filter FROM filters WHERE id = ?"), f.Id)
	if err != nil {
		log.Error().
			AnErr("Error", err).
			Msg("Could not execute query")
		return err
	}
	return nil
}

func (f *FilterCategory) readFromDB(db *sqlx.DB) error {
	err := db.Get(f.FilterId, db.Rebind("SELECT filter_id FROM filter_categories WHERE id = ?"), f.Id)
	if err != nil {
		log.Error().
			AnErr("Error", err).
			Msg("Could not execute query")
		return err
	}
	err = db.Get(f.Category, db.Rebind("SELECT category FROM filter_categories WHERE id = ?"), f.Id)
	if err != nil {
		log.Error().
			AnErr("Error", err).
			Msg("Could not execute query")
		return err
	}
	return nil
}

// AddFilter adds a filter.
func AddFilter(db *sqlx.DB, filter Filter) error {
	sb := sqlbuilder.MySQL.NewInsertBuilder()
	sb.InsertInto("filters")
	sb.Cols("filter")
	sb.Values(filter.Filter)
	sql, args := sb.Build()
	query, err := sqlbuilder.MySQL.Interpolate(sql, args)
	if err != nil {
		log.Error().
			AnErr("Error", err).
			Msg("Could not build sql query")
		return err
	}
	_, err = db.Exec(query)
	if err != nil {
		log.Error().
			AnErr("Error", err).
			Msg("Could not insert filters into DB")
		return err
	}
	if filter.RiskLevel != "" || filter.Keywords != "" {
		var id []string
		err := db.Select(&id, "SELECT filters.id FROM filters WHERE filters.filter = '"+filter.Filter+"'")
		if err != nil {
			log.Error().
				AnErr("Error", err).
				Msg("Could not exec query")
			return err
		}
		filter.Id = id[0]
		err = ChangeFilter(db, filter)
		if err != nil {
			log.Error().
				AnErr("Error", err).
				Msg("Could not update filter")
			return err
		}
	}
	return nil
}

// ChangeFilter changes a filter.
func ChangeFilter(db *sqlx.DB, filter Filter) error {
	if filter.Id == "" {
		err := errors.New("filterID is empty")
		return err
	}
	var filtersDBList []Filter
	sb := sqlbuilder.MySQL.NewSelectBuilder()
	sb.Select("*").From("filters").Where(sb.Equal("id", filter.Id))
	sql, args := sb.Build()
	query, err := sqlbuilder.MySQL.Interpolate(sql, args)
	if err != nil {
		log.Error().
			AnErr("Error", err).
			Msg("Could not build sql query")
		return err
	}
	err = db.Select(&filtersDBList, query)
	if err != nil {
		log.Error().
			AnErr("Error", err).
			Msg("Could not get filters from DB")
		return err
	}
	if filtersDBList == nil {
		err = errors.New("Specified filter ID is not in DB")
		return err
	}
	if filter.Filter != "" {
		_, err = db.Exec(db.Rebind("UPDATE filters SET filter = ? WHERE id = ?"), filter.Filter, filter.Id)
		if err != nil {
			log.Error().
				AnErr("Error", err).
				Msg("Could not update db")
			return err
		}
	}
	if filter.RiskLevel != "" {
		_, err = db.Exec(db.Rebind("UPDATE filters SET risk_level = ? WHERE id = ?"), filter.RiskLevel, filter.Id)
		if err != nil {
			log.Error().
				AnErr("Error", err).
				Msg("Could not update db")
			return err
		}
	}
	if filter.Keywords != Null {
		_, err = db.Exec(db.Rebind("UPDATE filters SET keywords = ? WHERE id = ?"), filter.Keywords, filter.Id)
		if err != nil {
			log.Error().
				AnErr("Error", err).
				Msg("Could not update db")
			return err
		}
	}
	return nil
}

// DeleteFilter deletes a filter.
func DeleteFilter(db *sqlx.DB, id string) error {
	_, err := db.Exec(db.Rebind("DELETE FROM case_status WHERE filter_id = " + id))
	if err != nil {
		log.Error().
			AnErr("Error", err).
			Msg("Could not execute query")
		return err
	}
	_, err = db.Exec(db.Rebind("DELETE FROM case_comments WHERE filter_id = " + id))
	if err != nil {
		log.Error().
			AnErr("Error", err).
			Msg("Could not execute query")
		return err
	}
	sb := sqlbuilder.NewDeleteBuilder()
	sb.DeleteFrom("filter_categories").Where(sb.Equal("filter_id", id))
	sql, args := sb.Build()
	query, err := sqlbuilder.MySQL.Interpolate(sql, args)
	if err != nil {
		log.Error().
			AnErr("Error", err).
			Msg("Could not build sql query")
		return err
	}
	_, err = db.Exec(query)
	if err != nil {
		log.Error().
			AnErr("Error", err).
			Msg("Could not delete filter_categories")
		return err
	}
	var filtersDBList []Filter
	sb2 := sqlbuilder.MySQL.NewSelectBuilder()
	sb2.Select("*")
	sb2.From("filters")
	sb2.Where(sb2.Equal("id", id))
	sql, args = sb2.Build()
	query, err = sqlbuilder.MySQL.Interpolate(sql, args)
	if err != nil {
		log.Error().
			AnErr("Error", err).
			Msg("Could not build sql query")
		return err
	}
	err = db.Select(&filtersDBList, query)
	if err != nil {
		log.Error().
			AnErr("Error", err).
			Msg("Could not get filters from DB")
		return err
	}
	if len(filtersDBList) == 0 {
		return errors.New("FilterId is not in DB!")
	}
	_, err = db.Exec(db.Rebind("DELETE FROM filters WHERE id = ?"), id)
	if err != nil {
		log.Error().
			AnErr("Error", err).
			Msg("Could not delete FilterId from DB")
		return err
	}
	return nil
}

// ImportFilter imports the filter and filtercategories.
func ImportFilter(db *sqlx.DB, input filterConfig) error {
	_, err := db.Exec(db.Rebind("SET FOREIGN_KEY_CHECKS = 0"))
	if err != nil {
		log.Error().
			AnErr("Error: ", err).
			Msg("Could not change foreign key checks to 0")
		return err
	}
	_, err = db.Exec(db.Rebind("TRUNCATE TABLE case_status"))
	if err != nil {
		log.Error().
			AnErr("Error: ", err).
			Msg("Could not truncate case_status")
		return err
	}
	_, err = db.Exec(db.Rebind("TRUNCATE TABLE filter_categories"))
	if err != nil {
		log.Error().
			AnErr("Error: ", err).
			Msg("Could not truncate filter_categories")
		return err
	}
	_, err = db.Exec(db.Rebind("TRUNCATE TABLE filter_excluded_categories"))
	if err != nil {
		log.Error().
			AnErr("Error: ", err).
			Msg("Could not truncate filter_excluded_categories")
		return err
	}
	_, err = db.Exec(db.Rebind("TRUNCATE TABLE filters"))
	if err != nil {
		log.Error().
			AnErr("Error: ", err).
			Msg("Could not truncate filters")
		return err
	}
	filterSplitter := regexp.MustCompile("; ")
	filters := filterSplitter.Split(input.Filters, -1)
	filtersSeperateSplitter := regexp.MustCompile(", ")
	sb := sqlbuilder.MySQL.NewInsertBuilder()
	sb.InsertInto("filters")
	sb.Cols("id", "filter")
	for i := range filters {
		sb.Values(filtersSeperateSplitter.Split(filters[i], -1)[0], filtersSeperateSplitter.Split(filters[i], -1)[1])
	}
	sql, args := sb.Build()
	query, err := sqlbuilder.MySQL.Interpolate(sql, args)
	if err != nil {
		log.Error().
			AnErr("Error", err).
			Msg("Could not build sql query")
		return err
	}
	_, err = db.Exec(query)
	if err != nil {
		log.Error().
			Msg("Could not execute query")
		return err
	}

	filterCategoriesSplitter := regexp.MustCompile(";\n")
	filterCategoriesSeperateSplitter := regexp.MustCompile(", ")
	filterCategories := filterCategoriesSplitter.Split(input.FilterCategories, -1)
	if filterCategories[0] != "" {
		sb = sqlbuilder.MySQL.NewInsertBuilder()
		sb.InsertInto("filter_categories")
		sb.Cols("filter_id", "category")
		for i := range filterCategories {
			if filterCategories[i] == "" {
				continue
			}
			sb.Values(filterCategoriesSeperateSplitter.Split(filterCategories[i], -1)[0], filterCategoriesSeperateSplitter.Split(filterCategories[i], -1)[1])
		}
		sql, args = sb.Build()
		query, err = sqlbuilder.MySQL.Interpolate(sql, args)
		if err != nil {
			log.Error().
				AnErr("Error", err).
				Msg("Could not build sql query")
			return err
		}
		_, err = db.Exec(query)
		if err != nil {
			log.Error().
				Msg("Could not execute query")
			return err
		}
	}

	excludedFilterCategories := filterCategoriesSplitter.Split(input.ExcludedFilterCategories, -1)
	if excludedFilterCategories[0] != "" {
		sb = sqlbuilder.MySQL.NewInsertBuilder()
		sb.InsertInto("filter_excluded_categories")
		sb.Cols("filter_id", "category")
		for i := range excludedFilterCategories {
			sb.Values(filterCategoriesSeperateSplitter.Split(excludedFilterCategories[i], -1)[0], filterCategoriesSeperateSplitter.Split(excludedFilterCategories[i], -1)[1])
		}
		sql, args = sb.Build()
		query, err = sqlbuilder.MySQL.Interpolate(sql, args)
		if err != nil {
			log.Error().
				AnErr("Error", err).
				Msg("Could not build sql query")
			return err
		}
		_, err = db.Exec(query)
		if err != nil {
			log.Error().
				Msg("Could not execute query")
			return err
		}
	}

	_, err = db.Exec(db.Rebind("SET FOREIGN_KEY_CHECKS = 1"))
	if err != nil {
		log.Error().
			AnErr("Error: ", err).
			Msg("Could not change foreign key checks to 1")
		return err
	}

	return nil
}

// ImportFilterFromConfig imports the filter config file.
func ImportFilterFromConfig(db *sqlx.DB) error {
	filterConfig := filterConfig{
		Filters:                  viper.GetString("filters"),
		FilterCategories:         viper.GetString("filter_categories"),
		ExcludedFilterCategories: viper.GetString("excluded_filter_categories"),
	}
	if (filterConfig.Filters == "") && (filterConfig.FilterCategories == "") && (filterConfig.ExcludedFilterCategories == "") {
		err := errors.New("Filter config is empty or not found!")
		log.Error().
			AnErr("Error", err)
		return err
	}
	err := ImportFilter(db, filterConfig)
	if err != nil {
		log.Error().
			AnErr("Error", err).
			Msg("Could not import config")
		return err
	}
	return nil
}

// ImportFilterFromBuffer imports the filter config from a buffer stream.
func ImportFilterFromBuffer(db *sqlx.DB, config []byte) error {
	filterConfig := filterConfig{}
	err := yaml.Unmarshal(config, &filterConfig)
	if err != nil {
		log.Error().
			AnErr("Error", err).
			Msg("Could not unmarshall config")
		return err
	}
	err = ImportFilter(db, filterConfig)
	if err != nil {
		log.Error().
			AnErr("Error", err).
			Msg("Could not import config")
		return err
	}
	return nil
}

// ExportFilterConfig exports the filter config.
func ExportFilterConfig(db *sqlx.DB) (string, error) {
	var filters []Filter
	err := db.Select(&filters, db.Rebind("SELECT * FROM filters"))
	if err != nil {
		log.Error().
			AnErr("Error", err).
			Msg("Could not execute query")
		return "", err
	}
	filterCategories, err := ExportFilterCategories(db)
	if err != nil {
		log.Error().
			AnErr("Error", err).
			Msg("Could not export filters")
		return "", err
	}
	excludedFilterCategories, err := ExportExcludedFilterCategories(db)
	if err != nil {
		log.Error().
			AnErr("Error", err).
			Msg("Could not export filters")
		return "", err
	}

	var output string
	if (len(filters) > 0) && (len(filterCategories) > 0) {
		output = "filters: "
		output = output + filters[0].Id + ", " + filters[0].Filter
		for i := 1; i < len(filters); i++ {

			output = output + "; " + filters[i].Id + ", " + filters[i].Filter
		}

		output = output + "\nfilter_categories: |"

		output = output + "\n " + filterCategories[0].FilterId + ", " + filterCategories[0].Category

		for i := 1; i < len(filterCategories); i++ {
			output = output + ";\n " + filterCategories[i].FilterId + ", " + filterCategories[i].Category
		}

		output = output + "\nexcluded_filter_categories: |"
		if excludedFilterCategories != nil {
			output = output + "\n " + excludedFilterCategories[0].FilterId + ", " + excludedFilterCategories[0].Category

			for i := 1; i < len(excludedFilterCategories); i++ {
				output = output + ";\n " + excludedFilterCategories[i].FilterId + ", " + excludedFilterCategories[i].Category
			}
		}
		return output, nil
	}
	log.Error().
		Msg("filters is equal to 0")
	return "", err
}

// AddFilterCategory adds a filter category.
func AddFilterCategory(db *sqlx.DB, filterId string, category string) error {
	var filterCategories []FilterCategory
	sb := sqlbuilder.MySQL.NewSelectBuilder()
	sb.Select("*").From("filter_categories").Where(sb.Equal("filter_id", filterId), sb.Equal("category", category))
	sql, args := sb.Build()
	query, err := sqlbuilder.MySQL.Interpolate(sql, args)
	if err != nil {
		log.Error().
			AnErr("Error", err).
			Msg("Could not build sql query")
		return err
	}
	err = db.Select(&filterCategories, query)
	if (err != nil) || (filterCategories != nil) {
		log.Error().
			AnErr("Error", err).
			Msg("filter category already in db")
		return errors.New("filter category already in db")
	}
	var categories []Category
	sb = sqlbuilder.MySQL.NewSelectBuilder()
	sb.Select("category").From("categories").Where(sb.Equal("category", category))
	sql, args = sb.Build()
	query, err = sqlbuilder.MySQL.Interpolate(sql, args)
	if err != nil {
		log.Error().
			AnErr("Error", err).
			Msg("Could not build sql query")
		return err
	}
	err = db.Select(&categories, query)
	if (err != nil) || (categories == nil) {
		log.Error().
			AnErr("Error", err).
			Msg("category not listed")
		return errors.New("category not listed")
	}
	sb2 := sqlbuilder.MySQL.NewInsertBuilder()
	sb2.InsertInto("filter_categories")
	sb2.Cols("filter_id", "category")
	sb2.Values(filterId, category)
	sql, args = sb2.Build()
	query, err = sqlbuilder.MySQL.Interpolate(sql, args)
	if err != nil {
		log.Error().
			AnErr("Error", err).
			Msg("Could not build sql query")
		return err
	}
	_, err = db.Exec(query)
	if err != nil {
		log.Error().
			AnErr("Error", err).
			Msg("Could not insert filter category into DB")
		return err
	}
	return nil
}

// DeleteFilterCategory deletes a filter category.
func DeleteFilterCategory(db *sqlx.DB, id string) error {
	var filterCategoriesDBList []FilterCategory
	sb := sqlbuilder.MySQL.NewSelectBuilder()
	sb.Select("*").From("filter_categories").Where(sb.Equal("id", id))
	sql, args := sb.Build()
	query, err := sqlbuilder.MySQL.Interpolate(sql, args)
	if err != nil {
		log.Error().
			AnErr("Error", err).
			Msg("Could not build sql query")
		return err
	}
	err = db.Select(&filterCategoriesDBList, query)
	if err != nil {
		log.Error().
			AnErr("Error", err).
			Msg("Could not get filters from DB")
		return err
	}
	if len(filterCategoriesDBList) == 0 {
		return errors.New("FilterId not in DB")
	}
	_, err = db.Exec(db.Rebind("DELETE FROM filter_categories WHERE id = ?"), id)
	if err != nil {
		log.Error().
			AnErr("Error", err).
			Msg("Could not delete FilterCategory from DB")
		return err
	}
	return nil
}

// ExportFilterCategories export all filter categories.
func ExportFilterCategories(db *sqlx.DB) ([]FilterCategory, error) {
	var filterCategories []FilterCategory
	err := db.Select(&filterCategories, db.Rebind("SELECT * FROM filter_categories"))
	if err != nil {
		log.Error().
			AnErr("Error", err).
			Msg("Could not execute query")
		return nil, err
	}
	return filterCategories, nil
}

// AddExcludedFilterCategory adds a filter category.
func AddExcludedFilterCategory(db *sqlx.DB, filterId string, category string) error {
	var filterCategories []FilterCategory
	sb := sqlbuilder.MySQL.NewSelectBuilder()
	sb.Select("*").From("filter_excluded_categories").Where(sb.Equal("filter_id", filterId), sb.Equal("category", category))
	sql, args := sb.Build()
	query, err := sqlbuilder.MySQL.Interpolate(sql, args)
	if err != nil {
		log.Error().
			AnErr("Error", err).
			Msg("Could not build sql query")
		return err
	}
	err = db.Select(&filterCategories, query)
	if (err != nil) || (filterCategories != nil) {
		log.Error().
			AnErr("Error", err).
			Msg("excluded filter category already in db")
		return errors.New("excluded filter category already in db")
	}
	var categories []Category
	sb = sqlbuilder.MySQL.NewSelectBuilder()
	sb.Select("category").From("categories").Where(sb.Equal("category", category))
	sql, args = sb.Build()
	query, err = sqlbuilder.MySQL.Interpolate(sql, args)
	if err != nil {
		log.Error().
			AnErr("Error", err).
			Msg("Could not build sql query")
		return err
	}
	err = db.Select(&categories, query)
	if (err != nil) || (categories == nil) {
		log.Error().
			AnErr("Error", err).
			Msg("category not listed")
		return errors.New("category not listed")
	}
	sb2 := sqlbuilder.MySQL.NewInsertBuilder()
	sb2.InsertInto("filter_excluded_categories")
	sb2.Cols("filter_id", "category")
	sb2.Values(filterId, category)
	sql, args = sb2.Build()
	query, err = sqlbuilder.MySQL.Interpolate(sql, args)
	if err != nil {
		log.Error().
			AnErr("Error", err).
			Msg("Could not build sql query")
		return err
	}
	_, err = db.Exec(query)
	if err != nil {
		log.Error().
			AnErr("Error", err).
			Msg("Could not insert excluded filter category into DB")
		return err
	}
	return nil
}

// DeleteExcludedFilterCategory deletes a filter category.
func DeleteExcludedFilterCategory(db *sqlx.DB, id string) error {
	var filterCategoriesDBList []FilterCategory
	sb := sqlbuilder.MySQL.NewSelectBuilder()
	sb.Select("*").From("filter_excluded_categories").Where(sb.Equal("id", id))
	sql, args := sb.Build()
	query, err := sqlbuilder.MySQL.Interpolate(sql, args)
	if err != nil {
		log.Error().
			AnErr("Error", err).
			Msg("Could not build sql query")
		return err
	}
	err = db.Select(&filterCategoriesDBList, query)
	if err != nil {
		log.Error().
			AnErr("Error", err).
			Msg("Could not get filters from DB")
		return err
	}
	if len(filterCategoriesDBList) == 0 {
		return errors.New("FilterId not in DB")
	}
	_, err = db.Exec(db.Rebind("DELETE FROM filter_excluded_categories WHERE id = ?"), id)
	if err != nil {
		log.Error().
			AnErr("Error", err).
			Msg("Could not delete excluded Filter Category from DB")
		return err
	}
	return nil
}

// ExportExcludedFilterCategories export all filter categories.
func ExportExcludedFilterCategories(db *sqlx.DB) ([]FilterCategory, error) {
	var filterCategories []FilterCategory
	err := db.Select(&filterCategories, db.Rebind("SELECT * FROM filter_excluded_categories"))
	if err != nil {
		log.Error().
			AnErr("Error", err).
			Msg("Could not execute query")
		return nil, err
	}
	return filterCategories, nil
}

func ImportCategories(db *sqlx.DB) error {
	categoriesConfig := categoriesConfig{
		viper.GetString("categories"),
	}
	if categoriesConfig.Categories == "" {
		err := errors.New("Category config is empty or not found!")
		log.Error().
			AnErr("Error", err)
		return err
	}
	_, err := db.Exec(db.Rebind("SET FOREIGN_KEY_CHECKS = 0"))
	if err != nil {
		log.Error().
			AnErr("Error: ", err).
			Msg("Could not change foreign key checks to 0")
		return err
	}
	_, err = db.Exec(db.Rebind("TRUNCATE TABLE categories"))
	if err != nil {
		log.Error().
			AnErr("Error: ", err).
			Msg("Could not truncate categories")
		return err
	}
	_, err = db.Exec(db.Rebind("SET FOREIGN_KEY_CHECKS = 1"))
	if err != nil {
		log.Error().
			AnErr("Error: ", err).
			Msg("Could not change foreign key checks to 1")
		return err
	}
	categoriesSplitter := regexp.MustCompile(";\n")
	categories := categoriesSplitter.Split(categoriesConfig.Categories, -1)
	sb := sqlbuilder.MySQL.NewInsertBuilder()
	sb.InsertInto("categories")
	sb.Cols("category")
	for i := range categories {
		if categories[i] == "" {
			continue
		}
		sb.Values(categories[i])
	}
	sql, args := sb.Build()
	query, err := sqlbuilder.MySQL.Interpolate(sql, args)
	if err != nil {
		log.Error().
			AnErr("Error", err).
			Msg("Could not build sql query")
		return err
	}
	_, err = db.Exec(query)
	if err != nil {
		log.Error().
			Msg("Could not execute query")
		return err
	}
	return nil
}