It is now so easy to view CVE using SQL

Before I start describing how to use this new SQL Table Function, I need to explain what CVE are.

Common Vulnerabilities and Exposures, CVE, are standardized, publicly identified cybersecurity flaws in software or firmware. When IBM, or a researcher, find a flaw in IBM i, or related software, it is given a unique id. These ids are cataloged globally by the MITRE Corporation, and published with their severity and impacts.

You can learn more about CVE on IBM's "What is CVE?" page.

I cannot find a CL command that allows me to view CVE details. I would have to search for a particular CVE using the IBM Product Security Central page.

With the latest Technology Refreshes, IBM i 7.6 TR2 and 7.5 TR8, is a new SQL Table Function that allows me view, and search, all of CVE from my IBM i partition.

The CVE_INFO Table Function is found in the library SYSTOOL, it has one parameter the IBM i release number. I went through and checked for each release to find that V5R3 is the earliest there are results for:

01 SELECT * 02 FROM TABLE (SYSTOOLS.CVE_INFO(IBMI_RELEASE => '5.3'))

The default for the parameter is the current release, therefore, I could just use the following for IBM i 7.6:

01 SELECT * 02 FROM TABLE (SYSTOOLS.CVE_INFO())

After running the above statement, I decided that if I used this on a regular basis the following columns would be most useful:

• SCORE:  Whether the CVE is "Critical", "High", "Medium", or "Low"
• CVE_ID:  CVE identifier
• SUMMARY:  CVE's summary text
• PUBLISH_DATE:  Date CVE was published
• IBMI_RELEASE:  The IBM i release
• X_FORCE_URL:  URL used by X-Force for the CVE

I can use this statement to get the CVE information for the current release:

01 SELECT SCORE,CVE_ID,SUMMARY,PUBLISH_DATE, 02 IBMI_RELEASE,X_FORCE_URL, 03 FROM TABLE (SYSTOOLS.CVE_INFO()) 04 WHERE PUBLISH_DATE > CURRENT DATE - 1 MONTH 05 ORDER BY SCORE,PUBLISH_DATE

I am not going to show the results as I can see an issue with them. If I sort by SCORE then the results will not be sorted in the order I want as when I sort the results, I find that "Low" comes before "Medium". What I need is a column that allows me sort the way I want.

This is where I used, what I call, a derived column. This is a column whose value is derived from the contents of other columns. Here I am going to create a new column based on the value in the SCORE column

01 CASE SCORE 02 WHEN 'Critical' THEN '1' 03 WHEN 'High' THEN '2' 04 WHEN 'Medium' THEN '3' 05 WHEN 'Low' THEN '4' 06 ELSE '0' 07 END

Line 1: I use this Case statement with the SCORE column.

Line 2: If value in SCORE is Critical then I assign is the value of '1'.

Lines 3 – 5: I assign values to the other Score values.

Line 6: I always add this line to my Case statements just in case the column could contain another value.

I inserted this into the previous SQL statement:

01 SELECT SCORE,CVE_ID,SUMMARY,PUBLISH_DATE, 02 IBMI_RELEASE,X_FORCE_URL, 03 CASE SCORE 04 WHEN 'Critical' THEN '1' 05 WHEN 'High' THEN '2' 06 WHEN 'Medium' THEN '3' 07 WHEN 'Low' THEN '4' 08 ELSE '0' 09 END AS "SCORE_RANK" 10 FROM TABLE (SYSTOOLS.CVE_INFO()) 11 WHERE PUBLISH_DATE > CURRENT DATE - 1 MONTH 12 ORDER BY SCORE_RANK,PUBLISH_DATE

Lines 3 – 9: Are the Case statement. I am calling this column SCORE_RANK.

Line 11: I only want the CVE published in the last month.

Line 12: I can sort by my derived column, SCORE_RANK and the Publish Date.

The first five results are:

PUBLISH IBMI_ SCORE SCORE CVE_ID SUMMARY _DATE RELEASE X-FORCE-URL _RANK -------- -------------- --------------- ---------- ------- ----------------------- ----- Critical CVE-2026-8633 IBM WebSpher... 2026-06-22 7.6 https://www.cve.org... 1 Critical CVE-2026-34182 OpenSSL for ... 2026-07-14 7.6 https://www.cve.org... 1 High CVE-2026-9072 IBM WebSpher... 2026-06-22 7.6 https://www.cve.org... 2 High CVE-2026-8858 IBM WebSpher... 2026-06-22 7.6 https://www.cve.org... 2 High CVE-2026-8620 IBM WebSpher... 2026-06-22 7.6 https://www.cve.org... 2

The columns ended with "..." I can expand to see all the details.

The contents of the X-FORCE-URL can be copied into browser to see all the information in a nicer format.

This is a great new security addition, I can see myself sharing this information with auditors.

 

You can learn more about the CVE_INFO SQL Table Function from the IBM website here.

 

This article was written for IBM i 7.6 TR2 and 7.5 TR8.