Skip to content

Beginner SQL for CTF – Install a MaridaDB database.

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.

Back Ground

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 ?

database schema
database schema

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 “”

Docker Set-Up

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

Docker Compose

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'


    image: mariadb:latest
    container_name: maria_db
      - "3306:3306/tcp"
     - /home/root/docker/mariadb/mariadb_data/:/var/lib/mysql
      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 

Compose Commands

After this command completes successfully check the status of the MariaDB container with the following command.

docker-compose ps
Output of Docker-Compose up
Output of a new MariaDB container

If you want to delete stop the docker container run the command

docker-compose stop

And to delete the database run the command,

docker-compose down

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.

Testing connectivity

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 -pmypassword
connect to a MySQL Database.

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, 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,

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
cd test_db

And to import the data into a new database.

mysql -u root -P 3307 -h -pmypassword <employees_partitioned.sql

This is a lot of data and should take approx 1 minute to load into your Database server.

Successfully downloaded and imported the sql  data.
Successful Import of Data

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 -pmypassword 
SHOW databases;
test SQL database
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.

    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;
Size of the Database
Size of the Database


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.

Published inCTFGetting Started With CTF ChallengesSQL

Be First to Comment

Leave a Reply

Your email address will not be published. Required fields are marked *