Provide a query to search the online GIT repo for a YARA Rule
https://github.com/Yara-Rules/rules
VARIABLES
Yara rule file name STRING
-- Search for YARA RULES --
-- VARIABLE $$Yara rule file name$$ STRING
WITH RECURSIVE
-- Build a counter from 1 to 500
Counter(x) AS (VALUES (1) UNION ALL SELECT x+1 FROM Counter WHERE x < 500),
-- GET THE index of rules from the GIT REPO
Index_File(index_of_rules) AS (
SELECT REPLACE(REPLACE(result,'include ".',''),'"','')
FROM curl
WHERE url = 'https://raw.githubusercontent.com/Yara-Rules/rules/master/index.yar'
),
-- Build a list of the yara rules files from the public git repo based on the current index of rules
Rule_Files(Yara_Rule_URL) AS (
SELECT SPLIT(index_of_rules,CHAR(10),x) Yara_Rule_URL
FROM Index_file, Counter
WHERE Yara_Rule_URL LIKE '%$$Yara rule file name$$%'
),
-- Get the relevant YARA Signature Rules
Signature_Rules(Yara_Rule_URL, Yara_Sig_Rule ) AS (
SELECT Yara_Rule_URL, CAST('/*'||Yara_Rule_URL||'*/'||result AS TEXT) -- Insert the URL for the RULE into the Rule (HACK) We need it for the displayed results
FROM Rule_Files
JOIN curl ON url = 'https://raw.githubusercontent.com/Yara-Rules/rules/master/'||Yara_Rule_URL
)
SELECT
replace(Yara_Rule_URL, rtrim(Yara_Rule_URL, replace(Yara_Rule_URL, '/', '')), '') YARA_File,
'https://raw.githubusercontent.com/Yara-Rules/rules/master/'||Yara_Rule_URL,
YARA_Sig_Rule
FROM Signature_Rules
