DEV Community: Giannis Ftaras

Managing multiple databases with Docker and phpMyAdmin

Giannis Ftaras — Mon, 06 Nov 2023 10:08:50 +0000

When you are working locally with multiple projects you sometimes need to access different databases in order to view, change or manipulate data.

There are multiple ways to achieve this...

The old fashioned way

Using the good-old MySQL CLI in order to jump in and do what you want. This has the benefit of making you more conformable in using the terminal which might be required for staging or production environments, but this can also slow down your process and can be quite daunting at times.

We have an app for that!

There are multiple applications out there that can help you manage databases such as MySQL Workbench, TablePlus or Beekeeper Studio. While all these applications are awesome and are doing a great job at what they are designed for, they also have their drawbacks. MySQL Workbench can be hard to work with sometimes, TablePlus is not free and while Beekeeper has a free Community version it cannot be used in an enterprise environment or the free features might seem quite limiting at times.

phpMyAdmin to the rescue!

A web-based GUI that allows you to easily view and manage the databases that you want. Don't get me wrong, phpMyAdmin has its drawbacks as well, such as - prone to crashing, many vulnerabilities are often found, etc. but for local development I believe it has the right ratio of pros vs cons in order to be my daily DB management tool.

Setting up phpMyAdmin with Docker

If you're using Docker, the installation process of phpMyAdmin is quite easy and straight-forward. You'll only need a couple of things:

Install Docker

No matter which operating system you use, you will have to install Docker (duh!)

Create the docker-compose file

Select a folder in your computer where you want to run this project from and create a docker-compose.yml file.
This file will contain all the necessary information that Docker needs in order to build the appropriate containers. A default database will also be created in order to be used as a workbench and to avoid slow loading times if your other databases are not currently online or spun up.

version: '3.8'

services:

  workbench-mysql-database:
    image: 'mysql:8.0.0'
    container_name: 'workbench-mysql-database'
    environment:
      MYSQL_ROOT_PASSWORD: 'root'
      MYSQL_DATABASE: 'workbench'
      MYSQL_USER: 'workbench'
      MYSQL_PASSWORD: 'workbench-pass'
      MYSQL_ALLOW_EMPTY_PASSWORD: 1
    ports:
      - '${FORWARD_DB_PORT:-3310}:3306'
    networks:
      internal-net:
    volumes:
      - mysql-data:/var/lib/mysql
    restart: unless-stopped

  workbench-phpmyadmin:
    image: 'phpmyadmin:latest'
    container_name: 'workbench-phpmyadmin'
    environment:
      UPLOAD_LIMIT: '600M'
    ports:
      - '7090:80'
    volumes:
      - ./config.user.inc.php:/etc/phpmyadmin/config.user.inc.php
    networks:
      internal-net:
    restart: unless-stopped

volumes:
  mysql-data:
networks:
  internal-net:

As you can see in the code above a file named config.user.inc.php will also be mounted to the phpMyAdmin container. This file holds all the configuration needed in order to connect to our databases.

Create the phpMyAdmin configuration file

In the same folder that you created docker-compose.yml you also need to create the config.user.inc.php file.
In the configuration below we are specifying the attributes needed to connect to our other databases.

$cfg['Servers'] = [
    1 => [
        'auth_type' => 'config',
        'host' => 'host.docker.internal',
        'port' => 3306,
        'user' => 'db1user',
        'password' => 'db1pass',
        'verbose' => 'BD1'
    ],
    2 => [
        'auth_type' => 'config',
        'host' => 'host.docker.internal',
        'port' => 3307,
        'user' => 'db2user',
        'password' => 'db2pass',
        'verbose' => 'DB2'
    ],
    3 => [
        'auth_type' => 'config',
        'host' => 'workbench-mysql-database',
        'port' => 3306,
        'user' => 'root',
        'password' => 'root',
        'verbose' => 'Workbench'
    ]
];

For the host attribute there are three options that you can use:

