Capture the Flag Challenges require a lot of knowledge. In this series I am going to introduce you to some beginner SQL queries and some of the Database knowledge you will need to learn in order to progress with doing a CTFs which include SQL databases. This includes the install a sql database.
Table of Contents
- Back Ground Information
- Installing MariaDB in Docker
- Installing a Database into MySQL
Before we can get started with the technical side of beginner SQL we need sine back ground knowledge on the subject.
What is CTF?
CTF stands for Capture The Flag”. It’s a CyberSecurity Competition that challenges contestants to solve a variety of tasks ranging from using Open Source Intelligence for tracking down hidden information across the internet, to programming exercises, cracking encryption and to finding your way into a server to capture or find a Hidden Flag.
There is nearly always a Hidden Flag, in the code, on the internet or buried deep inside a unsecured Database or Web Application server. For IT security experts and IT experts in general it is a deeply rewarding and educational experience.
However the knowledge required to become proficient in all the topics and be successful in the challenges can be very daunting and the Knowledge difficult to acquire with out years of experience. So my blog should help with some of the knowledge gathering.
What is SQL
SQL stands for Structured Query Language and is a programming language used by nearly all relational databases to query, manipulate, and define data, and to provide access control. It was first developed at IBM in the 1970s with Oracle as a major contributor, which led to implementation of the SQL ANSI standard. SQL has spurred many extensions from companies such as IBM, Oracle, and Microsoft.
Relational Database ?
RDBMS (Relational Database Management System) is the basis for SQL. Items in a relational database are organized as a set of tables with columns and rows. Relational database technology provides the most efficient and flexible way to access structured information.
MariaDB and MySQL are two of the most deployed opensource relational databases in the world and maintain compatibility via the MySQL protocol. MySQL clients can connect to MariaDB and vice versa. However, they have evolved in their own ways, becoming separate databases with unique features and different product visions. Organizations of all sizes continue to replace MySQL with MariaDB in order to get out from under Oracle.
For this tutorial we will be using MariaDB.
Installing MySQL in Docker,
There are a few pre-requirements to get started with this tutorial.
- Docker must be installed in a Linux System,
- To manage the docker Containers docker-compose should also be installed,
- A directory or file system to keep the docker compose config and the databases files in. In my case it will be in /home/root/docker/mysql,
For the environment I am going to use ArchLinux. However, you don’t have to use arch, I have a previous post on setting up BlackArch linux. But any modern Linux version will do as long as you have docker installed and configured. I also have a post describing how to install Docker and a MariaDB instance.
The IP address of my system running Docker is “192.168.1.163”
As long as there is Docker and docker-compose installed its very easy to install and start MariaDB. First pull the Docker image down to the local server and then start the database with docker-compose.
docker pull mariadb:latest
Make a working directory and change into it.
mkdir -p /home/root/docker/mariadb cd /home/root/docker/mariadb
Create the docker compose file, and using your favorite text editor (which should be vim) put the below information into a file called docker-compose.yml.
# Use root/example as user/password credentials version: '3.1' services: mariadb: image: mariadb:latest container_name: maria_db ports: - "3306:3306/tcp" volumes: - /home/root/docker/mariadb/mariadb_data/:/var/lib/mysql environment: MYSQL_ROOT_PASSWORD: mypassword restart: unless-stopped
Now you can start up the database in demon mode and it keep running, even after a system reboot it will start up again.
docker-compose up -d
After this command completes successfully check the status of the MariaDB container with the following command.
If you want to delete stop the docker container run the command
And to delete the database run the command,
The Database files will still be in the directory created earlier. If recreating the database using the same docker-compose.yml file then the database will be recovered. In short this is one of the great features about docker and makes upgrades a breeze.
When running the docker-compose commands remember to be “cd” into the same directory as the docker-compose.yml file.
Now that the database is installed its time to the connectivity to it. In order to connect to the database you must have a database client installed. So this case its the mysql client. For example, in ArchLinux you can use the below command to install it.
pacman -S mysql
And to connect to the database the following command is used,
mysql -u root -h 192.168.1.163 -pmypassword
Lets break down this command
- mysql, The command to connect to the database,
- -u root, The user that we connect to the database with,
- -h 192.168.1.163, The IP address of the database host,
- -pmypassword, The password used to connect to the database.
In order to move on to the next part you must exit the mysql client with the following command
If this is not working for you be sure to go back over all the steps and that you have not missed anything.
Installing a Database into MySQL,
Now that we have a MariaDB Database Server installed we need some data to create a Database. In order to populate a database we need a lot of data in a sql format. We can just take a CSV file and import it but for this we will take a preformated database and import it into our MardiaDB server.
Building a test Database
We can get a nice large test database from the below link, https://github.com/datacharmer/test_db
This database requires about 200MB of space available but includes many tables and lots of data to learn with. So first we have to clone the data locally.
cd /home/root/docker/mariadb git clone https://github.com/datacharmer/test_db cd test_db
And to import the data into a new database.
mysql -u root -P 3307 -h 192.168.1.163 -pmypassword <employees_partitioned.sql
This is a lot of data and should take approx 1 minute to load into your Database server.
Querying the new Database with SQL
I won’t go too deep into this as I will do a full post into how to browse and get data that is useful to Capture the Flag enumeration.
Browsing the Database
Now the new test data is imported we can relaunch the mysql client and view the available databases.
mysql -u root -P 3307 -h 192.168.1.163 -pmypassword SHOW databases;
Getting the size of your new database
Don’t worry too much about the commands here as we can get to that over time but the result is important after all it is a Beginner SQL write up. You just have to copy and paste the command into the myql shell.
SELECT table_schema 'Database Name', SUM(data_length + index_length) 'Size in Bytes', ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) 'Size in MiB' FROM information_schema.tables GROUP BY table_schema;
So now you have a database to play with and can get started with learning the basic beginner SQL by doing a install of a SQL database that you will need to progress with Capture the Flag Challenges. Join me in the next post when jump into SQL and its syntax.
I really enjoy making this content and if you would like to support the cost of keeping this site up and running, please make a purchase through one of my affiliate links.