SPLK-1002 Splunk Core Certified Power User – Splunk Use Cases Of All Industries

1. Security Use Case: SQL Injection Detection in Splunk

In this video we will be seeing a security industry related use case in splunk that is sql injection attack detection using splunk. In our lab setup on Amazon aws I have installed a vulnerable application which is used for penetration testing and made sure to collect all the logs and send to a splunk installed vulnerable application is called bwap. If you would like to know more about this application, it is listed in the description, check it out. In this application there is sql injection vulnerability which we will be exploiting and investigating on it using our splunk so that we understand how this attack requests are intercepted by our application and determine a way to correlate this event and alert depending upon it.

For sql injection we will be using kali linux which is installed in my local PC and using this vm we will be launching attack on the vulnerable machine on our aws and the logs will be forwarded to our splunk instance which is also in the aws. In kali linux we have a utility known as sql Map which is widely used for exploiting sql injection vulnerabilities. We will be using the same utility and go through two use cases, that is sql injection without evading techniques and sql injection with evading techniques. The first method, that is where a hacker has recently learned about sql map and trying to exploit it without much evading techniques, whereas the second method, it consists of encoding the payload or tampering with the payload in order to evade ips and ids signatures.

Now let us see the first scenario where I have not used any bypass techniques, just trying to exploit the vulnerability. So this is our bwap. I have logged in and I’ve got the session ID using this add on and we’ll be using this session ID to log into our bwap application using sql Map. This is our splunk instance where as you can see now we are receiving the logs from our bwap application and the most important parameter here is user agent, where we have seen three different user agents as of now and the uri which are all being accessed. Now let us go ahead and initiate the attack and see how the logs look once an attack is being performed on our vulnerable application. So this is my Karl linux sql map. I’m using the sql Map utility and this is our vulnerable url and I’m using with the authentication that is this session ID and we’ll be listing the databases from this application.

Let me run this and in the meantime I’ll go to this and keep it real time in order to see the logs. What we are getting when an attacker is attacking our site as you can see, there are a lot of junk values which as of now we don’t understand. Let us see. Once the attack is complete, we’ll see a pattern in these urls and try to identify and create an alerts based on these scenarios. Is our attack complete? Yes, it has successfully listed all the available databases. That means skill injection was successful. Let us stop this. As you can see, it didn’t even take more than five minutes. It was just two minutes. So now let us see what are all the evidences that we collected as part of this sql vulnerability application.

You see there are three different user agents. That is sql map, mozilla Fi and mozilla phi which were different, that is some other crawling websites. Or this was my user agent that was being accessed period to attack. So let us nail down into sql map. So this is the user agent. By now we should know search queries which are acceptable in splunk. That is I’ll be searching using wild card whichever the user agent contains sql map. As you can see now we have lot of junk values. We are not able to understand what it is. And this parameter is the uri. It contains lot of junk values which is nothing. But this has been intercepted by your web server because it cannot handle the spaces it is being replaced by percentage 20. Now let us decode this url for this plunk as an inbuilt function which can decode urls which is called as url decode and I’m assigning it back to the uri field itself. Let me run this for last 15 minutes and let’s see what is the decoded url looks like as it is visible. It is an sql injection statement. That is it is testing for a different conditions in order to validate whether sql injection is successful or not. Based on these scenarios, we’ll be able to create our alert. Let’s say the first indication was the user agent which was the tool name itself.

So whenever you run an sql map without any evading techniques, it gives away your user agents as sql map itself. And the second one is the uris. The uri contains the actual sql code that is executed on your database. So this is nothing, but basically it is testing multiple combination in order to validate whether this application is vulnerable or not. So these logs we are able to successfully get it from a splunk. This will become one scenario.

That is for our first scenario where we’ll be able to see the url queries and the user agent is sql map. Now let us consider the second techniques where the user is performing change of user agent and also tampering with the payload. Here the payload is straightforward. It is nothing but just testing for the similar one equal to one condition which is always true in order to bypass your authentication to the database. Now let us see the second method. For the second method, I’ll be using this sql map command. In this command I’m writing this complete output whatever the sql map command generates into a temp file and I provided batch in order to stop looking for answers like this where the user input is required in order to proceed.

