Database and Storage Output
Overview
Owned paths/globs:
- spiderfoot/db.py
- modules/sfp__stor_db.py
- modules/sfp__stor_stdout.py
- test/…/test_spiderfootdb.py
- test/…/test_sfp__stor_db.py
- test/…/test_sfp__stor_stdout.py
- test/…/test_sfp__stor_db.py
- test/…/test_sfp__stor_stdout.py
spiderfoot/db.py is the SQLite persistence layer. SpiderFootDb.__init__() requires an options dict containing __database, creates the parent directory for that file, opens a SQLite connection, stores both the connection and cursor on the instance, and registers a REGEXP SQL function after confirming the schema exists. If tbl_scan_config is missing it creates the full schema; if only the correlation tables are missing it attempts a small pre-4.0 migration by running the schema queries containing correlation. All SQL access is serialized through the class-level dbhLock re-entrant lock because the same database handle is used from scan/module threads.
The schema in SpiderFootDb.createSchemaQueries defines the core storage tables: tbl_event_types for event metadata, tbl_config for global configuration, tbl_scan_instance for scan lifecycle metadata, tbl_scan_log for logs, tbl_scan_config for per-scan configuration snapshots, tbl_scan_results for events, and two correlation tables mapping correlation findings to result hashes. SpiderFootDb.eventDetails seeds the built-in event catalog into tbl_event_types; both create() and __init__(..., init=True) insert these rows and ignore duplicates on the init path.
Scan state is split by responsibility in spiderfoot/db.py. scanInstanceCreate(), scanInstanceSet(), scanInstanceGet(), scanInstanceList(), and scanInstanceDelete() manage rows in tbl_scan_instance and clean associated config/results/log rows on deletion. configSet(), configGet(), configClear(), scanConfigSet(), and scanConfigGet() store option maps using GLOBAL for non-module keys and component:option for module-scoped keys. scanLogEvent() and scanLogEvents() write tbl_scan_log, defaulting a missing component to SpiderFoot and storing timestamps in milliseconds.
Event persistence is centered on SpiderFootDb.scanEventStore() in spiderfoot/db.py. It accepts only a non-empty scan id and a SpiderFootEvent, validates that the event has string eventType, data, module, hash source metadata, float generated, and integer confidence/visibility/risk values in the 0-100 range, then inserts the event into tbl_scan_results. The stored fields are the scan id, event hash, event type, generated timestamp, confidence, visibility, risk, producing module, data, and source_event_hash; optional truncation is applied only to the stored data value before insert. ROOT events are special because their hash and source hash are both ROOT, and their module may be empty.
Query/report methods in spiderfoot/db.py read from the same tables and return raw SQLite result tuples for callers. search() joins child events, source events, and event type metadata and requires at least two criteria among scan id, event type, value, and regex. scanResultSummary() groups results by type, module, or entity; scanResultEvent() is the main event listing query and can filter by event type(s), source module(s), exact data, source event hash, correlation id, and false-positive flag. scanResultEventUnique(), scanResultHistory(), scanLogs(), and scanErrors() provide aggregate or log views. scanElementSourcesDirect(), scanElementChildrenDirect(), scanElementSourcesAll(), and scanElementChildrenAll() walk the event graph through hash and source_event_hash. correlationResultCreate(), scanCorrelationSummary(), and scanCorrelationList() create and read correlation findings and their linked events.
modules/sfp__stor_db.py is the built-in database storage plugin. It watches *, has priority 0, and is intentionally allowed to use the plugin database handle (SpiderFootPlugin.__sfdb__ is documented in spiderfoot/plugin.py as exceptional access for this module). setup() stores the SpiderFoot context and overlays user options. handleEvent() exits if _store is false, otherwise logs a debug message and calls self.__sfdb__.scanEventStore(self.getScanId(), sfEvent). If maxstorage is non-zero and len(sfEvent.data) exceeds it, the module passes maxstorage as the truncation size so the database receives a shortened data value while the event object remains unchanged.
modules/sfp__stor_stdout.py is the built-in command-line/stdout storage plugin. It also watches * and has priority 0, but it emits rather than persists events. handleEvent() suppresses ROOT, then either outputs all events or, when _showonlyrequested is true, only event types listed in _requested. output() normalizes event data and source data to strings, optionally strips newlines, removes <SFURL> tags, applies _maxlength, and prints in one of three formats: padded tabular output using _eventtypes[event.eventType], delimited CSV including module/type/source/data, or JSON based on SpiderFootEvent.asDict() with the event type replaced by the display label from _eventtypes. JSON output is streamed as comma-separated objects using the module-level firstEvent flag; it does not write array brackets.
The tests under test/…/test_spiderfootdb.py exercise constructor validation, type/value validation, and basic return shapes for the database API, with several behavior tests still marked todo or containing placeholder assertions. The module unit tests in test/…/test_sfp__stor_db.py and test/…/test_sfp__stor_stdout.py cover setup and watcher/producer return types while skipping strict option-description parity because both modules use private options. The integration tests in test/…/test_sfp__stor_db.py and test/…/test_sfp__stor_stdout.py sketch handleEvent() flows with SpiderFootEvent and SpiderFootTarget, but the actual tests are currently skipped.
Coupling
- If
tbl_scan_resultscolumns or the tuple order returned byscanResultEvent()in spiderfoot/db.py changes, also updatescanElementSourcesDirect()andscanElementChildrenDirect()in the same file; both comments state their output must align withscanResultEvent(), andscanElementSourcesAll()depends on fixed positions such as child hash at index8and parent hash at index9. - If
SpiderFootEventfields or validation semantics change, verify bothSpiderFootDb.scanEventStore()in spiderfoot/db.py and the output formatter in modules/sfp__stor_stdout.py. The database path re-validates event fields before insert, while stdout assumesevent.data,event.sourceEvent.data,event.asDict(), andevent.sourceEventHashhave the existing shapes. - If event type display data changes, keep
SpiderFootDb.eventDetailsin spiderfoot/db.py and the_eventtypesoption consumed by modules/sfp__stor_stdout.py consistent. Database reports join stored type IDs totbl_event_types, while stdout tab/csv/json labels index_eventtypes[event.eventType]directly and will fail if a printed event type lacks a display label. - If scan orchestration changes how modules receive database handles or scan ids, verify modules/sfp__stor_db.py: it uses
self.__sfdb__.scanEventStore()andself.getScanId()directly rather than routing through the generic event notification API. - If CLI output needs valid JSON documents, account for modules/sfp__stor_stdout.py streaming comma-separated objects without array delimiters. Any caller wrapping this output is responsible for printing surrounding brackets and handling the
firstEventstate.
Risks
- spiderfoot/db.py creates
tbl_scan_correlation_results.scan_instance_idwithREFERENCES tbl_scan_instances(guid), but the scan table is namedtbl_scan_instance. If SQLite foreign key enforcement is enabled or migration tooling validates referenced tables, correlation storage can fail or report an invalid schema. - spiderfoot/db.py commits once per event insert in
scanEventStore()and once per correlation-event mapping incorrelationResultCreate(). During high-volume scans, the storage plugin in modules/sfp__stor_db.py can spend significant time in SQLite commits and expose more lock contention despite the in-processdbhLock. - spiderfoot/db.py builds
IN (...)fragments inscanElementSourcesDirect()andscanElementChildrenDirect()by string interpolation after filtering hashes withisalnum(). That limits injection risk for current SHA-like hashes, but changing event hash formats to include non-alphanumeric characters will silently drop ids from graph traversal. - modules/sfp__stor_stdout.py assumes non-ROOT events always have
sourceEventpopulated beforeoutput()readsevent.sourceEvent.data. If a caller constructs an event with a missing or invalid source outside the normalSpiderFootEventpath, stdout emission will raise instead of producing partial output.