SQL Fundamentals [CyberSecurity 101] Learning Path TryHackMe Writeup || Detailed Walkthrough || Beginner Friendly

SQL Fundamentals — CyberSecurity 101 Learning Path From TryHackMe

This Room is a Part of Web Hacking Section from CyberSecurity 101 Learning Path on TryHackMe .

Task 1 : Introduction

Task 1 is a basic module about the introduction of this room ,
it’s good to give it a read before proceeding to the next Task

Task 2 : Databases 101

Task 2 Question 1 : What type of database should you consider using if the data you’re going to be storing will vary greatly in its format?

Non-relational databases are designed to handle flexible, unstructured, or semi-structured data that may come in different formats, making them suitable for data with high variability (e.g., documents, key-value pairs, or collections). This structure allows data of varying types and quantities to be stored in one place without a rigid schema.

Answer : Non-Relational Database

Task 2 Question 2 : What type of database should you consider using if the data you’re going to be storing will reliably be in the same structured format?

Relational databases store data in a structured, table-based format with fixed columns and rows, making them ideal when data is consistently formatted. This structure is reliable for applications where data integrity and predefined relationships between data are essential, like processing e-commerce transactions.

Answer : Relational Database

Task 2 Question 3 : In our example, once a record of a book is inserted into our “Books” table, it would be represented as a ___ in that table?

In relational databases, each record (or entry) is stored as a row within a table. Each row contains data for a single item or entity, in this case, a book, where each column in the row represents an attribute or field, such as “Name” or “Published_date.”

Answer : Row

Task 2 Question 4 : Which type of key provides a link from one table to another?

A foreign key is used in relational databases to create a relationship between two tables. It links a column in one table to the primary key of another table, establishing a reference point between related data. For example, an “author_id” in the “Books” table can link to an “id” in the “Authors” table.

Answer : Foreign Key

Task 2 Question 5 : which type of key ensures a record is unique within a table?

A primary key uniquely identifies each record within a table, ensuring that no two records in the table are the same. For instance, each book might have a unique “id” serving as the primary key in the “Books” table, making it easy to distinguish each entry.

Answer : Primary Key

Task 2 is Done !

Task 3 : SQL

Task 3 Question 1 : What serves as an interface between a database and an end user?

A Database Management System (DBMS) acts as the intermediary between the database and the user, allowing users to interact with the data stored in the database. It provides tools to retrieve, update, and manage data, making it possible for users to handle complex data tasks without directly manipulating the raw data files.
Examples include MySQL, MongoDB, Oracle Database, and MariaDB.

Answer : DBMS

Task 3 Question 2 : What query language can be used to interact with a relational database?

SQL (Structured Query Language) is specifically designed for interacting with relational databases, enabling users to define, query, and manipulate data in these structured databases. SQL uses commands that are relatively easy to learn, like SELECT, INSERT, and UPDATE, allowing users to perform tasks such as retrieving data, inserting new records, and updating existing information quickly and accurately.

Answer : SQL

Task 3 is Completed !

Task 4 : Database and Table Statements

Make sure we have the right setup and VM is started with SQL Fundamentals v4 selected if you are using the AttackBox

Next we have to login to the mysql database using the command →

mysql -u root -p

The password prompted would be → tryhackme

If you get stuck , follow the Video snipped below →

Once you see mysql> option after login to mysql database ,
we are set to take on the challenges for the Task 4

Task 4 Question 1 : Using the statement you’ve learned to list all databases, it should reveal a database with a flag for a name; what is it?

To list all available databases, you would use the SHOW DATABASES;
SQL command. This command returns a list of all existing databases in the MySQL environment, including any hidden or system databases.

Let’s check the databases by using the command → SHOW DATABSES;

We found a flag for the Task 4 Question 1 !!

THM{575a947132312f97b30ee5aeebba629b723d30f9}

Task 4 Question 2 : In the list of available databases, you should also see the task_4_db database. Set this as your active database and list all tables in this database; what is the flag present here?

To set task_4_db as the active database, you would use the USE task_4_db; command. This tells MySQL that any subsequent commands will apply to task_4_db. Then, you can use SHOW TABLES; to view all tables within this database

From the earlier Task we already know the database →

+-----------------------------------------------+
| Database                                      |
+-----------------------------------------------+
| THM{575a947132312f97b30ee5aeebba629b723d30f9} |
| information_schema                            |
| mysql                                         |
| performance_schema                            |
| sys                                           |
| task_4_db                                     |
| thm_books                                     |
| thm_books2                                    |
| tools_db                                      |
+-----------------------------------------------+

The Question asks to list the task_4_db database

Command we are going to use to select the db → use task_4_db;

Command we are going to use to show Tables → SHOW TABLES;

We got the Flag for Task 4 Question 2 →

THM{692aa7eaec2a2a827f4d1a8bed1f90e5e49d2410}

Task 4 Now complete !

Task 5 : CRUD Operations

Task 5 Question 1 : Using the tools_db database, what is the name of the tool in the hacking_tools table that can be used to perform man-in-the-middle attacks on wireless networks?

