Detecting Time-based SQLI

Credits: Patrick Smith

Purpose

This post details real-world analysis of an incident response where the logging gods were not on my side (thanks redacted client) and how I was able to use an incovential way to detect time-based SQL injection exploitation through commonly available logging mechanisms that may be overlooked.

Background

My company was contracted to assist in a CSIR (Cybersecurity Incident Response) effort. At the tme of first contract, the initial means of compromise were unknown.

The objectives were simple:

  1. Discover the initial means of compromise
  2. Evaluate what information was potentially acccessed by the threat
  3. Mitigate the current attack and provide future guidance

Story

This post will cover objective one. After discovering a malicious binary dropped by the threat(s), it was analyzed for any sort of C2 callbacks. This provided an IP address. Naturally, the first thing I did was used Crowdstrike to search for all machines that have made any of outbound newtwork connections to the known IP address, which can be used as an IOC (indicator of compromise) for other machines on the network. While it’s normal for externally facing hosts to be receiving a ton of unknown inbound connections, connections being built OUTBOUND to unknown IPs are a hugeee red flag.

An application server, which was serving a publicly-facing web applications, happened to be calling back to this IP address. External web applications are very prone to being attacked, so this was a good place to start. Whenever I find a malicious IP address, I always note down the ISP (hosting provider), the country, and the range. I try to build a database of signatures on the threat(s). By doing this, I can make subsequent searches or just keep my eye out for other “sketchy” IP addresses that may be related to the threat(s). When checking inbound connections to all of the known hosts on the client’s network from the known-malicious IP address, the application server had significantly more than any others, and it was all port 443 (HTTPS/the webserver). Unfortunately, this client did not have logs/the ability to view the entirety of the conents of the HTTPS traffic, such as TLS inspection, so a big challenge was trying to figure out what the attacker(s) were doing with this webserver.

I went straight to the webserver logs, in this case the webserver was IIS. When it comes to webserver logs, there are a two primary types:

  1. Access logs
    • Logs every HTTP request to the webserver, but not the entire HTTP request (it wont contain all of the HTTP headers or the body of requests)
  2. Error logs
    • Usually not very helpful for the goal here.

IIS Access log analysis

By default,IIS access logs are configured to use the W3C file format which includes the following fields related to logged HTTP requests:

*Important reminder: IIS access logs do NOT log certain aspects of HTTP requests, such as the bodies of HTTP requests, this means discovering attacks that distribute payloads via the body of HTTP requests and HTTP headers can be challenging and often yield inconclusive results without definitive empirical evidence. *

For this scenario, two IP addresses will be mentioned. They will be referred to as Attacker-IP-A and Attacker-IP-B, both belonging to the attacker.

The suspect IP address began making connections to the web application on 2/8. The following files were requested:

193 requests: /login.aspx 2 requests: /WebResource.axd 1 request: /Scripts/WebForms/MsAjax/MicrosoftAjaxWebForms.js 1 request: /Style/buttons.css 1 request: /Scripts/WebForms/MsAjax/MicrosoftAjax.js 1 request: /favicon.ico

Identifying Anomolous HTTP Traffic

The request breakdown shows nearly all of the traffic going directly to the login.aspx page which is very uncommon behavior for normal users and even automated scanners. Normal behavior would return many more HTTP GET requests as the user browses the website and the browser loads static assets. Of all of these requests, 193 were HTTP POST, and only 6 were HTTP GET. This is extremely irregular behavior. For a vulnerability to be exploited, there is typically preliminary exploration of the website and other fuzzing, however, these requests are directly targeting /login.aspx, signifying the original reconassiance and fuzzing was likely performed from different IP addresses.

The first HTTP request received from is an HTTP POST request to /login.aspx. This is another irregularity and indication of programmatic exploitation as normal users would need to usually visit the website, for example, https://website.com/, then manually traverse to https://website.com/login.aspx by clicking around. This activity will create HTTP GET requests in the process; because this is not consistent with what is being seen within the logs, this strengthens the narrative that other IP addresses are being used.

Example log entry:

2022-02-08 06:34:24 <redacted internal IP> POST /login.aspx - 443 - <Attacker-IP-A> Mozilla/5.0+(Macintosh;+U;+PPC+Mac+OS+X;+en-us)+AppleWebKit/125.5.5+(KHTML,+like+Gecko)+      Safari/125.11 - 302 0 0 242

