Adjust db query to output subdomains even if no relations exist

Heiko Kiesel · Heiko Kiesel · commit 5e5e030ed97b · 2023-08-25T15:24:00.000+02:00
Signed-off-by: Heiko Kiesel &lt;heiko.kiesel@iteratec.com&gt;
diff --git a/scanners/amass/parser/parser.js b/scanners/amass/parser/parser.js
@@ -7,7 +7,7 @@ const fs = require('fs');
 const path = require('path');
 const os = require('os');
 
-async function checkifTableExists(db){
+async function checkifTableExists(db) {
   const query = `select count(*) from sqlite_master m where m.name="assets" OR m.name="relations"`
 
   return new Promise((resolve, reject) => {
@@ -41,43 +41,41 @@ async function openDatabase(fileContent) {
 async function parse(fileContent) {
   const db = await openDatabase(fileContent);
   const tableExists = await checkifTableExists(db);
-  if(!tableExists) return [];
+  if (!tableExists) return [];
 
   return new Promise((resolve, reject) => {
 
     const query = `
     WITH relation_chain AS (
-      SELECT 
-          fqdn.content AS subdomain, 
-          ips.content AS ip,
-          cidr.content AS cidr,
-          asn.id AS asn_id,
-      asn.content AS asn
+      SELECT
+        fqdn.content AS subdomain, 
+            ips.content AS ip,
+            cidr.content AS cidr,
+            asn.id AS asn_id,
+        asn.content AS asn
       FROM assets fqdn
-    
-      JOIN relations r1 ON fqdn.id = r1.from_asset_id AND (r1.type = 'a_record' OR r1.type = 'aaaa_record')
-      JOIN assets ips ON r1.to_asset_id = ips.id
-    
-      JOIN relations r2 ON ips.id = r2.to_asset_id AND r2.type = 'contains'
-      JOIN assets cidr ON r2.from_asset_id = cidr.id
-    
-      JOIN relations r3 ON cidr.id = r3.to_asset_id AND r3.type = 'announces'
-      JOIN assets asn ON r3.from_asset_id = asn.id
-    
-      WHERE fqdn.type = 'FQDN'
-    )
       
-      SELECT 
+      LEFT JOIN relations r1 ON fqdn.id = r1.from_asset_id AND (r1.type = 'a_record' OR r1.type = 'aaaa_record')
+        LEFT JOIN assets ips ON r1.to_asset_id = ips.id
+        
+        LEFT JOIN relations r2 ON ips.id = r2.to_asset_id AND r2.type = 'contains'
+        LEFT JOIN assets cidr ON r2.from_asset_id = cidr.id
+        
+        LEFT JOIN relations r3 ON cidr.id = r3.to_asset_id AND r3.type = 'announces'
+        LEFT JOIN assets asn ON r3.from_asset_id = asn.id
+        
+        WHERE fqdn.type = 'FQDN'
+    )
+    SELECT
       rc.subdomain, 
       rc.ip, 
       rc.cidr, 
       rc.asn,
       a.content AS managed_by,
       (SELECT content FROM assets WHERE id = 1) AS domain
-      
-      FROM relation_chain rc
-      JOIN relations r ON rc.asn_id = r.from_asset_id AND r.type = 'managed_by'
-      JOIN assets a ON r.to_asset_id = a.id;`;
+    FROM relation_chain rc
+    LEFT JOIN relations r ON rc.asn_id = r.from_asset_id AND r.type = 'managed_by'
+    LEFT JOIN assets a ON r.to_asset_id = a.id;`;
 
     db.all(query, [], (err, rows) => {
       if (err) {
