Intro Link to heading

This is a write-up for Bumblebee, a HTB Sherlock which is a gamified defensive CTF-like challenge. To start, they provide you with a scenario for the challenge along with the files needed to conduct the investigation. Additionally, they have a list of questions that you will need to answer.

Write-up Link to heading

To start we take a copy of the secnario to place into our notes.

An external contractor has accessed the internal forum here at Forela via the Guest WiFi and they appear to have stolen credentials for the administrative user! We have attached some logs from the forum and a full database dump in sqlite3 format to help you in your investigation.

Questions Link to heading

What was the username of the external contractor?

We start by taking a look at the SQLite database, to do this we can use DB Browser for SQLite. Once we open the database we see that there is a table named phpbb_users that contains a lists of users.

Searching the data in the table, we see towards the end of the table a username of apoole1 with the email [email protected].

What IP address did the contractor use to create their account?

In the same table as the previous question, we see the IP that the contractor used to create their account:

What is the post_id of the malicious post that the contractor made?

Searching through the database and the names of other tables, we find phpbb_posts which are the posts that users made on the forum.

We can use SQL to link the poster_id and the user_id together to identify which posts were made by which user.

FROM phpbb_posts 
INNER JOIN phpbb_users ON phpbb_posts.poster_id = phpbb_users.user_id;

After running the SQL query, the results show 3 posts, one of which, done by our contractor, contains a bunch of code in the post text. The post_id of this post is 9.

What is the full URI that the credential stealer sends its data to?

Extracting the text from the post_text field of the database table and reviewing the code, which appears to be HTML with some JavaScript, we find a POST to a webserver at sending the login information collected.

When did the contractor log into the forum as the administrator? (UTC)

Searching throught the tables in the database we find the phpbb_log table. The table contains a list of logs various operations conducted by users. There is an entry for LOG_ADMIN_AUTH_SUCCESS which is particularly interesting.

Using SQL, we identify the username conducting the log operations and convert the timestamp to a human-readable format.

	datetime(log_time, 'unixepoch'),
	INNER JOIN phpbb_users on phpbb_log.user_id = phpbb_users.user_id;

After running the SQL query, we find the answer to be 2023-04-26 10:54:31.

In the forum there are plaintext credentials for the LDAP connection, what is the password?

To find the LDAP credentials, we check the phpbb_config table. This table is a flat key-value table, so we just have to look for the config_name related to LDAP. We find several config names referring to LDAP but only one for the password.

What is the user agent of the Administrator user?

Knowing that the admin user communicates with the server from the IP Address, we look at the access.log for this IP address.

From that, we can see that the user agent being used is:

Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/ Safari/537.36
What time did the contractor add themselves to the Administrator group? (UTC)

Reviewing previous answers, we also see when they added themselves to the Administrators group.

What time did the contractor download the database backup? (UTC)

Knowing when the contractor started the database backup from the phpbb_log table, we look at the access.log file for traffic coming from the contractor’s IP around the time when the database backup was started. To do this, we run the PowerShell command:

 Get-Content .\access.log | Select-String -Pattern "" -CaseSensitive:$false | Select-String -Pattern "26/Apr/2023:1"

What was the size in bytes of the database backup as stated by access.log?

In the same output as the previous question we also get the size of the request which would be the size of the file.

Conclusion Link to heading

With the completion of the final question, we conclude this HTB Sherlock challenge. Throughout this journey, we have learned how to interpret access.log files and navigate through SQLite database files effectively.