JSON to SQL database

General
1

Hi everyone,

Can you help me please with my project?
I'd like to download data from a website (here: https://nvd.nist.gov/vuln/data-feeds). It's a JSON file, and i'd like to insert the data to my sqlite database.
I created the database with the information i'd like to use, and I created a http request for the website.
I'd like to know, how can I insert the data from the JSON file to my databse correctly, because I'd like to create different selects and so on.
Any help would be wonderful.
Thank you.

2

If you feed it through a JSON node that should convert it to a javascript object, then you should be able to extract whatever data you need to put in the db.

3

I tried to use the JSON node, but I got an error: JSON parse error.

4

Well it isn't json then. Can you copy/paste it here, or a bit of it at least if it is large. Are you sure you are not getting xml? It seemed to be an option when I looked briefly at the link you posted.

5

Hi,
Yes I'm sure. I downloaded the zip file, and got a JSON. Here is the content:

 "configurations" : {
      "CVE_data_version" : "4.0",
      "nodes" : [ {
        "operator" : "OR",
        "cpe_match" : [ {
          "vulnerable" : true,
          "cpe23Uri" : "cpe:2.3:a:google:chrome:*:*:*:*:*:*:*:*",
          "versionEndExcluding" : "17.0.963.46"
        } ]
      } ]
    },
    "impact" : {
      "baseMetricV2" : {
        "cvssV2" : {
          "version" : "2.0",
          "vectorString" : "AV:N/AC:M/Au:N/C:P/I:P/A:P",
          "accessVector" : "NETWORK",
          "accessComplexity" : "MEDIUM",
          "authentication" : "NONE",
          "confidentialityImpact" : "PARTIAL",
          "integrityImpact" : "PARTIAL",
          "availabilityImpact" : "PARTIAL",
          "baseScore" : 6.8
        },
        "severity" : "MEDIUM",
        "exploitabilityScore" : 8.6,
        "impactScore" : 6.4,
        "acInsufInfo" : false,
        "obtainAllPrivilege" : false,
        "obtainUserPrivilege" : false,
        "obtainOtherPrivilege" : false,
        "userInteractionRequired" : true
      }
    },
    "publishedDate" : "2012-02-09T04:10Z",
    "lastModifiedDate" : "2020-04-17T13:31Z"
  }, {
    "cve" : {
      "data_type" : "CVE",
      "data_format" : "MITRE",
      "data_version" : "4.0",
      "CVE_data_meta" : {
        "ID" : "CVE-2011-3958",
        "ASSIGNER" : "cve@mitre.org"
      },
      "problemtype" : {
        "problemtype_data" : [ {
          "description" : [ {
            "lang" : "en",
            "value" : "CWE-416"
          } ]
        } ]
6

What is the exact error from the JSON node?

7

That's all:
msg : string[16]

"JSON parse error"

8

What do you mean you downloaded a zip file?

I thought you were accessing JSON directly?

Can you share exactly what URL you are accessing? The page you have linked to contains dozens of links to JSON data and zip files.

9

If you could export a short flow exhibiting the problem that would be even better.

10

That's not json as it is missing the opening {

Which file did you download ?

11

I am not sure that JSON requires that the content is always an object, rather than a sequence of items not contained in an object. See RFC 7158 - The JavaScript Object Notation (JSON) Data Interchange Format , particularly the second para of section 2.
However, in the context of the JSON node which is expected to output an object perhaps the node only accepts {..}.

12

No- see section 3. It is invalid Json.

13

Personally, I'd give them the benefit of the doubt that they have made a mistake in the copy and paste in the forum, given the website they have pointed to lists lots of valid JSON feeds.

But my questions still stand as to exactly which URL they are trying to download. If they have picked one that wraps the JSON as a zip file, then they won't be able to download it and treat it as JSON straight away.

14

Yes, on further consideration I think you are right. What it is saying is that a single json value is valid json (so "hello world" is valid json) but you can't have a sequence of them separated by commas unless they are in an object.

15

I always want to use the last modified ZIP file, from the website. In that case : Feed: CVE-Modified, Updated: 4/26/2020 12:01:08 PM -04:00 and from the download section the ZIP file, in which there is a JSON file, and I want to use that JSON file in a form that I can insert the data into the sqlite database.
Sorry for the questions, I'm really new, and I didn't find so much information about that case.

16

The example you posted isn't JSON because it does not start and end with { and }. Did you not see the posts saying that?
If they really are not there then you could add them and then try the JSON node.

17

@Colin that is not the issue here. The website only provides the data wrapped in a zip file. So the HTTP Request node cannot get the JSON directly - it gets the zip file which needs to be extracted before the JSON can be accessed.

@asdf123 here is a flow that gets the data, extracts the zip file and parses the JSON. You will need to install node-red-contrib-zip first.

[{"id":"483b0b05.a55344","type":"inject","z":"623e0198.c764b","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":140,"y":240,"wires":[["f2b63ad.3ca42c8"]]},{"id":"f2b63ad.3ca42c8","type":"http request","z":"623e0198.c764b","name":"","method":"GET","ret":"bin","paytoqs":false,"url":"https://nvd.nist.gov/feeds/json/cve/1.1/nvdcve-1.1-modified.json.zip","tls":"","persist":false,"proxy":"","authType":"","x":290,"y":240,"wires":[["9ec4df54.c26fd"]]},{"id":"f45acf66.a0adb","type":"debug","z":"623e0198.c764b","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":850,"y":240,"wires":[]},{"id":"9ec4df54.c26fd","type":"zip","z":"623e0198.c764b","name":"","mode":"decompress","filename":"","outasstring":false,"x":450,"y":240,"wires":[["aad8c1ac.d4cd2"]]},{"id":"473d6965.d4fe88","type":"json","z":"623e0198.c764b","name":"","property":"payload","action":"","pretty":false,"x":710,"y":240,"wires":[["f45acf66.a0adb"]]},{"id":"aad8c1ac.d4cd2","type":"function","z":"623e0198.c764b","name":"","func":"msg.payload = msg.payload[0].payload.toString()\nreturn msg;","outputs":1,"noerr":0,"x":590,"y":240,"wires":[["473d6965.d4fe88"]]}]
18

Since @asdf123 posted the unzipped contents I assumed he had worked out out how to do that, as he gave the impression that the string he posted was what he fed into the JSON node. Perhaps that is not the case though.

19

Thank you so much, I managed to solve the problem with your help.

20

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.