Hunting: Webserver SQL Injection

But why would someone try and hack me?

Famous last words...

SQL Injection attacks are not limited to large corporations that have juicy data to extract. There are many reasons why an attacker may target you.

• There are essentially an infinite amount of "bad bots" that are constantly scanning the internet to find vulnerabilities and sometimes selling this information, other times selling initial access
• Actors may simply be looking to steal credit card numbers, browser cookies (bank account mostly), typically some kind of financial motivation
• Sophisticated actors love to compromise residential networks in order to force their traffic to traverse the network like a VPN. This allows them to blend in when obtaining initial access to a large organization.
• Plus a myriad of other reasons

Now that we have the why out of the way, let's get into the how to detect this. Again, I'm a huge Splunk guy so my solution is going to be implemented through SPL (Splunk Processing Language) and will assume you have your web server logs going into Splunk. If you do not have this START DOING IT. Even if you're self hosting you're never going to regret having too many logs and you never know when you're going to need it.

What is the URI Query?

We are going to be looking for injection attempts utilizing the URI query. The URI query or query string is a feature used by web servers typically to serve dynamic content. The parameters are typically in key value pairs.
Ex:

/ghost/api/content/tiers/?include=monthly_price%2Cyearly_price%2Cbenefits&key=notarealkey&limit=all

However, whenever serving dynamic content, there's always some inherent risk as these parameters are controlled by the client, meaning we the administrator/developer need to handle input with a grain of salt. This is where the injection attempts come into play.

Common SQL Injection Techniques

As the name implies, SQL injection is a attack technique targeting SQL. If you're familiar or even if you're not, SQL is a database that contains keys and values. These values are retrieved using SQL statements, these are the commands you send to the server to tell it what you want to retrieve and what functions you want to apply. There are some specific commands that we are going to be searching for as they're the most common seen in injection attempts. These are as followed:

Union
Select
Drop
Alter
Update

These all have the ability to modify database tables or select data from the tables they should not be able to retrieve.

Additionally, there are some special characters used to terminate SQL statements, attackers use these to terminate the expected statement from the web server and proceed to run their own. These are as followed:

• String Terminators
  • '
  • "
• Comments
  • /*
  • */
  • --
• Command Terminators
  • ;
  • 1=1 (or equivalent)

Now that we all know what we're looking for, lets get into the SPL.

Writing SPL

index=caddy sourcetype=caddy:caddy uri_query=*
``` Rename fields for easier reference/eval statements ```
| rename resp_headers.Location{} AS location_header request.tls.version AS tls_version

``` Include https? in URL, just makes it prettier ```
| eval url = if(isnull(tls_version),"http://".url, "https://".url)

``` Create the location header that states if they were redirected ```
| eval location_header = if(isnull(location_header), "No Redir", location_header)

``` This decodes the query so we can look for the strings themselves ```
| eval uri_query = urldecode(uri_query)

``` This is where the magic comes in, regex to match on what we discussed ```
| regex uri_query="(?i)(union.*select|select.*from|insert.*into|drop.*table|update.*set|alter.*table--|;|or\s+\d=\d|\d=\d\s+--|--\s*|</?script>|/\*|\*/|\.\./|alert\(|^[^\']*\'[^\']*$|^[^\"]*\"[^\"]*$)"

``` Table out events for easier analysis ```
| table _time status src_ip http_host url user_agent http_referrer location_header
| sort -_time

A few things that this is looking for that were not previously discussed:

• </?script>
  • The ? in regex implies that the previous character is optional, meaning this will match both <script> and </script>
• alert(
  • This is looking for the alert statement, many actors will try this method first as the attacker is able to view and see if the javascript is now executing on the page.

Running this search will return results similar to the following:

If you don't want to view the encoded URL, you can add an eval to decode the field.

index=caddy sourcetype=caddy:caddy uri_query=*
| rename resp_headers.Location{} AS location_header request.tls.version AS tls_version

``` Decode can be added here: ```
| eval url = urldecode(if(isnull(tls_version),"http://".url, "https://".url))

| eval location_header = if(isnull(location_header), "No Redir", location_header)
| eval uri_query = urldecode(uri_query)
| regex uri_query="(?i)(union.*select|select.*from|insert.*into|drop.*table|update.*set|alter.*table--|;|or\s+\d=\d|\d=\d\s+--|--\s*|</?script>|/\*|\*/|\.\./|alert\(|^[^\']*\'[^\']*$|^[^\"]*\"[^\"]*$)"
| table _time status src_ip http_host url user_agent http_referrer location_header
| sort -_time

Wrap Up

Now that you have the data, this is where the interesting part begins. Based on the data you now have to investigate if an exploitation took place. You cannot base this strictly on the HTTP status code returned, a 200 doesn't necessarily mean an exploitation took place, and a 404 also doesn't mean no exploitation took place. Often times the best place to start is if there is a SQL statement, go check your database and ensure no data was modified. If they are attempting to inject JavaScript, I would simply perform a similar query but instead of their functions, just attempt to execute alert("1") and if you get an alert with 1 as the text, you know that they were able to successfully inject.
Responding to an incident is outside of the scope of this post as this already feels pretty long. However if you find anything juicy feel free to contact me via a comment or email me [email protected].

Happy Hunting!