crawler_handler.py

import logging 

from re import (
    compile, 
    IGNORECASE
)

from crawler.sources import (
    Source
)

from db import (
    create_connection,
    insert_data,
    find_id_by_params,
    update_data_by_id
)

log = logging.getLogger(__name__)
    
def crawler_handler(
    sources: list[Source]
) -> None:
    """
    Handles the processing and storage of parsed data from sources.

    :param sources: A list of Source objects containing parsed data.
    """
    
    cpe_pattern = compile(
        "^cpe\:2\.3\:[aho]\:(\w{0,64})\:(\w{0,64})\:"
    )
    
    cwe_pattern = compile(
        "^[cwe][A-z, -]{0,2}(\d{1,4})$"
    )
    
    conn = create_connection()

    for source in sources:
        try:
            source_title = source['title']
            source_priority = source['priority']
            source_contents = source['content']
            
        except KeyError as kErr:
            log.error(
                f"SourceHandleError: {kErr}"
            )
            continue

        for source_content in source_contents:
            try:
                cve_name = source_content['cve_id']

                insert_data(
                    conn=conn,
                    table_name="Cve",
                    query={
                        'name': cve_name
                    }
                )

                cve_id = find_id_by_params(
                    conn=conn,
                    table_name="Cve",
                    search_query={
                        'name': cve_name
                    }
                )

                insert_data(
                    conn=conn,
                    table_name="Event",
                    query={
                        'source': source_title,
                        'cve_id': cve_id
                    }
                )

            except KeyError as kErr:
                log.error(
                    f"Failed to extract `cve_id` from parsed content ({source_title})."
                )
                continue

            try:
                published_date = source_content['published']
                if published_date:
                    update_data_by_id(
                        conn=conn,
                        table_name="Cve",
                        query={
                            'published_date': published_date
                        },
                        row_id=cve_id
                    )

            except KeyError as kErr:
                log.error(
                    f"Failed to extract `published_date` from parsed content ({source_title})."
                )

            try:
                description = source_content['description']
                insert_data(
                    conn=conn,
                    table_name="Description",
                    query={
                        'value': description, 
                        'source': source_title, 
                        'priority': source_priority, 
                        'cve_id': cve_id
                    }
                )

            except KeyError as kErr:
                log.error(
                    f"Failed to extract `links` from parsed content ({source_title})."
                )

            try:
                link = source_content['links']
                insert_data(
                    conn=conn,
                    table_name="Link",
                    query={
                        'uri': link,
                        'cve_id': cve_id
                    }
                )

            except KeyError as kErr:
                log.error(
                    f"Failed to extract `links` from parsed content ({source_title})."
                )

            try:
                cvss = float(source_content['cvss'])
                if cvss:
                    update_data_by_id(
                        conn=conn,
                        table_name="Cve",
                        query={
                            'cvss': cvss
                        },
                        row_id=cve_id
                    )

            except KeyError as kErr:
                log.error(
                    f"Failed to extract `cvss` from parsed content ({source_title})."
                )

            try:
                cwe_content = source_content['cwe']
                cwe_number = int(
                    cwe_pattern.search(
                        cwe_content,
                        IGNORECASE,
                    ).group(1)
                )
                
                cwe_id = find_id_by_params(
                    conn=conn,
                    table_name="Cwe",
                    search_query={
                        'id': cwe_number
                    }
                )

                update_data_by_id(
                        conn=conn,
                        table_name="Cve",
                        query={
                            'cwe_id': cwe_id
                        },
                        row_id=cve_id
                )

            except KeyError as kErr:
                log.error(
                    f"Failed to extract `cwe_id` from parsed content ({source_title})."
                )
            except AttributeError as aErr:
                log.error(
                    f"Check format of the `cwe_id` in parsed content ({source_title})."
                )

            try:
                cpe = source_content['cpe']
                cpe_regex = cpe_pattern.search(
                    string=cpe
                )
                
                vendor_name = cpe_regex.group(1)
                product_name = cpe_regex.group(2)
                
                insert_data(
                    conn=conn,
                    table_name="Vendor",
                    query={
                        'name': vendor_name
                    }
                )
            
                vendor_id = find_id_by_params(
                    conn=conn,
                    table_name="Vendor",
                    search_query={
                        'name': vendor_name
                    }
                )
                
                insert_data(
                    conn=conn,
                    table_name="Product",
                    query={
                        'name': product_name,
                        'vendor_id': vendor_id
                    }
                )

                product_id = find_id_by_params(
                    conn=conn,
                    table_name="Product",
                    search_query={
                        'name': product_name
                    }
                )
                
                insert_data(
                    conn=conn,
                    table_name="Cpe",
                    query={
                        'value': cpe,
                        'vendor_id': vendor_id,
                        'product_id': product_id
                    }
                )

            except KeyError as kErr:
                log.error(
                    f"Failed to extract `cpe` from parsed content ({source_title})."
                )
            except Exception as exception:
                log.error(
                    f"Unexpected exception ({source_title}) was handled: {exception}."
                )