Vulnerabilities: finding affected consumers

[sebastian...]

Hi there,

I was wondering: what would be the best/recommended way to identify consuming packages/products of vulnerable packages?

We currently use OSS Index as a vulnerability source with automatic assessments. For products currently in development, this works fine (vulnerable packages are blocked). However, what happens, when a vulnerability is discovered after a product is released? We use pgscan to add consumer infos to packages for this purpose, but there does not seem to be a very convenient way to get from the list of vulnerabilities to the affected consumers.

We check the list of vulnerabilities on a regular basis and look for newly discovered vulnerabilities. However, there is no link from the vulnerability to the corresponding packages, never mind a list of consumers. That is why our current workflow is currently a bit cumbersome: we take the list of vulnerabilities, then open a new browser tab to search the corresponding feeds, copy & paste the names of the packages into feed's search, open the package's info package, search for the affected versions of the package and then navigate to "Usage & Statistics" to find the affected consumers. This does not seem to be very efficient. Surely, there must be a better way to do this...?

[stevedennis]

Hi @sebastian ,

Your current workflow sounds like it's the best approach for now, albeit cumbersome; there's also the possibility of directly querying the database, and generating a kind of report with your own tooling.

This is of course something we can consider improving, but it's hard to guess where without knowing more details and having real world datasets. So definitely an opportunity for a feature request / collaboration.

Let us know as you solidify your processes and learn what data you find valuable!

Thanks,
Steve

[sebastian...]

Hi Steve,
thanks for your reply. One thing that would make things a bit easier would be if there would be a link from each entry in the "Vulnerabilities" list to the corresponding package's page.

Example:
Let's say our ProGet server was named "myprogetserver.intranet" and our Nuget proxy feed (i.e. the feed that is connected to the api.nuget.org) was named "Nuget".
[CVE-2019-1302] defines a vulnerability for Microsoft.AspNetCore 2.2.0, so it would be great to have a link to myprogetserver.intranet/feeds/NuGet/Microsoft.AspNetCore/2.2.0/ or myprogetserver.intranet/feeds/NuGet/Microsoft.AspNetCore/2.2.0/vulnerabilities.
[CVE-2021-26701] defines a vulnerability for several versions of System.Text.Encodings.Web (4.0.1, 5.0.0, 4.7.1, 4.7.0, 4.6.0, 4.5.0, 4.0.0 and 4.4.0), so each displayed version should be a link to the corresponding page (myprogetserver.intranet/feeds/NuGet/System.Text.Encodings.Web/4.0.0/, myprogetserver.intranet/feeds/NuGet/System.Text.Encodings.Web/4.0.1/, ...).

I understand that this is a non-trivial request, because packages could occur in several different feeds and information of packages that have not been downloaded are not cached, but maybe one solution to this would be to assign default feeds per package type for the Vulnerabilities view? Of course the perfect solution would be to have an additional column in that view that would list all affected consumers of those packages.

Since you mentioned generating our own report: We are actually considering building our own tool to analyze & display packages and products and the dependencies between them. What would help as a lot for this would be a kind of generic API for all feeds and package types. What we would need is this:

• list of all feeds
• per feed: feed type and list of all packages in that feed
• per package: list of all versions of that package
• per package version: license information, dependencies and vulnerabilities

Basically, it would be very similar to the API for Universal feeds, just for all package types (or at least Nuget and npm).

Any thoughts on the points above?

Cheers,
Sebastian

[stevedennis]

Hi @sebastian, thanks for the feedback!

These both could be pretty complex (specially the API) and would thus end up as strategic, roadmap-level items (as opposed to some minor enhancements); we'd already have ProGet v2022 mostly planned out, so it would be a bit before we can consider it.

How's your relational database / SQL skills? If you're comfortable exploring the data in there, it might give you some insight / ideas into what we can do as a low-risk, minor-enhancement that would add a lot of value. Once you're able to see what is possible with the existing data, then it might be a view we can do.

We don't have a "guide" on how to query the tables, but we can assist if you have questions. The database columns should be familiar, but tables of interest: Vulnerabilities, FeedVulnerabilitySources (links feeds + vulnerability sources), and PackageDependents (package consumers).

Thanks,
Steve

[sebastian...]

Hi Steve,
we did consider the SQL approach, but didn't go with it initially, since (according to the documentation) it is not really a recommended way to interact with ProGet.
But if you say that you might consider supporting this scenario in the future, we can definitely look into it. Digging into the relational schema shouldn't be a problem. I will do some testing and update this thread when we have some results.

Cheers,
Sebastian

[stevedennis]

@sebastian great, let us know what you find!

When it comes to reporting/reading data, no problem directly querying the tables. We definitely support that, and some folks have quite advanced reports that tie together various systems.

I'd just go directly to SQL Server for that , and do SELECT on the appropriate views/tables. Let us know what questions / issues you have!

[sebastian...]

Hi @stevedennis,

we did a quick test and it turned out that connecting vulnerabilities to consumers of affected packages is actually pretty straight forward.

