Today, David Bianco showed me a way of creating SQL queries that I didn’t even know was possible. This is a technique with which it is possible to query the payload of Snort events in the Sguil database. These payloads are stored by Snort when it alerts and is the payload the actual rule triggered on. David showed a nice example of retrieving url’s for PHP url inclusion attacks.
I have written before about my usage of Mod_Security. I let Mod_Security respond with a 403 Forbidden message. Sadly, the alert generated by Mod_Security can not be displayed in Sguil. As somewhat of a replacement for that, I let Snort alert on the 403 Forbidden messages, so i can see in Sguil that Mod_Security blocked something. The disadvantage of this is that the 403 alert in itself does not contain much info. The sheer number of 403’s makes inspecting every single one with requesting a transcript a bit to much work.
This is where the new query comes in. The query creates a list of url’s that the server reported to be 403 Forbidden. Interesting is that we are not looking at an attack, but at an attack response. This means the the attackers IP is actually the destination IP.
SELECT COUNT(*) AS cnt, INET_NTOA(dst_ip) AS “Dest IP”, trim(LEADING “access ” FROM substring_index(substr(unhex(data_payload),locate(‘access ‘,unhex(data_payload))), ‘n’, 1)) AS url FROM event INNER JOIN data ON event.sid = data.sid and event.cid = data.cid where (signature like “%ATTACK-RESPONSES 403 Forbidden%”) GROUP BY dst_ip,url ORDER BY cnt DESC LIMIT 10;
The result is this:
As you can see, the first six results are from the comment spam I wrote about earlier. I left the source IP out because there is only one webserver. The query can easily be extended to show source IP’s as well.