If you provide batch, it just continues without any issues and there is no need for ignore proxy because we are not using any proxies and we are automatically assigning a random agent that is your user agent so that sql map will not give away any information. It will pick up automatically any of the valid user agents. Say there’s a mosul or Chrome or Safari depending on whichever value that pops up during this attack. The tamper parameter is used for encoding or bypassing your ips ids solutions. Similarly the iphone U followed by the complete vulnerable url with a valid session. And we are listing again just the databases. Let us see how our logs look. Now I’ll keep it real time last five minute window. Since user agent there is nothing, it is not showing up anything because our user agent will be changed because we have selected random user agent.

As you can see now there is an sql injection attempt, but it is reporting it as Safari or a modular user agent. So this is the user agent it randomly picked up, which is reported as a macintosh and it is using Safari browser. Now let us see how we can identify this attack. I believe this attack was also successful and it has successfully listed our databases. So let me stop this real time search and I’ll use a search time for last five minutes search time filter. We know now the user agent string that is sql map will not filter us our results, but we still know the uri. If you look at the uri, it still contains the malicious payload that was sent by the attacker. So what do I do? I’ll go ahead and decode this url.

Let me have a look how the uri looks because real time overrides the first time so I’ll not be able to see it. So here it is. Now it has successfully decoded our uri. If you see the uri there is a consistent pattern which gives away that it is an illegal url query. If you see closely, there is an and condition in almost all the request and also there are order by and. Always the and is followed by digit equals another four random digits. This is a common pattern that we are able to see from the sql map that is trying to penetrate our environment. We’ll see how we can filter this one using our Rejects command. We’ll see in our previous videos how to use Rejects command.

Now we’ll be using Rejects command to identify just the sql injection attempts that were performed on our website using sql map. So this is nothing but a regular rejects where it is looking for and followed by a space, followed by four digits or multiple digits and it is separated by equal symbol. So this is the syntax that it is looking for. Let me rerun the search. Now we have 36 events. Let’s see how many events will get filtered out. Now we have only 211 events which contained and four digits followed by another four digits separated with the equal sign. So this is reject searching. Now let us see what were the urls under time, client IP, user agent and also Location.

Let’s quickly grab the location using IP location command. This is our client IP. Perfect. So here we have the time of attack that was performed and the uri which was used. From the uri itself we can clearly identify this is not a regular url request. It is a penetration attempt using a scale injection. This is the client IP. This is the user agent used by the client IP to perform this attack. And this is the country it is originating from, which is nothing but my details. But this user agent as you can see in the first scenario it was giving away easily saying it is an sql map tool, but now it is difficult to identify it was an sql map attempt or a manual query. In this way we can create multiple use cases in splunk without the need for any signatures or dependency on other security products.

These alerts I have already created earlier, let us see if they are triggered. Perfect. While we are demonstrating, I had previously created these alerts and these are run at a scheduled interval so that it can pick it up. As you can see it has successfully picked up and I have said the severity is critical. Now we know there was an sql injection attempt that was performed on our website. If you click on view results you will be able to see the results and as of now the action for this event is just to log it as a triggered event. The actions can be you can send it to respective team based on smtp alerts that is sending out an email saying sql injection attack was identified using a user agent known sql map or sql injection attack type two that is using Rejects command.

We have identified there was an sql injection attempt and it can be sent to respective teams. But there is also another option parents Plunk. You can schedule an alert to invoke a script which can take actions. Let’s say for this scenario the best action would be past this client IP in your script, that is from the output of splunk to your script and this script will be able to log into your perimeter firewall and block this IP without the need of human intervention. So this is just a workaround. There are much more options which you can take to avoid what this kind of attack. But it is always good to fix the code in order to permanently eliminate these kind of attacks.