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.

MySQL/MariaDB

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,

Set-Up

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”

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'

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 

Compose Commands

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

docker-compose ps

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

exit

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;

Wrap-Up

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.

Support

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.