Further analysis of the traffic from revealed an extremely odd consistent behavior of consecutive very long response times of 15,000 milliseconds (15 seconds). For perspective, normal response times were noted to be within the range of 200-500 milliseconds.

Important note: The last field of the logs (f.e 15249 on the first entry) is the amount of time it took the webserver to serve the HTTP response.

Format reminder:

[Time] [Source IP] [HTTP method] [URL] [Source port] [User-Agent] [Referer] [HTTP Status Code] [sc-substatus] [sc-win32-status] [HTTP Response Time Taken]

IIS access log entries:

 03:24:23 <redacted internal IP> POST /login.aspx - 443 - <Attacker-IP-A> Mozilla/5.0+(Windows;+U;+Windows+NT+6.1;+en-US)+AppleWebKit/534.13+(KHTML,+      like+Gecko)+Chrome/9.0.597.0+Safari/534.13 - 200 0 0 15249
 03:24:39 <redacted internal IP> POST /login.aspx - 443 - <Attacker-IP-A> Mozilla/5.0+(Windows;+U;+Windows+NT+6.1;+en-US)+AppleWebKit/534.13+(KHTML,+      like+Gecko)+Chrome/9.0.597.0+Safari/534.13 - 200 0 0 15246
 03:24:55 <redacted internal IP> POST /login.aspx - 443 - <Attacker-IP-A> Mozilla/5.0+(Windows;+U;+Windows+NT+6.1;+en-US)+AppleWebKit/534.13+(KHTML,+      like+Gecko)+Chrome/9.0.597.0+Safari/534.13 - 200 0 0 15249
 03:25:11 <redacted internal IP> POST /login.aspx - 443 - <Attacker-IP-A> Mozilla/5.0+(Windows;+U;+Windows+NT+6.1;+en-US)+AppleWebKit/534.13+(KHTML,+      like+Gecko)+Chrome/9.0.597.0+Safari/534.13 - 200 0 0 15242
 03:25:11 <redacted internal IP> POST /login.aspx - 443 - <Attacker-IP-A> Mozilla/5.0+(Windows;+U;+Windows+NT+6.1;+en-US)+AppleWebKit/534.13+(KHTML,+      like+Gecko)+Chrome/9.0.597.0+Safari/534.13 - 302 0 0 242
 03:25:27 <redacted internal IP> POST /login.aspx - 443 - <Attacker-IP-A> Mozilla/5.0+(Windows;+U;+Windows+NT+6.1;+en-US)+AppleWebKit/534.13+(KHTML,+      like+Gecko)+Chrome/9.0.597.0+Safari/534.13 - 200 0 0 15245
 03:25:43 <redacted internal IP> POST /login.aspx - 443 - <Attacker-IP-A> Mozilla/5.0+(Windows;+U;+Windows+NT+6.1;+en-US)+AppleWebKit/534.13+(KHTML,+      like+Gecko)+Chrome/9.0.597.0+Safari/534.13 - 200 0 0 15245
 03:25:58 <redacted internal IP> POST /login.aspx - 443 - <Attacker-IP-A> Mozilla/5.0+(Windows;+U;+Windows+NT+6.1;+en-US)+AppleWebKit/534.13+(KHTML,+      like+Gecko)+Chrome/9.0.597.0+Safari/534.13 - 200 0 0 15243
 03:26:14 <redacted internal IP> POST /login.aspx - 443 - <Attacker-IP-A> Mozilla/5.0+(Windows;+U;+Windows+NT+6.1;+en-US)+AppleWebKit/534.13+(KHTML,+      like+Gecko)+Chrome/9.0.597.0+Safari/534.13 - 200 0 0 15244
 03:26:29 <redacted internal IP> POST /login.aspx - 443 - <Attacker-IP-A> Mozilla/5.0+(Windows;+U;+Windows+NT+6.1;+en-US)+AppleWebKit/534.13+(KHTML,+      like+Gecko)+Chrome/9.0.597.0+Safari/534.13 - 200 0 0 15247
 03:26:45 <redacted internal IP> POST /login.aspx - 443 - <Attacker-IP-A> Mozilla/5.0+(Windows;+U;+Windows+NT+6.1;+en-US)+AppleWebKit/534.13+(KHTML,+      like+Gecko)+Chrome/9.0.597.0+Safari/534.13 - 200 0 0 15246
 03:27:00 <redacted internal IP> POST /login.aspx - 443 - <Attacker-IP-A> Mozilla/5.0+(Windows;+U;+Windows+NT+6.1;+en-US)+AppleWebKit/534.13+(KHTML,+      like+Gecko)+Chrome/9.0.597.0+Safari/534.13 - 200 0 0 15242
 03:27:16 <redacted internal IP> POST /login.aspx - 443 - <Attacker-IP-A> Mozilla/5.0+(Windows;+U;+Windows+NT+6.1;+en-US)+AppleWebKit/534.13+(KHTML,+      like+Gecko)+Chrome/9.0.597.0+Safari/534.13 - 200 0 0 15248
 03:27:31 <redacted internal IP> POST /login.aspx - 443 - <Attacker-IP-A> Mozilla/5.0+(Windows;+U;+Windows+NT+6.1;+en-US)+AppleWebKit/534.13+(KHTML,+      like+Gecko)+Chrome/9.0.597.0+Safari/534.13 - 200 0 0 15246
 03:27:47 <redacted internal IP> POST /login.aspx - 443 - <Attacker-IP-A> Mozilla/5.0+(Windows;+U;+Windows+NT+6.1;+en-US)+AppleWebKit/534.13+(KHTML,+      like+Gecko)+Chrome/9.0.597.0+Safari/534.13 - 200 0 0 15242
 03:28:02 <redacted internal IP> POST /login.aspx - 443 - <Attacker-IP-A> Mozilla/5.0+(Windows;+U;+Windows+NT+6.1;+en-US)+AppleWebKit/534.13+(KHTML,+      like+Gecko)+Chrome/9.0.597.0+Safari/534.13 - 200 0 0 15246
 

