Day 11 – Where Are the Reindeers?
The focus of Day 11 is interacting with SQL databases using a Relational Database Management System (RDBMS).
We learned about non-relational (NoSQL) databases in Day 7.
The advantage of relational databases comes into play when there is an advantage in relating attributes from one table to those in another table. The classic example is a store where you may have one table for the items, another for customers, and a third to track invoices. The invoices table can pull data from the ‘items’ and ‘customers’ tables so that the data it contains can be sourced instead of being created from scratch or entered separately.
This is where relational databases excel; when data consists of groups of interrelated items, you can use an SQL database to perform features like pulling data from multiple tables to produce helpful data collections such as an invoice or analytics.
Question 1
There is an open port related to MS SQL Server accessible over the network. What is the port number?
We’ll start by performing a TCP port scan using nmap. In this case we expect our target to be a Windows machine. Windows machines sometimes won’t respond to ping scans, which could cause nmap to quit the scan. To prevent this we can add the ‘-Pn’ flag to the command to tell nmap not to perform a ping scan.
The resulting command is:
nmap -Pn 10.10.10.10
Make sure you substitute your target machine’s IP address for the ‘10.10.10.10’ in the example.
I also like to use the -T4 option to speed up the scan, so my final command is:
nmap -Pn -T4 10.10.10.10
This resulted in a fast scan that gave me the following result in a few seconds:
The scan produces a table with four open TCP ports, and tells us the service in the third, ‘service’ column.
We’re looking for the port with the ms-sql service running on it.
Answer:
(Highlight below to see answer):
1433
Question 2
Let’s try to run, sqsh -S 10.10.10.10 -U sa -P t7uLKzddQzVjVFJp
If the connection is successful, you will get a prompt. What is the prompt that you have received?
Basically we are just trying to use the squish (sqsh) command to connect to the database on the port that we found in the last question.
We already knew: (1) The IP address, and (2) The port number.
The port that we found (1433) is the default for SQL server, so we don’t need to specify it in our command.
We do however, need a username and password, which are given by THM:
username: sa
password: t7uLKzddQzVjVFJp
The syntax for sqsh is as follows:
sqsh -S <server IP> -U <username> -P <password>
So using our example IP the command would be:
sqsh -S 10.10.10.10 -U sa -P t7uLKzddQzVjVFJp
Answer:
(Highlight below to see answer):
1>
Question 3
What is the first name of the reindeer of id 9?
We use SQL queries to display the contents of databases. Performing a query requires two steps: entering the query, and then entering ‘go’, which sends the query to the database.
SQL queries have a specific format; the most basic version is:
SELECT <columns> FROM <database.table>
In this example we’ll be pulling everything from the table ‘names’ in database ‘reindeer.dbo’:
SELECT * FROM reindeer.dbo.names
The wildcard asterisk (*) tells the database to dump all columns from the ‘names’ table.
We know that we are looking for an ID of 9, so we can answer the question at this point.
If we want to get some extra practice, we can also try querying the database to produce only the reindeer with an ID of 9. To do this, we would use the syntax:
SELECT <columns> FROM <database.table> WHERE <condition>
In this case:
SELECT * FROM reindeer.dbo.names WHERE id = 9;
Answer:
(Highlight below to see answer):
Rudolph
Question 4
Check the table schedule. What is the destination of the trip scheduled on December 7?
Modify the query above for the table ‘schedule’:
SELECT * FROM reindeer.dbo.schedule
Then we can look up the destination on the table.
If we want to pull only the answer to the question, we can drill down further:
SELECT * FROM reindeer.dbo.schedule WHERE data = ‘Dec 7 2021’
By pulling the whole table, we were able to get the format of the date parameter.
Answer:
(Highlight below to see answer):
Prague
Question 5
Check the table presents. What is the quantity available for the present “Power Bank”?
Let’s perform the same operation, this time for the table ‘presents’:
SELECT * FROM reindeer.dbo.presents
If we wanted to pull only the present ‘Power Bank’, we could use:
SELECT * FROM reindeer.dbo.presents WHERE name = ‘Power Bank’
Answer:
(Highlight below to see answer):
25000
Question 6
There is a flag hidden in the grinch user’s home directory. What are its contents?
We have to use xp_cmdshell, which will allow us to execute Windows commands. As a review of some basic Windows commands:
whoami – Prints the user ID
dir – Prints the contents of the working directory
type – Prints the contents of a file
Going back to the SQL prompt, let’s use xp_cmdshell to execute the whoami command using the following syntax:
xp_cmdshell ‘whoami’
Now let’s try to answer the question by accessing the user Grinch’s home directory. We’ll use the ‘dir’ command to print out its’ contents so that we can start looking for the flag.
xp_cmdshell ‘dir c:\Users\grinch’
We can see a number of other directories including Desktop and Documents. After poking around, we find a flag.txt in Documents:
Then we can print the contents of the file using
xp_cmdshell ‘type c:\Users\grinch\Documents\flag.txt’
Answer:
(Highlight below to see answer):
THM{YjtKeUy2qT3v5dDH}