SQL Fundamentals | {flino|dev}

Cyber Security 101 > Web Hacking > SQL Fundamentals

tcpdump the basics

Task 1

Introduction

Teach me the basics of SQL!

No answer needed

Task 2

Databases 101

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 database

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 database

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

row

Which type of key provides a link from one table to another?

foreign key

which type of key ensures a record is unique within a table?

primary key

Task 3

SQL

What serves as an interface between a database and an end user?

DBMS

What query language can be used to interact with a relational database?

SQL

Task 4

Database and Table Statements

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

Execute this query:

SHOW DATABASES;

THM{575a947132312f97b30ee5aeebba629b723d30f9}

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?

Execute this query:

USE task_4_db;
SHOW TABLES;

THM{692aa7eaec2a2a827f4d1a8bed1f90e5e49d2410}

Task 5

CRUD Operations

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?

Execute this query:

USE tools_db;
SELECT * FROM hacking_tools WHERE description LIKE '%man-in-the-middle%’;

Wi-Fi Pineapple

Using the tools_db database, what is the shared category for both USB Rubber Ducky and Bash Bunny?

Execute this query:

SELECT * FROM hacking_tools WHERE name = 'USB Rubber Ducky' OR name = 'Bash Bunny';

USB attacks

Task 6

Clauses

Using the tools_db database, what is the total number of distinct categories in the hacking_tools table?

Execute this query:

SELECT DISTINCT category from hacking_tools;

6

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

Execute this query:

SELECT * FROM hacking_tools ORDER BY name ASC;

Bash Bunny

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

Execute this query:

SELECT * FROM hacking_tools ORDER BY name DESC;

Wi-Fi Pineapple

Task 7

Operators

Using the tools_db database, which tool falls under the Multi-tool category and is useful for pentesters and geeks?

Execute this query:

SELECT * FROM hacking_tools WHERE category LIKE '%Multi-tool%';

Flipper Zero

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

Execute this query:

SELECT * FROM hacking_tools WHERE amount >= 300;

RFID cloning

Using the tools_db database, which tool falls under the Network intelligence category with an amount less than 100?

Execute this query:

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

Lan Turtle

Task 8

Functions

Using the tools_db database, what is the tool with the longest name based on character length?

Execute this query:

SELECT name, MAX(LENGTH(name)) AS ln from hacking_tools GROUP BY name ORDER BY ln DESC;

USB Rubber Ducky

Using the tools_db database, what is the total sum of all tools?

Execute this query:

SELECT SUM(amount) FROM hacking_tools;

1444

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 ” & ”.

Execute this query:

SELECT GROUP_CONCAT(name SEPARATOR " & ") FROM hacking_tools WHERE SUBSTRING(amount, LENGTH(amount), LENGTH(amount)) != "0";

Flipper Zero & iCopy-XS

Task 9

Conclusion

I’m ready to move forward and learn more about web application security.

No answer needed