We need to query the tools_db database and focus on the hacking_tools table and Find the tool used for man-in-the-middle attacks on wireless networks.

In SQL, the DESCRIBE (or sometimes DESC) command is used to show the structure of a table, including details about each column

The output of the DESC hacking_tools; command provides the schema details of the hacking_tools table, which includes the columns and their properties.

DESC hacking_tools;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| id          | int         | NO   | PRI | NULL    | auto_increment |
| name        | varchar(50) | NO   |     | NULL    |                |
| category    | varchar(50) | NO   |     | NULL    |                |
| description | text        | YES  |     | NULL    |                |
| amount      | int         | NO   |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+

we need to query the hacking_tools table and retrieve only the name and description columns for each record in the table.

For this we are going to use the command →

SELECT name , description FROM hacking_tools;

This result shows the name and category of various hacking tools from the hacking_tools table

+------------------+-------------------------------------------------------------------------+
| name             | description                                                             |
+------------------+-------------------------------------------------------------------------+
| Flipper Zero     | A portable multi-tool for pentesters and geeks in a toy-like form       |
| O.MG cables      | Malicious USB cables that can be used for remote attacks and testing    |
| Wi-Fi Pineapple  | A device used to perform man-in-the-middle attacks on wireless networks |
| USB Rubber Ducky | A USB keystroke injection tool disguised as a flash drive               |
| iCopy-XS         | A tool used for reading and cloning RFID cards for security testing     |
| Lan Turtle       | A covert tool for remote access and network intelligence gathering      |
| Bash Bunny       | A multi-function USB attack device for penetration testers              |
| Proxmark 3 RDV4  | A powerful RFID tool for reading, writing, and analyzing RFID tags      |
+------------------+-------------------------------------------------------------------------+

According to the description column in the hacking_tools table,
the Wi-Fi Pineapple is specifically mentioned as a device →
"used to perform man-in-the-middle attacks on wireless networks."

Answer : Wi-Fi Pineapple

Task 5 Question 2 : Using the tools_db database, what is the shared category for both USB Rubber Ducky and Bash Bunny?

Both tools USB Rubber Ducky and Bash Bunny are grouped under the USB attacks category because they leverage USB interfaces to deliver hacking payloads or execute commands, allowing them to exploit the target device’s USB connectivity for penetration testing or security assessments.

Answer : USB Attacks

Task 5 Done !

Task 6 : Clauses

Task 6 Question 1 : Using the tools_db database, what is the total number of distinct categories in the hacking_tools table?

SELECT DISTINCT category FROM hacking_tools;

The above command will return a single value showing the
total number of unique categories in the category column of the hacking_tools table.

+----------------------+
| category             |
+----------------------+
| Multi-tool           |
| Cable-based attacks  |
| Wi-Fi hacking        |
| USB attacks          |
| RFID cloning         |
| Network intelligence |
+----------------------+
6 rows in set 

Answer : 6

Task 6 Question 2 : Using the tools_db database, what is the first tool (by name) in ascending order from the hacking_tools table?

This command, it will return a list of all the tool names from the hacking_tools table, sorted in ascending alphabetical order.

SELECT name FROM hacking_tools ORDER BY name ASC;

Output of the above run command →

+------------------+
| name             |
+------------------+
| Bash Bunny       |
| Flipper Zero     |
| iCopy-XS         |
| Lan Turtle       |
| O.MG cables      |
| Proxmark 3 RDV4  |
| USB Rubber Ducky |
| Wi-Fi Pineapple  |
+------------------+

Answer is : Bash Bunny

Task 6 Question 3 : Using the tools_db database, what is the first tool (by name) in descending order from the hacking_tools table?

This question is almost same as above , so ASC would be replaced by DSC which refers to Ascending and Descending respectively

SELECT name FROM hacking_tools ORDER BY name DESC;

The output is simply flipped from Top to Bottom

Task 6 Done !

Task 7 : Operators

Task 7 Question 1 : Using the tools_db database, which tool falls under the Multi-tool category and is useful for pentesters and geeks?

running this command →

SELECT * FROM hacking_tools;

it will return all rows and all columns from hacking_tools table .

To get to the exact answer we need to try another command
( Try it yourself )

SELECT name FROM hacking_tools WHERE category = 'Muli-Tool' AND description LIKE "%pentesters%" AND description LIKE "%geeks%";

Answer : Flipper Zero

Task 7 Question 2 : Using the tools_db database, what is the category of tools with an amount greater than or equal to 300?

To answer the question about determining the category of tools with an amount greater than or equal to 300 in the tools_db database, we can analyze the SQL query:

SELECT category FROM hacking_tools WHERE amount >= 300;

• SELECT category:

This part of the command indicates that we are interested in retrieving the category column from the hacking_tools table. The category column contains the classifications of different tools.

• FROM hacking_tools:

This specifies the table from which to retrieve the data. Here, the table is hacking_tools, which is assumed to contain various tools along with their corresponding amounts and categories.

• WHERE amount >= 300:

