Start of change

CVE_INFO table function

The CVE_INFO table function returns a list of Common Vulnerabilities and Exposures (CVEs), where each CVE lists the requested IBM i release as an affected product. By default the list is applicable for the partition where the query is run.

This table function consumes data from the https://www.ibm.com/support/pages/securityapp/api/search search engine, focusing only on those entries that match a search on "IBM i" and have the specified IBM i operating system release listed within the "field_affected_products" key/value pair.

When queried, the table function uses the JSON_TABLE and SYSTOOLS.HTTPGETCLOB table functions to consume current security bulletin information from IBM support. If the partition cannot connect to the website, no rows are returned. When querying this table function, the job CCSID cannot be 65535 or the query will fail.

Authorization: See Note below.

Read syntax diagramSkip visual syntax diagram CVE_INFO (IBMI_RELEASE => ibmi-release )
The schema is SYSTOOLS.
ibmi-release
An expression that returns a string that contains the release level for the returned CVE information, formatted as version-number . release-number. For example, 7.6. The default is the release of the IBM i being used to run the query.

The result of the function is a table containing a row with the format shown in the following table. All the columns are nullable.

Table 1. CVE_INFO table function
Column name Data type Description
CVE_ID VARCHAR(20) CCSID 1208 The CVE identifier.
SCORE VARCHAR(20) CCSID 1208 The score assigned to the CVE.
  • Critical
  • High
  • Medium
  • Low
PUBLISH_DATE DATE The date when the CVE was published.
TITLE VARCHAR(2000) CCSID 1208 The title of the CVE.
IBM_SUPPORT_URL VARCHAR(200) CCSID 1208 The URL of the IBM support page which contains the complete details needed to understand this specific CVE, and any actions that should be taken to remediate the situation.
SUMMARY VARCHAR(2000) CCSID 1208 The summary text provided with the CVE.
DESCRIPTION VARCHAR(5000) CCSID 1208 The description provided with the CVE.
PRODUCT_ID VARCHAR(7) CCSID 1208 The product identifier.
PRODUCT_NAME VARCHAR(50) CCSID 1208 The name of the product.
IBMI_RELEASE CHAR(3) CCSID 1208 The IBM i release for this row, formatted like 7.6.
MODIFICATION_DATE DATE The date when the CVE was most recently modified.
X_FORCE_URL VARCHAR(100) CCSID 1208 The URL used by IBM X-Force for this CVE.
FIELD_VULNERABLITY_DETAILS CLOB(102400) CCSID 1208 Additional details returned by the IBM support page. The value is formatted HTML.
AFFECTED_PRODUCTS VARCHAR(3000) CCSID 1208 Product information returned by the IBM support page. The value is formatted HTML.

Notes

  • This table function is provided in the SYSTOOLS schema as an example of how to retrieve data from a web page using an SQL table function. Similar to other Db2® for i provided tools within SYSTOOLS, the SQL source can be extracted and used as a model for building similar interfaces, or to create a customized version within a user-specified schema.

    Services provided in SYSTOOLS have authorization requirements that are determined by the interfaces used to implement the service. To understand the authority requirements, extract the SQL for the service and examine the implementation.

Example

List the IBM i CVEs that have been published in the last month for the release of the IBM i where this query is run.

SELECT * FROM TABLE (SYSTOOLS.CVE_INFO()) 
   WHERE PUBLISH_DATE > CURRENT DATE - 1 MONTH;
End of change