Repeatedly updating the CVE library

[xu-xiang]

As shown After repeated execution-modified, it is found that the data in the database has been added repeatedly. The expected effect should be that no data is added, and existing data is updated.

[kotakanbe]

@xu-xiang Thanks for reporting. What is the git revisoin number(commit hash) of go-cve-dict you are using?

[xu-xiang]

root@ubuntu:~/go/bin# ./go-cve-dictionary -v go-cve-dictionary 0.1.1

[kotakanbe]

@xu-xiang please paste the output of below command.

$ cd $GOPATH/src/github.com/kotakanbe/go-cve-dictionary 
$ git rev-parse --short HEAD 

[xu-xiang]

And I found nvd.CveDetailID always == 0 in insertIntoNvd

func (r *RDBDriver) insertIntoNvd(cves []models.CveDetail) error {
	var err error
	var refreshedNvds []string
	bar := pb.New(len(cves))
	if c.Conf.Quiet {
		bar.Output = ioutil.Discard
	} else {
		bar.Output = os.Stderr
	}
	bar.Start()

	for chunked := range chunkSlice(cves, 10) {
		var tx *gorm.DB

		tx = r.conn.Begin()

		for _, c := range chunked {
			bar.Increment()

			//TODO rename
			old := models.CveDetail{}
			c.Nvd.CveID = c.CveID

			// select old record.
			result := tx.Where(&models.CveDetail{CveID: c.CveID}).First(&old)
			if result.RecordNotFound() || old.ID == 0 {
				if err = tx.Create(&c).Error; err != nil {
					tx.Rollback()
					return fmt.Errorf("Failed to insert. cve: %s, err: %s",
						pp.Sprintf("%v", c), err)
				}
				refreshedNvds = append(refreshedNvds, c.CveID)
				continue
			}

			if !result.RecordNotFound() {
				// select Nvd from db
				nvd := models.Nvd{}
				r.conn.Model(&old).Related(&nvd, "Nvd")

				if nvd.CveDetailID == 0 {
					c.Nvd.CveDetailID = old.ID
					// nvd.CveDetailID == 0 则创建一条新的nvd记录
					if err = tx.Create(&c.Nvd).Error; err != nil {
						tx.Rollback()
						return fmt.Errorf("Failed to insert. cve: %s, err: %s",
							pp.Sprintf("%v", c.Nvd), err)
					}
					refreshedNvds = append(refreshedNvds, c.CveID)
					continue
				}

[xu-xiang]

root@ubuntu:~/go/src/github.com/kotakanbe/go-cve-dictionary# git rev-parse --short HEAD c2bcc41

[kotakanbe]

@xu-xiang

I couldn't reproduce it. Please delete the DB and re-fetch.

[kotakanbe]

$ git rev-parse --short HEAD        
c2bcc41

 !  ~/g/s/g/k/go-cve-dictionary   *$…  go build ; and ./go-cve-dictionary fetchnvd -years 2017                                                                                  12.5s  Wed Aug  8 22:55:04 2018
 0 / 1 [---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------]   0.00%[Aug  8 22:55:10]  INFO Fetching... https://static.nvd.nist.gov/feeds/xml/cve/nvdcve-2.0-2017.xml.gz
 1 / 1 [==============================================================================================================================================================================================] 100.00% 1m0s
[Aug  8 22:56:11]  INFO Fetched 14906 CVEs
[Aug  8 22:56:11]  INFO Inserting NVD into DB (sqlite3).
[Aug  8 22:56:11]  INFO Inserting CVEs...
 14906 / 14906 [=======================================================================================================================================================================================] 100.00% 52s
[Aug  8 22:57:04]  INFO Refreshed 14906 Nvds.
 ~/g/s/g/k/go-cve-dictionary   *$…                                                                                                                                                1.9m  Wed Aug  8 22:57:05 2018
 ~/g/s/g/k/go-cve-dictionary   *$…  go build ; and ./go-cve-dictionary fetchnvd -years 2017                                                                                              Wed Aug  8 22:59:39 2018
 0 / 1 [---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------]   0.00%[Aug  8 22:59:40]  INFO Fetching... https://static.nvd.nist.gov/feeds/xml/cve/nvdcve-2.0-2017.xml.gz
 1 / 1 [=============================================================================================================================================================================================] 100.00% 1m13s
[Aug  8 23:00:54]  INFO Fetched 14906 CVEs
[Aug  8 23:00:54]  INFO Inserting NVD into DB (sqlite3).
[Aug  8 23:00:54]  INFO Inserting CVEs...
 14906 / 14906 [========================================================================================================================================================================================] 100.00% 4s
[Aug  8 23:00:59]  INFO Refreshed 0 Nvds.

[kotakanbe]

[xu-xiang]

This is in sqlite. that's ok

root@hk:~/go/bin# cd ../src/github.com/kotakanbe/go-cve-dictionary/
root@hk:~/go/src/github.com/kotakanbe/go-cve-dictionary# git rev-parse --short HEAD
c2bcc41
root@hk:~/go/src/github.com/kotakanbe/go-cve-dictionary# cd ~/go/bin/
root@hk:~/go/bin# ./go-cve-dictionary fetchnvd -modified
 0 / 1 [------------------------------------------------------------------------------------------------------------------------------------]   0.00%[Aug  8 23:00:28]  INFO Fetching... https://static.nvd.nist.gov/feeds/xml/cve/nvdcve-2.0-modified.xml.gz
 1 / 1 [=================================================================================================================================] 100.00% 5s
[Aug  8 23:00:34]  INFO Fetched 1101 CVEs
[Aug  8 23:00:34]  INFO Inserting NVD into DB (sqlite3).
[Aug  8 23:00:34]  INFO Inserting CVEs...
 1101 / 1101 [==========================================================================================================================] 100.00% 14s
[Aug  8 23:00:48]  INFO Refreshed 1101 Nvds.
root@hk:~/go/bin#
root@hk:~/go/bin# ./go-cve-dictionary fetchnvd -modified
 0 / 1 [------------------------------------------------------------------------------------------------------------------------------------]   0.00%[Aug  8 23:00:53]  INFO Fetching... https://static.nvd.nist.gov/feeds/xml/cve/nvdcve-2.0-modified.xml.gz
 1 / 1 [=================================================================================================================================] 100.00% 8s
[Aug  8 23:01:02]  INFO Fetched 1101 CVEs
[Aug  8 23:01:02]  INFO Inserting NVD into DB (sqlite3).
[Aug  8 23:01:02]  INFO Inserting CVEs...
 1101 / 1101 [===========================================================================================================================] 100.00% 0s
[Aug  8 23:01:02]  INFO Refreshed 0 Nvds.
root@hk:~/go/bin#

[xu-xiang]

But in mysql will insert again

root@hk:~/go/bin# docker rm -f mysql
mysql
root@hk:~/go/bin# sudo docker run --name mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=chHUIDCUAUaidfhasuadasuda  -d mysql:5
b68ae526bcd7016579f63f4cf0af00504c154ad22e2fad9910f480b8e88d37b6
root@hk:~/go/bin#
root@hk:~/go/bin#
root@hk:~/go/bin#
root@hk:~/go/bin# ./go-cve-dictionary fetchnvd -dbtype mysql -dbpath root:chHUIDCUAUaidfhasuadasuda@(127.0.0.1:3306)/cve?charset=utf8 -modified
 0 / 1 [------------------------------------------------------------------------------------------------------------------------------------]   0.00%[Aug  8 23:04:10]  INFO Fetching... https://static.nvd.nist.gov/feeds/xml/cve/nvdcve-2.0-modified.xml.gz
 1 / 1 [=================================================================================================================================] 100.00% 6s
[Aug  8 23:04:16]  INFO Fetched 1101 CVEs
[Aug  8 23:04:16] ERROR Failed to open DB. dbtype: mysql, dbpath: root:chHUIDCUAUaidfhasuadasuda@(127.0.0.1:3306)/cve?charset=utf8, err: Error 1049: Unknown database 'cve'
root@hk:~/go/bin#
root@hk:~/go/bin# docker exec -it mysql bash
root@b68ae526bcd7:/# mysql -uroot -h127.0.0.1 -pchHUIDCUAUaidfhasuadasuda
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.23 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database cve;
Query OK, 1 row affected (0.00 sec)

mysql> exit
Bye
root@b68ae526bcd7:/# exit
exit
root@hk:~/go/bin# ./go-cve-dictionary fetchnvd -dbtype mysql -dbpath root:chHUIDCUAUaidfhasuadasuda@(127.0.0.1:3306)/cve?charset=utf8 -modified
 0 / 1 [------------------------------------------------------------------------------------------------------------------------------------]   0.00%[Aug  8 23:05:32]  INFO Fetching... https://static.nvd.nist.gov/feeds/xml/cve/nvdcve-2.0-modified.xml.gz
 1 / 1 [=================================================================================================================================] 100.00% 4s
[Aug  8 23:05:37]  INFO Fetched 1101 CVEs
[Aug  8 23:05:37]  INFO Inserting NVD into DB (mysql).
[Aug  8 23:05:37]  INFO Inserting CVEs...
 1101 / 1101 [==========================================================================================================================] 100.00% 44s
[Aug  8 23:06:22]  INFO Refreshed 1101 Nvds.
root@hk:~/go/bin# ./go-cve-dictionary fetchnvd -dbtype mysql -dbpath root:chHUIDCUAUaidfhasuadasuda@(127.0.0.1:3306)/cve?charset=utf8 -modified
 0 / 1 [------------------------------------------------------------------------------------------------------------------------------------]   0.00%[Aug  8 23:06:26]  INFO Fetching... https://static.nvd.nist.gov/feeds/xml/cve/nvdcve-2.0-modified.xml.gz
 1 / 1 [=================================================================================================================================] 100.00% 4s
[Aug  8 23:06:30]  INFO Fetched 1101 CVEs
[Aug  8 23:06:30]  INFO Inserting NVD into DB (mysql).
[Aug  8 23:06:30]  INFO Inserting CVEs...
 1101 / 1101 [==========================================================================================================================] 100.00% 44s
[Aug  8 23:07:15]  INFO Refreshed 1101 Nvds.
root@hk:~/go/bin#
root@hk:~/go/bin#

[xu-xiang]

BUG should appear here when using mysql

				// select Nvd from db
				nvd := models.Nvd{}
				r.conn.Model(&old).Related(&nvd, "Nvd")

[kotakanbe]

@xu-xiang

Thanks, reproduced. I found out that more testing is required on mysql. I will do it next week.

[xu-xiang]

Mysql's DATETIME conversion caused this problem

Mysql does not seem to support DATE and TIME data types, must use the DATETIME type. Mysql does not seem to support the DATETIME value '0000-00-00 00:00:00.

Add parseTime to fix this:

fetchnvd -debug-sql -dbtype mysql -dbpath root:passwd@(IP:PORT)/database?parseTime=true -modified 

[kotakanbe]

@xu-xiang Thanks :)

This bug will be fixed in v0.2.0 released on 2018/8/27. https://github.com/kotakanbe/go-cve-dictionary/pull/75

[xu-xiang]

When use sqlite nvd.LastModifiedDate.Equal(c.Nvd.LastModifiedDate) is Ture , but in mysql is False.

fetchnvd -dbtype mysql -dbpath root:passwd@(IP:3306)/cve?parseTime=true -debug-sql -modified -http-proxy http://127.0.0.1:1087 

[kotakanbe]

The location is suspicious. I will investigate firmly.