If the database is in the same container you can use the instance name (e.g. workbench-mysql-database)
If the database is in the same machine but on a different Docker container, you can use the Docker host domain (host.docker.internal)
You can also use an internal or external IP (e.g. 192.168.1.205 or 10.15.203.5)

In this file you can also add extra phpMyAdmin configuration attributes such the default server to be loaded on boot and many other. The configuration can also be changed on the fly without having to restart the containers.

This guide assumes that on each project that you are working on you have a different external port assigned to your MySQL instances otherwise when you try to spin up two containers at once you'll get an IP conflict.

Recursive wget and file transfer

Giannis Ftaras — Fri, 20 May 2022 10:41:41 +0000

We can simply transfer files between two PCs via the network without having to use USB sticks or other “archaic” methods.

We first cd to the directory we want and then run a simple python HTTP server.

Python2: python -m SimpleHTTPServer <port_number>
Python3: python3 -m http.server <port_number>

On the other machine we then use wget to recursively retrieve all the files from the remote directory.

wget -r -np -R "<condition>" <remote_url>

-r Recursively retrieve all files and directories from the remote machine.
-np No Parent flag. Do not ascend to the parent directory when retrieving recursively
-R Reject all files that match the conditions

Example: wget -r -np -R "index.html*" http://10.10.10.120:8080/

Why reject index.html?

When we use wget recursively the command downloads automatically an index.html file for each directory downloaded from the remote machine. With this flag we keep our downloads tidy and clean without any unnecessary files.

File upload bypass using MIME-type

Giannis Ftaras — Fri, 20 May 2022 10:37:43 +0000

There are many methods web developers incorporate in their applications in order to allow only certain file types to be uploaded. In the case of a slideshow of a photo booth web application, developers mostly check for a correct file extension (.jpg , .png , etc.) as well as its mime-type Content-type: image/jpeg in order to ensure that the file uploaded is indeed allowed and only an image.

Bypassing the file extension to upload a payload is straightforward and easy. We just change the file extension from payload.php to payload.php.gif

The mime-type check bypass is again relatively simple but most penetration testers tend to make it seem more complicated than it really is. A good example of that is when we try to incorporate a PHP script in a regular image, which in most cases is very difficult to achieve, and it doesn’t guarantee that the payload will indeed work. This happens because in some cases the server does not recognize the file to contain executable code and it simply tries to display it as an image.

The easy way is to trick the mime-type security check in order to think that the file we’ve uploaded is an image but in reality the web server is going to recognize it as a PHP script file.

To do so we only need to add one line at the start of our script: GIF89a;

GIF89a is a GIF file header. When the file gets reviewed by mime_content_type("myfile"); it gets fooled in order to think that it is an image instead of a PHP shell.

GIF89a;
<?php
  system('whoami'); # shellcode goes here
?>

Fast nmap scanning

Giannis Ftaras — Fri, 20 May 2022 10:35:21 +0000

Most of the time we need nmap to just do its job and present us with any available ports that may find in its default configuration, which is usually the 1000 most popular ports. Other times though, we need to scan fast and efficiently to list only open ports in the whole spectrum without any blows and whistles.

You can use any of the commands below depending on your needs.

nmap -sC -sV <TARGET_IP_ADDRESS> Type: Normal
Scan the top 1000 most popular ports with the default nmap scripts (-sC) and with version detection (-sV) active.

nmap -Pn -T4 -v1 -p- <TARGET_IP_ADDRESS> Type: Ultra Fast
Scan all ports (-p-) (0-65535) without ping (-Pn) which helps treat all hosts as online and thus reduce the overall execution time. We also specify the verbose mode (-v1) in order for nmap to list any open ports as soon as it finds them and -T4 to set a higher timing template which speeds ups the process even more. For further information regarding the -T flag you can review the nmap man page.

nmap -nvv -Pn -sS --open <TARGET_IP_ADDRESS> Type: Fast
Scan without DNS resolving (-n), without ping (-Pn) while using the TCP SYN scan type (-sS) and listing only open ports (–open).

