mysql cve search using duckdb

analyze_mysql_cve.sh

# git clone https://github.com/CVEProject/cvelistV5.git

#!/bin/bash

# usage: ./analyze_mysql_cve.sh 8.0.32

TARGET_VERSION="$1"

duckdb <<EOF
CREATE TABLE IF NOT EXISTS mysqlcve AS
    SELECT 
        cveMetadata.cveId,
        containers.cna.affected[1].product as product,
        coalesce(unnest(containers.cna.affected[1].versions).lessThanOrEqual,
        regexp_REPLACE(unnest(containers.cna.affected[1].versions).version, ' and prior|and earlier', '')) as version,   
        containers.cna.descriptions[1].value as description,
        coalesce(containers.cna.metrics[1].cvssV3_0.baseSeverity,containers.cna.metrics[1].cvssV3_1.baseSeverity) as severity,
        cveMetadata.datePublished as published_date
    FROM read_json('cvelistV5-main/cves/20*/*/*.json', union_by_name = true, ignore_errors = true)
    WHERE LOWER(containers.cna.affected[1].product) LIKE '%mysql server%'
ORDER BY published_date DESC;

SELECT *
FROM mysqlcve
WHERE
    CAST(split_part(version, '.', 1) AS INT) =  CAST(split_part('$TARGET_VERSION', '.', 1) AS INT)
    AND CAST(split_part(version, '.', 2) AS INT) = CAST(split_part('$TARGET_VERSION', '.', 2) AS INT)
    AND CAST(split_part(version, '.', 3) AS INT) <= CAST(split_part('$TARGET_VERSION', '.', 3) AS INT)
ORDER BY published_date DESC;
EOF