Vulnerability Scanner in a query

This query will perform a very basic vulnerability scan. What is does is generate a list of all installed applications on the device and collect their publisher, name and version information.  We exclude things from the list that do not have version numbers  (These tend to be patches/hotfixes from microsoft). Now that we have that list we craft a curl statement to check the CVE site on the internet with the appropriate search criteria.  The curl request will return HTML and we need to check that HTML to see if it has any results in it.  We use a key word match for an indicator that only shows on the HTML page if a result was found.  If we have a match we then show the result information with the URL that the admin can use to get more information on the identified vulnerability.

WARNING this is not a 'real' vulnerability scanner in that it is only checking for applications, it uses a very crude method to identify the application and matching CVE's ect. 

I thought it would be a nice toy for folks that do not have professional vulnerability scanners as it is better than nothing...

Only works on Windows and Windows Server. Likely has FP and FN challanges

Have FUN

WITH program_list AS ( 
   SELECT
      REPLACE(REPLACE(REPLACE(name,',',' '),'+',' '),'.',' ') name,  -- STRIP out some characters not normally found in product names to improve chances of finding it in the CSV DB
      version,
      REPLACE(REPLACE(REPLACE(publisher,',',' '),'+',' '),'.',' ') publisher
   FROM programs 
   WHERE version > ''
   )
/******************************************************************************************************\
| We will search for the Publisher, Product Name and version and use some wild cards '%'' after we     | 
| create a simple one word name for the publisher and product. The expectation is that these three     | 
| pices of information should be relativly unique, but we can still get FPs                            |
\******************************************************************************************************/
SELECT 
   publisher, 
   CAST(name AS TEXT) || ' ' || version Application, 
   url 'Identified CVE List' 
FROM program_list 
   JOIN curl ON 
      url = 'https://www.cvedetails.com/version-search.php?vendor=' 
         || replace(program_list.publisher, ltrim(program_list.publisher, replace(program_list.publisher, ' ', '')), '') 
         || '%&product=' 
         || replace(program_list.name, ltrim(program_list.name, replace(program_list.name, ' ', '')), '') 
         || '%&version=' 
         || program_list.version
WHERE result 
   LIKE '%Details for%';