exploitation_attempts_hunting_query.sql

--The query detects exploitation attempts based on related strings in the URI field that are logged by WAF/CDN security tools. 
--The query detects the exploitation of parameters defined in GET and POST requests in the URI. Hence, in case your application utilizes a specific parameter with StringSubstitutor which hasn’t been provided in the URI and therefore doesn’t exist in the CDN logs, it won’t be detected.


--Cloudflare
SELECT EDGE_START_TIMESTAMP,
       CLIENT_IP,
       CLIENT_REQUEST_USER_AGENT,
       CLIENT_REQUEST_METHOD,
       -- decoded URI
       REGEXP_REPLACE(REGEXP_REPLACE(CLIENT_REQUEST_URI, '(F){1,}',' '), '(%|[2]){0,}', '')        DECODED_URL,
       CLIENT_REQUEST_HOST,
       COALESCE(ORIGIN_RESPONSE_STATUS,
               CACHE_RESPONSE_STATUS)                                                             RESPONSE_CODE,
       IFF(RESPONSE_CODE ILIKE '20%','Might Be Successful','Failed attempt')                      VULN_STATUS,
       WAF_ACTION
  FROM RAW.CLOUDFLARE_HTTP
 WHERE
        -- adjust time-frame
       EDGE_START_TIMESTAMP >= CURRENT_TIMESTAMP - INTERVAL '7 days'
       -- relevant schemas to the vulnerable methods in StringSubstitutor
       AND CLIENT_REQUEST_URI ILIKE  ANY ('%${script%', '%${data%', '%${localhost%', '%${base64encoder%', '%${const%', '%${based64%', '%${dns%', '%${env%', '%${urldecoder%', '%${urlt%', '%${resourcebundlet%', '%${filet%', '%${javat%', '%${xml)t%','%$urlencoder%')
       -- common Java methods to execute code
      AND CLIENT_REQUEST_URI ILIKE ANY ('%java.lang.processBuilder%','%java.lang.runtime%exec%')
;

--AWS WAF
SELECT EVENT_TIME                                                                EVENT_TIME,
       HTTP_REQUEST_CLIENT_IP                                                    HTTP_REQUEST_CLIENT_IP,
       HTTP_REQUEST_COUNTRY                                                      HTTP_REQUEST_COUNTRY,
       HTTP_REQUEST_HEADERS                                                      HTTP_REQUEST_HEADERS,
       HTTP_REQUEST_URI                                                          HTTP_REQUEST_URI,
       -- decoded URI
       REGEXP_REPLACE(REGEXP_REPLACE(HTTP_REQUEST_URI, '(F){1,}',' '), '(%|[2]){0,}', '')        DECODED_URL
  FROM RAW.AWS_WAF
 WHERE
       -- adjust time-frame
       EVENT_TIME >= CURRENT_TIMESTAMP - INTERVAL '7 days'
       -- relevant schemas to the vulnerable methods in StringSubstitutor
       AND HTTP_REQUEST_URI ILIKE  ANY ('%${script%', '%${data%', '%${localhost%', '%${base64encoder%', '%${const%', '%${based64%', '%${dns%', '%${env%', '%${urldecoder%', '%${urlt%', '%${resourcebundlet%', '%${filet%', '%${javat%', '%${xml)t%','%$urlencoder%')
       -- common methods to execute code with Java
       AND HTTP_REQUEST_URI ILIKE ANY ('%java.lang.processBuilder%','%java.lang.runtime%exec%')