Searching inside payload data

Almost all of my searches involve IPs and/or port numbers, and Sguil has a lot of built-in support for these types of database queries, making them very easy to deal with. Sometimes, though, you want to search on something a little more difficult.

This morning, for example, I had a specific URL that was used in some PHP injection attack attempts, and I wanted to find only those alerts that had that URL as part of their data payload.

Constructing a query for this is actually pretty easy, if you use the HEX() and the CONCAT() SQL functions. If you're using the GUI interface, you only have to construct the WHERE clause, so you can do something like the following:

WHERE start_time >= "2007-07-09" \
AND data.data_payload like \
CONCAT("%", HEX("my.url.or.some.other.string"), "%")

The main problem with this type of query is that the data_payload field is not indexed, so it results in a table scan. You really need to make sure you have some other criteria that is indexed. In this case, I used the date to restrict the number of rows to search, but you could use IPs or port numbers as well.