The 15,000 millisecond response time was used as marker for possible exploitation attempts. I reviewed neighoring HTTP requests for similar behaviors, expecting a rotation in IP addresses. The following entries were discovered (spoiler: the same anomolous behavior):

   07:33:16 <redacted internal IP> POST /login.aspx - 443 - <Attacker-IP-B> Mozilla/5.0+(Windows;+U;+Windows+NT+5.0;+en-US)+AppleWebKit/532.0+(KHTML,+like+Gecko)+           Chrome/3.0.198+Safari/532.0 - 200 0 0 15256
  [NORMAL TRAFFIC] 07:33:27 <redacted internal IP> GET /keepalive.ashx - 443 - 73.211.202.213 Mozilla/5.0+(Windows+NT+10.0;+Win64;+x64)+AppleWebKit/537.36+(KHTML,+like+Gecko)+Chrome/        97.0.4692.99+Safari/537.36 https://website.com/quote.aspx?quoteid=2202013 302 0 0 50
  [NORMAL TRAFFIC] 07:33:27 <redacted internal IP> GET /keepalive.ashx - 443 - 73.211.202.213 Mozilla/5.0+(Windows+NT+10.0;+Win64;+x64)+AppleWebKit/537.36+(KHTML,+like+Gecko)+Chrome/        97.0.4692.99+Safari/537.36 https://website.com/frmQuoteSearch.aspx 302 0 0 52
 
   07:33:27 <redacted internal IP> GET /~login.aspx - 443 - 73.211.202.213 Mozilla/5.0+(Windows+NT+10.0;+Win64;+x64)+AppleWebKit/537.36+(KHTML,+like+Gecko)+Chrome/        97.0.4692.99+Safari/537.36 https://website.com/quote.aspx?quoteid=2202013 404 0 0 52
   07:33:27 <redacted internal IP> GET /keepalive.ashx - 443 - 73.211.202.213 Mozilla/5.0+(Windows+NT+10.0;+Win64;+x64)+AppleWebKit/537.36+(KHTML,+like+Gecko)+Chrome/        97.0.4692.99+Safari/537.36 https://website.com/quote.aspx?quoteid=2202013 302 0 0 54
   07:33:27 <redacted internal IP> GET /~login.aspx - 443 - 73.211.202.213 Mozilla/5.0+(Windows+NT+10.0;+Win64;+x64)+AppleWebKit/537.36+(KHTML,+like+Gecko)+Chrome/        97.0.4692.99+Safari/537.36 https://website.com/frmQuoteSearch.aspx 404 0 0 50
   07:33:27 <redacted internal IP> GET /~login.aspx - 443 - 73.211.202.213 Mozilla/5.0+(Windows+NT+10.0;+Win64;+x64)+AppleWebKit/537.36+(KHTML,+like+Gecko)+Chrome/        97.0.4692.99+Safari/537.36 https://website.com/quote.aspx?quoteid=2202013 404 0 0 61
   07:33:27 <redacted internal IP> GET /keepalive.ashx - 443 - 73.211.202.213 Mozilla/5.0+(Windows+NT+10.0;+Win64;+x64)+AppleWebKit/537.36+(KHTML,+like+Gecko)+Chrome/        97.0.4692.99+Safari/537.36 https://website.com/quote.aspx?quoteid=2202061 302 0 0 91
   07:33:27 <redacted internal IP> GET /~login.aspx - 443 - 73.211.202.213 Mozilla/5.0+(Windows+NT+10.0;+Win64;+x64)+AppleWebKit/537.36+(KHTML,+like+Gecko)+Chrome/        97.0.4692.99+Safari/537.36 https://website.com/quote.aspx?quoteid=2202061 404 0 0 51
  