ERP 2 Ecommerce Project

Giannis Ftaras — Fri, 20 May 2022 08:04:49 +0000

Background

One of the most challenging tasks when in a work environment is to unite two departments that handle similar tasks. For this project we are referring to the logistics department along with our online presence such as our E-Commerce store as well as the IT department.

As an example we can examine what needed to be done when product prices had to be changed in bulk. At first the logistics department had to calculate the available stock quantities and then in conjunction with an accounting supervisor calculate the new prices. These prices were then added to lists that were forwarded to the IT department for further processing and finally manually updated in the e-commerce store.

This task is time consuming for both departments and lengthy both on paper as well as in real life especially if the product count was high. A solution had to be created in order to increase productivity and reduce the time spent on single tasks.

A big plus for the implementation of this task is that the ERP application at the organization is able handle scripts that can be programmed directly into its general logic. Using this advantage we created a bridge that helped connect the logistics department with the online store directly. The system also had support for handling SOAP requests which helped us implement this task both ways, like introducing changes from the ERP application directly to the online store and vise versa.

The E-Commerce store was developed on the Woocommerce platform which is an open source plugin used in the WordPress CMS to transform a regular blog to a full fledged online store. Woocommerce has a built-in REST API which allows us to implement a variety of tasks like updating product variables, view and update customer information, and much more.

In order for the ERP application to communicate directly with the E-Commerce store an application had to be created in order to utilize the organizations needs. This was achieved by coding the required applications, as well as the ERP request handling, in the .NET programming language.

Some of the core aspects needed were:

Updating products (prices, SKUs, description, stock quantities, etc.)
Viewing and updating customer information
Order processing
Parcel tracking

Implementation

Before starting with all the application brainstorming we had to first configure the Woocommerce REST API properly, check its reliability and see if it matches the organizations needs. After initial setup and testing we then had to insure the implementation is secure and any third party access is prohibited. A wide range of techniques were used for security such as an HTTPS connection, rate and IP limiting, brute force protection, authentication, etc. Also a pseudo-user was created to handle read and write permissions as well as insure that the application scope was limited only to the variables needed for the implementation. We are then ready to test if the API works properly.

curl -X PUT https://example.com/wp-json/wc/v3/products/794 \
    -u consumer_key:consumer_secret \
    -H "Content-Type: application/json" \
    -d '{
  "regular_price": "24.54"
}'

After thorough testing and once we are certain that the API works properly for both reading and writing variables we can then start with the main application development.

Development

Every application on the planet how matter how free, open-source, or like some vendors like to call them, “unlock” there are always limitations in place that don’t match every organizations needs. The tricky part was that even though the ERP application was based on .NET, the code functionality that we could introduce was fairly limited. Because the scope of variables needed to implement was fairly big we opted to go for a man-in-the-middle application (referred to as “main application” from now on) as a result.

On the ERP application the processing level was fairly limited. Several listener modules were created that would check whether the end user was saving or requesting data from the server. All this information was then divided into variables which are fed to the main application.

Once the main application receives these variables it analyses the content in order to the determine the users’ needs, further process it and provide with the required result.

After this procedure the application then forwards the request to the Woocommerce REST API which process it and responds appropriately.

The code above is a very small snippet of how the finalized application works along with several other aspects like updating orders, error correction, exception handling and response handling. A “sidekick” to the main application had to also be created in order to log requests and responses to and from the REST API. This helped the non-tech savvy staff understand the servers’ response and act appropriately based on feedback.

Summary

The full implementation contains more that 4000 lines of code which consist of the modules mentioned above. Several information regarding the implementation were omitted for security and proprietary reasons.

This project helped the logistics department tackle every day tasks more easily without having to forward them to the IT department for further implementation and correction. As an organization grows these kind of everyday tasks can be time consuming and using this method we were able to increase productivity and reduce working double time, that had to be previously done in order to achieve something as simple as updating a product’s price.