Project Set Selection

[tudorpopovici1]

Project set selection strategy

1. Query ‘packages’ table (for packages having vulnerable dependencies) to find the repository link for each package.
2. See whether the repository contains any dependabot config files
3. If so, add it to the project set selection

[nmook]

Get repositories of projects that have vulnerable dependencies

-- Find repositories with vulnerable dependencies for their latest version.
SELECT DISTINCT pac2.repository
FROM    dependencies AS dep,
        packages AS pac1,
        package_versions AS ver,
        packages AS pac2
WHERE 
    -- All vulnerable dependencies.
        dep.dependency_id IN (  
            SELECT package_id
        FROM package_versions, packages
        WHERE package_versions.package_id = packages.id AND 
              metadata ? 'vulnerabilities'
    ) AND 

    -- Only check last version of repos.
    ver.id IN (SELECT id FROM (
        SELECT id, MAX(version)
        FROM package_versions
        GROUP BY id
    ) AS mandatoryalias) AND

        dep.dependency_id = pac1.id AND 
        dep.package_version_id = ver.id AND
        ver.package_id = pac2.id AND
        pac2.repository IS NOT NULL;

[nmook]

Resulting repository URLs in JSON and CSV: vuln-repos.zip

[tudorpopovici1]

project-selection.zip

Zip has file processed_repo.txt which contains all projects that were analyzed during the scope of running the script, along with a true/false flag indicating whether the project uses dependabot or not + the status code of the response to the GitHub API for that project.

project-selection.csv, project-selection.json include all projects that have a vulnerable dependency and use dependabot.