Suspected IP: 07:33:29 <redacted internal IP> POST /login.aspx - 443 - <Attacker-IP-A> Mozilla/5.0+(Windows+NT+10.0;+Win64;+x64;+rv:96.0)+Gecko/20100101+Firefox/96.0 https://         website.com/login.aspx 200 0 0 15103

A second IP address <Attacker-IP-B> was discovered to be making the same HTTP POST requests to the same login.aspx page with the same 15,000 millisecond response times, within 20 seconds of each other. This is further signal of other IP addresses being used. Inspection of traffic of <Attacker-IP-B> both in Splunk and IIS access logs shows significantly more HTTP traffic and connections predating the usage of <Attacker-IP-A>.

The IP address made the following HTTP requests:

22,298 requests: /login.aspx 
0 requests: /WebResource.axd
0 request: /Scripts/WebForms/MsAjax/MicrosoftAjaxWebForms.js 
0 request: /Style/buttons.css 
0 request: /Scripts/WebForms/MsAjax/MicrosoftAjax.js 
0 request: /favicon.ico requests 1 times

Every request from was an HTTP POST to /login.aspx. This behavior is extremely similar to what has been observed with , and even more irregular with no HTTP GET requests. A total of 4,570 HTTP requests from took over 5,000 milliseconds for the webserver to process and return a response.

Example:

 11:15:57 <redacted internal IP> POST /login.aspx - 443 - <Attacker-IP-B> Mozilla/5.0+(X11;+U;+Linux+x86_64;+en-US;+rv:1.9.2.9)+Gecko/20100915+Gentoo+Firefox/3.6.         9 - 200 0 0 15257
 44580
 11:16:13 <redacted internal IP> POST /login.aspx - 443 - <Attacker-IP-B> Mozilla/5.0+(X11;+U;+Linux+x86_64;+en-US;+rv:1.9.2.9)+Gecko/20100915+Gentoo+Firefox/3.6.         9 - 200 0 0 15257
 44582
 11:16:28 <redacted internal IP> POST /login.aspx - 443 - <Attacker-IP-B> Mozilla/5.0+(X11;+U;+Linux+x86_64;+en-US;+rv:1.9.2.9)+Gecko/20100915+Gentoo+Firefox/3.6.         9 - 200 0 0 15257
 44584
 11:16:44 <redacted internal IP> POST /login.aspx - 443 - <Attacker-IP-B> Mozilla/5.0+(X11;+U;+Linux+x86_64;+en-US;+rv:1.9.2.9)+Gecko/20100915+Gentoo+Firefox/3.6.         9 - 200 0 0 15264
 44586
 11:16:59 <redacted internal IP> POST /login.aspx - 443 - <Attacker-IP-B> Mozilla/5.0+(X11;+U;+Linux+x86_64;+en-US;+rv:1.9.2.9)+Gecko/20100915+Gentoo+Firefox/3.6.         9 - 200 0 0 15253
 44588
 11:17:15 <redacted internal IP> POST /login.aspx - 443 - <Attacker-IP-B> Mozilla/5.0+(X11;+U;+Linux+x86_64;+en-US;+rv:1.9.2.9)+Gecko/20100915+Gentoo+Firefox/3.6.         9 - 200 0 0 15257
 44590
 11:17:30 <redacted internal IP> POST /login.aspx - 443 - <Attacker-IP-B> Mozilla/5.0+(X11;+U;+Linux+x86_64;+en-US;+rv:1.9.2.9)+Gecko/20100915+Gentoo+Firefox/3.6.         9 - 200 0 0 15257
 44592
 11:17:46 <redacted internal IP> POST /login.aspx - 443 - <Attacker-IP-B> Mozilla/5.0+(X11;+U;+Linux+x86_64;+en-US;+rv:1.9.2.9)+Gecko/20100915+Gentoo+Firefox/3.6.         9 - 200 0 0 15259
 44594
 11:18:01 <redacted internal IP> POST /login.aspx - 443 - <Attacker-IP-B> Mozilla/5.0+(X11;+U;+Linux+x86_64;+en-US;+rv:1.9.2.9)+Gecko/20100915+Gentoo+Firefox/3.6.         9 - 200 0 0 15264