The WHERE clause filters the results based on the condition specified. It checks the amount column and selects only those records where the amount is greater than or equal to 300. The operator >= ensures that tools with an amount of 300 or more are included in the output.

Answer : RFID cloning

Task 7 Question 3 : Using the tools_db database, which tool falls under the Network intelligence category with an amount less than 100?

To find the tool that falls under the Network Intelligence category with an amount less than 100 in the tools_db database, you would use the following SQL query:

SELECT * FROM hacking_tools WHERE category = 'Network Intelligence' AND amount < 100;

This query effectively retrieves tools that belong to the Network Intelligence category and have an amount less than 100. It uses comparison operators to filter the data based on category and amount, allowing for precise data retrieval within the tools_db database.

Answer : Lan Turtle

Task 7 Done !

Task 8 : Functions

Task 8 Question 1 : Using the tools_db database, what is the tool with the longest name based on character length?

To find the tool with the longest name, you would typically use the LENGTH() function to calculate the length of each tool's name and then select the maximum value. The query would look something like this:

SELECT name LENGTH(name) AS name_length FROM hacking_tools ORDER BY name_length DESC;

• LENGTH(name) calculates the length of each tool's name.
• ORDER BY LENGTH(name) DESC sorts the tools by their name length in descending order.
• LIMIT 1 ensures only the tool with the longest name is returned.

+------------------+-------------+
| name             | name_length |
+------------------+-------------+
| USB Rubber Ducky |          16 |
| Wi-Fi Pineapple  |          15 |
| Proxmark 3 RDV4  |          15 |
| Flipper Zero     |          12 |
| O.MG cables      |          11 |
| Lan Turtle       |          10 |
| Bash Bunny       |          10 |
| iCopy-XS         |           8 |
+------------------+-------------+

USB Rubber Ducky has the longest name among the tools listed in the tools_db.

Answer : USB Rubber Ducky

Task 8 Question 2 : Using the tools_db database, what is the total sum of all tools?

To calculate the total sum of all tools, you would use the SUM() function on the relevant column that holds the values of the tools (typically the amount or price column). The query might look like this:

SELECT SUM(amount) AS total_price FROM hacking_tools;

• SUM(amount) adds up all the values in the amount column.

+-------------+
| total_price |
+-------------+
|        1444 |
+-------------+
1 row in set

Answer : 1444

Task 8 Question 3 : Using the tools_db database, what are the tool names where the amount does not end in 0, and group the tool names concatenated by " & ".

To find tool names where the amount does not end in 0, you would typically use the MOD() function or string functions to check the last digit of the amount. The query could look like this:

SELECT GROUP_CONCAT(name SEPARATOR " & ") AS name_nonzero FROM hacking_tools WHERE SUBSTRING(amount, -1, 1) != 0;

SELECT Statement

• SELECT GROUP_CONCAT(name SEPARATOR " & ") AS name_nonzero: This part of the query specifies that you want to select a single field, name_nonzero, which will contain the concatenated names of the tools.
• GROUP_CONCAT(name SEPARATOR " & "):
• GROUP_CONCAT() is an aggregate function that concatenates values from multiple rows into a single string.
• The SEPARATOR " & " specifies that each name in the concatenated string should be separated by " & ".
• For example, if the names were “Tool A” and “Tool B”, the result would be “Tool A & Tool B”.

FROM Clause

• FROM hacking_tools: This specifies the table from which to retrieve the data, in this case, hacking_tools.

WHERE Clause

• WHERE SUBSTRING(amount, -1, 1) != 0: This clause filters the rows based on a condition related to the amount column.
• SUBSTRING(amount, -1, 1):
• The SUBSTRING() function extracts a portion of a string.
• Here, it is used to get the last character of the amount string:
• The first argument is the amount column.
• The second argument -1 indicates that it should start from the last character.
• The third argument 1 indicates that it should extract only one character.
• != 0: This checks if the last character of the amount is not equal to 0. This means the query will only consider rows where the amount does not end with a zero.

+-------------------------+
| name_nonzero            |
+-------------------------+
| Flipper Zero & iCopy-XS |
+-------------------------+
1 row in set

Answer : Flipper Zero & iCopy-XS

Task 8 is now complete !

Task 9 is Conclusion

Congratulations ! We have solved the room together !

Hope you have enjoyed solving this room as much i did

if you want to get the latest Try Hack Me writeups delivered , go ahead and follow me on Medium and also hit the notify via email

Let’s Connect on Linkedin → https://linkedin.com/in/sunnysinghverma

You can also add me Respect on — Hack The Box if you want i would really appreciate it :)

https://app.hackthebox.com/users/1585635

My TryHackMe Profile Page →

https://tryhackme.com/p/SuNnY

if you did you can add a clap to this article to let me know and if you loved this article you can click clap icon upto 50 times to let me know and that will make my day 🤗
You can also follow me on medium to get more articles about CTFs and Cybersecurity in the near Future but don’t forget to hit that email notification icon right next to the follow me button

Thank you !
SuNnY