The following statement gives us all consumers:

SELECT 
	v.Vulnerability_Id,
	d.Dependent_Package_Name, 
	MIN(d.Dependent_Version_Text) AS Min_Dependent_Package_Version, 
	MAX(d.Dependent_Version_Text) AS Max_Dependent_Package_Version,  
	d.Package_Name, 
	d.Version_Text, 
	v.Score, 
	v.Title_Text 
FROM 
	PackageDependents d 
JOIN 
	Feeds f 
	ON d.Feed_Id = f.Feed_Id
JOIN 
	(SELECT * FROM Vulnerabilities CROSS APPLY STRING_SPLIT(Package_Versions, ',') ) v
	ON d.Package_Name = v.Package_Name 
	AND d.Version_Text = LTRIM(v.value)
	AND f.FeedType_Name = v.FeedType_Name
GROUP BY
	v.Vulnerability_Id,
	d.Dependent_Package_Name, 
	d.Package_Name, 
	d.Version_Text, 
	v.Score, 
	v.Title_Text
;

Another statement shows all downloads of affected packages (useful in case not all products already provide consuming data to ProGet):

SELECT
	v.Vulnerability_Id,
	d.Package_Id, 
	d.Version_Text, 
	v.Score, 
	v.Title_Text,
	d.User_Name,  
	MAX(d.Download_Date) AS Last_Downloaded,
	COUNT(*) AS Downloads_Count
FROM 
	PackageDownloads d 
JOIN 
	Feeds f 
	ON d.Feed_Name = f.Feed_Name
JOIN 
	(SELECT * FROM Vulnerabilities CROSS APPLY STRING_SPLIT(Package_Versions, ',') ) v
	ON d.Package_Id = v.Package_Name 
	AND d.Version_Text = LTRIM(v.value)
	AND f.FeedType_Name = v.FeedType_Name
GROUP BY 
	v.Vulnerability_Id,
	d.Package_Id, 
	d.Version_Text,
	d.User_Name,
	v.Score, 
	v.Title_Text
;

Note that in both cases we assume that Package_Versions is either a single version or a comma separated list of versions (this was the case for all the examples we have at the moment).

[Coming back to my original question, that is actually something you guys could integrate into the Vulnerabilities page of ProGet in a slightly modified way: Basically, group both statements by Vulnerability_Id to get the download count and consumers count and display those numbers in separate columns for a quick overview. When a user clicks on one of those numbers, you can display a detailed list for that specific vulnerability.]

One thing we could not find yet was information about the licensing of each package. That is an information which is displayed within ProGet on every package's overview page, but it is apparently not stored within the database (or maybe we just didn't find it)? This is something we would like to be able to link to consumers as well (which product uses which set of licenses?). Is there a simple way to get to that information?

Cheers,
Sebastian

[stevedennis]

@sebastian that's awesome, great you could figure it out!

The Package_Versions field is supposed to be some kind of range specifier (e.g. something like [3.4.4-3.4.4.8), but I don't know format offhand). However, we've also never seen it in the wild in any dataset. It's always just a CSV of versions.

Regarding licensing, that information is not really in the database. It's parsed from the manifest file (e.g. .nuspec) in the front-end. That file is stored in database, but it's practical to use in SQL. We talked about building a kind of job that would normalize that into a PackageLicenses table, and then allow custom reporting (or show how a consumer is using it).

At some point, we'd love to get a copy of your data (database backup if possible) so we can see some real-world consumers/consumption and build some pages from it. We do have some sort of idea in PRoGet how to make this look/work better, but seeing real data would be helpful. That development won't start until Q2, so maybe we'll reach out in a month or so and ask :)

[sebastian...]

Hi @stevedennis,

having a PackageLicense table would be great for two reasons:
a) It would give as the ability to do some reporting on used licenses.
b) There are lots of packages that use embedded license files. ProGet already has a feature where we can manually assign the corresponding license to a specific version of a package, but as far as I can tell, this is done by generating a pseudo URL for each package and assigning that URL to the corresponding license. While this does work fine as long as it is done only for a small number of packages, I'm not so sure how it affect usability (and maybe performance) when this is done on a larger scale. Having a separate table that connects packages (or package versions) to specific licenses might be a cleaner way to store and process that information.

I'd have to check whether we could give you a dump of our database, but we are only just starting to use ProGet, so there isn't really too much data there yet. The fact that we are new to this is actually the reason we are so interested in reports about licenses and vulnerabilities: We are setting up workflows, etc. and at some point we want to completely block downloads of vulnerable packages or packages that use restrictive/unknown licenses, but before we activate hard filtering rules we want to get an overview on how this would affect products currently in development (so we can adapt our rules or define exceptions). At the moment we are generating data by integrating pgscan to our build pipelines and gathering download statistics for packages (and hope that we will be able to analyze that data in a useful way), so feel free to reach out in a month or so. Maybe we will have enough meaningful data to be of use for you guys by then.

Cheers,
Sebastian