Need help to request all package vulnerabilities in ProGet 2024 version

[fabrice.mejean]

Hi,

In proget 2023 I was able to export all vulnerable packages :

SELECT
      [Package_Name]
	  ,[Package_Versions]
	  ,[Score]
	  ,[ScoreSeverity_Text]
	  ,[AssessmentType_Name]
	  ,'' as WillBeAssessTo
	  ,[Assessment_Date]
	  ,[BlockDownload_Indicator]
	  ,[Description_Text]
	  ,[External_Id]
	  ,[Feed_Id]
	  ,[FeedType_Name]
	  ,[Severity_Code]
	  ,[Title_Text]
	  ,[Vulnerability_Id]
	  ,[VulnerabilitySource_Id]
	  ,[VulnerabilitySource_Name]      
  FROM [ProGet].[dbo].[Vulnerabilities_Extended]
  where [FeedType_Name] = 'nuget'
  order by Score desc

now I just found this request but I don't have the version but just the AffectedVersions_Text:

SELECT
      ppne.[Package_Name]
	  ,ppne.AffectedVersions_Text	  
	  ,pve.Score
	  ,pve.ScoreSeverity_Text
	  ,pve.AssessmentType_Name
	  ,'' as WillBeAssessTo
	  ,pve.Assessment_Date
	  ,pve.Details_Text 
	  ,[External_Id]
	  ,ppne.PackageType_Name 
	  ,pve.Severity_Code
	  ,pve.Summary_Text 
	  ,pve.PgvdId_Text
	  ,pve.CveId_Text 
	  ,pve.BlockDownload_Indicator 
  FROM ProGet.dbo.PgvdPackageNames_Extended as ppne
inner join ProGet.dbo.PgvdVulnerabilities_Extended as pve on pve.Pgvd_Id = ppne.Pgvd_Id 
  where ppne.PackageType_Name = 'nuget'
  order by pve.Score desc

Have you got any idea on how to request it like I did before ?

Many thanks for your help.

Regards,

Fabrice MEJEAN

[atripp]

Hi @fabrice-mejean ,

It's no longer possible to query this information from the database.

As you've noticed, ProGet now uses a version range (i.e. AffectedVersions_Text ) to determine whether a package is vulnerable or not. So instead of 4.2.3 it's now 4.2.3-4.2.8 or [4.2.*) or something like that.

Unfortunately it's not practical/feasible to parse this information unless you were to rewrite a substantial amount of ecosystem-specific parsing logic - this would be basically impossible to do in a SQL query.

Instead, you'll need to use the upcoming pgutil packages meatdata command to see what vulnerabilities a particular packages has. You can also use Notifiers to address newly-discovered vulnerabilities.

Thanks,
Alana