Identifying the issue

At this point, anomolous traffic has been identified, but what is causing it? How and why would an attacker influence HTTP response times? The answer: time-based SQL injection.

Time-based SQL Injection is an inferential SQL Injection technique that relies on sending an SQL query to the database which forces the database to wait for a specified amount of time before responding, in the case of SQL server, that is typically WAIT FOR DELAY. The response time will indicate to the attacker whether the result of the query is TRUE or FALSE. Essentially, an injected query, in English would read: If the first character of the password of the user 'patrick' is 'a', then sleep for 10 seconds. If the first character is indeed ‘a’, the sleep command is executed, and the HTTP response will be delayed for 10 seconds, which can easily be measured by an attacker.

Given the abnormal attention to /login.aspx, I began to audit this page for signs of vulnerability. The client provided source code to the relevant web applications and through code review of the application, I was able to identify the SQL injection vulnerability then confirmed it to exist with live dynamic testing.

Time App IP HTTP method URL Source port Source IP User-Agent Referer HTTP Status Code HTTP Response Time Taken      
03:24:23 intip POST /login.aspx 443 Attacker-IP-A Mozilla/5.0+(Windows;+U;+Windows+NT+6.1;+en-US)+AppleWebKit/534.13+(KHTML,+like+Gecko)+Chrome/9.0.597.0+Safari/534.13 - 200 0 15249      
03:24:39 intip POST /login.aspx 443 Attacker-IP-A Mozilla/5.0+(Windows;+U;+Windows+NT+6.1;+en-US)+AppleWebKit/534.13+(KHTML,+like+Gecko)+Chrome/9.0.597.0+Safari/534.13 - 200 0 15246      
03:24:55 POST /login.aspx 443 Attacker-IP-A Mozilla/5.0+(Windows;+U;+Windows+NT+6.1;+en-US)+AppleWebKit/534.13+(KHTML,+like+Gecko)+Chrome/9.0.597.0+Safari/534.13 - 200 0 15249      
03:25:11 <intip POST /login.aspx 443 Attacker-IP-A Mozilla/5.0+(Windows;+U;+Windows+NT+6.1;+en-US)+AppleWebKit/534.13+(KHTML,+like+Gecko)+Chrome/9.0.597.0+Safari/534.13 - 200 0 15242      
03:25:11 <intip POST /login.aspx 443 Attacker-IP-A Mozilla/5.0+(Windows;+U;+Windows+NT+6.1;+en-US)+AppleWebKit/534.13+(KHTML,+like+Gecko)+Chrome/9.0.597.0+Safari/534.13 302 242          
03:25:27 <intip POST /login.aspx - 443 - Mozilla/5.0+(Windows;+U;+Windows+NT+6.1;+en-US)+AppleWebKit/534.13+(KHTML,+like+Gecko)+Chrome/9.0.597.0+Safari/534.13 - 200 0 0 15245
03:25:43 <intip POST /login.aspx - 443 - Mozilla/5.0+(Windows;+U;+Windows+NT+6.1;+en-US)+AppleWebKit/534.13+(KHTML,+ike+Gecko)+Chrome/9.0.597.0+Safari/534.13 - 200 0 0 15245
03:25:58 <intip POST /login.aspx - 443 - Mozilla/5.0+(Windows;+U;+Windows+NT+6.1;+en-US)+AppleWebKit/534.13+(KHTML,+like+Gecko)+Chrome/9.0.597.0+Safari/534.13 - 200 0 0 15243
03:26:14 <intip POST /login.aspx - 443 - Mozilla/5.0+(Windows;+U;+Windows+NT+6.1;+en-US)+AppleWebKit/534.13+(KHTML,+like+Gecko)+Chrome/9.0.597.0+Safari/534.13 - 200 0 0 15244
03:26:29 <intip POST /login.aspx - 443 - Mozilla/5.0+(Windows;+U;+Windows+NT+6.1;+en-US)+AppleWebKit/534.13+(KHTML,+like+Gecko)+Chrome/9.0.597.0+Safari/534.13 - 200 0 0 15247
03:26:45 <intip POST /login.aspx - 443 - Mozilla/5.0+(Windows;+U;+Windows+NT+6.1;+en-US)+AppleWebKit/534.13+(KHTML,+like+Gecko)+Chrome/9.0.597.0+Safari/534.13 - 200 0 0 15246
03:27:00 <intip POST /login.aspx - 443 - Mozilla/5.0+(Windows;+U;+Windows+NT+6.1;+en-US)+AppleWebKit/534.13+(KHTML,+like+Gecko)+Chrome/9.0.597.0+Safari/534.13 - 200 0 0 15242
03:27:16 <intip POST /login.aspx - 443 - Mozilla/5.0+(Windows;+U;+Windows+NT+6.1;+en-US)+AppleWebKit/534.13+(KHTML,+like+Gecko)+Chrome/9.0.597.0+Safari/534.13 - 200 0 0 15248
03:27:31 <intip POST /login.aspx - 443 - Mozilla/5.0+(Windows;+U;+Windows+NT+6.1;+en-US)+AppleWebKit/534.13+(KHTML,+like+Gecko)+Chrome/9.0.597.0+Safari/534.13 - 200 0 0 15246
03:27:47 <intip POST /login.aspx - 443 - Mozilla/5.0+(Windows;+U;+Windows+NT+6.1;+en-US)+AppleWebKit/534.13+(KHTML,+like+Gecko)+Chrome/9.0.597.0+Safari/534.13 - 200 0 0 15242
03:28:02 <intip POST /login.aspx - 443 - Mozilla/5.0+(Windows;+U;+Windows+NT+6.1;+en-US)+AppleWebKit/534.13+(KHTML,+like+Gecko)+Chrome/9.0.597.0+Safari/534.13 - 200 0 0 15246

In conclusion…

Moral of the story: reviewing the response times to HTTP requests can aid in the successful exploitation of time-based SQL injections.

Tips & tricks

It’s hilarious what you can find here. I used to run a blog that would be DDoS’d occasionally, typically by people using web stressers or “booters”. It would be hilarious to see somebody would hit the “attack” button on ragebooter.net, then browse straight to my website from there, revealing https://ragebooter.net in the referrer header.

If you’re seeing a constant stream of very odd response times, this likely isn’t natural. If your webapp is normally averaging 10,000 MS response times, you have other problems to worry about.

User-agents will contain information related to the client that built the HTTP request. This is usually stuff like operating system, browser name, or anything related to the underlying technology used. For example, building HTTP requests with cURL will contain “cURL” in the user-agent of the HTTP requests it sends, unless you specify a different agent to use, or none at all. The same applies for programming libraries, such as Python’s Requests.

If you’re seeing a single IP address using a bunch of different user-agents in a short timeframe (like an hour..), this is often an attacker trying to evade detection, but usually overcompensating. I can’t imagine too many scenarios where a legitimate user would change user-agents 15 times in an hour, unless they’re using 10 different versions of Firefox and have 5 different computers and phones.