BLOG | NGINX

NGINX Tutorial: Protect Kubernetes Apps from SQL Injection

NGINX-Part-of-F5-horiz-black-type-RGB
Daniele Polencic Thumbnail
Daniele Polencic
Published March 22, 2022

This tutorial is one of four that put into practice concepts from Microservices March 2022: Kubernetes Networking:

Want detailed guidance on using NGINX for even more Kubernetes networking use cases? Download our free eBook, Managing Kubernetes Traffic with NGINX: A Practical Guide.

You work in IT for a popular local store that sells a variety of goods, from pillows to bicycles. They’re about to launch their first online store, but they asked a security expert to pen test the site before it goes public. Unfortunately, the security expert found a problem! The online store is vulnerable to SQL injection. The security expert was able to exploit the site to obtain sensitive information from your database, including usernames and passwords.

Your team has come to you – the Kubernetes engineer – to save the day. Luckily, you know that SQL injection (as well as other vulnerabilities) can be mitigated using Kubernetes traffic management tools. You already deployed an Ingress controller to expose the app and, in a single configuration, you’re able to ensure the vulnerability can’t be exploited. Now, the online store can launch on time. Well done!

Lab and Tutorial Overview

This blog accompanies the lab for Unit 3 of Microservices March 2022 – Microservices Security Pattern in Kubernetes, demonstrating how to use NGINX and NGINX Ingress Controller to block SQL injection.

To run the tutorial, you need a machine with:

  • 2 CPUs or more
  • 2 GB of free memory
  • 20 GB of free disk space
  • Internet connection
  • Container or virtual machine manager, such as Docker, Hyperkit, Hyper-V, KVM, Parallels, Podman, VirtualBox, or VMware Fusion/Workstation
  • minikube installed
  • Helm installed
  • A configuration that allows you to launch a browser window. If that isn’t possible, you need to figure out how to get to the relevant services via a browser.

To get the most out of the lab and tutorial, we recommend that before beginning you:

This tutorial uses these technologies:

The instructions for each challenge include the complete text of the YAML files used to configure the apps. You can also copy the text from our GitHub repo. A link to GitHub is provided along with the text of each YAML file.

This tutorial includes four challenges:

  1. Deploy a Cluster and Vulnerable App
  2. Hack the App
  3. Use an NGINX Sidecar Container to Block Certain Requests
  4. Configure NGINX Ingress Controller to Filter Requests

Challenge 1: Deploy a Cluster and Vulnerable App

In this challenge, you deploy a minikube cluster and install Podinfo as a sample app that has security vulnerabilities.

Create a Minikube Cluster

Deploy a minikube cluster. After a few seconds, a message confirms the deployment was successful.

$ minikube start 
🏄  Done! kubectl is now configured to use "minikube" cluster and "default" namespace by default

Install the Vulnerable App

Here you deploy a simple e‑commerce app that consists of two microservices:

  • A MariaDB database
  • A PHP microservice that connects to the database and retrieves data

Perform these steps:

  1. Using the text editor of your choice, create a YAML file called 1-app.yaml with the following contents (or copy from GitHub).

    apiVersion: apps/v1 kind: Deployment 
    metadata: 
      name: app 
    spec: 
      selector: 
        matchLabels: 
          app: app 
      template: 
        metadata: 
          labels: 
            app: app 
        spec: 
          containers: 
            - name: app 
              image: f5devcentral/microservicesmarch:1.0.3 
              ports: 
                - containerPort: 80 
              env: 
                - name: MYSQL_USER 
                  value: dan 
                - name: MYSQL_PASSWORD 
                  value: dan 
                - name: MYSQL_DATABASE 
                  value: sqlitraining 
                - name: DATABASE_HOSTNAME 
                  value: db.default.svc.cluster.local 
    --- 
    apiVersion: v1 
    kind: Service 
    metadata: 
      name: app 
    spec: 
      ports: 
        - port: 80 
          targetPort: 80 
          nodePort: 30001 
      selector: 
        app: app 
      type: NodePort 
    --- 
    apiVersion: apps/v1 
    kind: Deployment 
    metadata: 
      name: db 
    spec: 
      selector: 
        matchLabels: 
          app: db 
      template: 
        metadata: 
          labels: 
            app: db 
        spec: 
          containers: 
            - name: db 
              image: mariadb:10.3.32-focal 
              ports: 
                - containerPort: 3306 
              env: 
                - name: MYSQL_ROOT_PASSWORD 
                  value: root 
                - name: MYSQL_USER 
                  value: dan 
                - name: MYSQL_PASSWORD 
                  value: dan 
                - name: MYSQL_DATABASE 
                  value: sqlitraining 
    
    --- 
    apiVersion: v1 
    kind: Service 
    metadata: 
      name: db 
    spec: 
      ports: 
        - port: 3306 
          targetPort: 3306 
      selector: 
        app: db 
    
  2. Deploy the app and API:

    $ kubectl apply -f 1-app.yaml deployment.apps/app created 
    service/app created 
    deployment.apps/db created 
    service/db created 
    
  3. Confirm that the Podinfo pods deployed, as indicated by the value Running in the STATUS column. It can take 30–40 seconds for them to fully deploy, so wait until the status of both pods is Running before continuing to the next step (reissuing the command as necessary).

    $ kubectl get podsNAME                  READY   STATUS    RESTARTS   AGE 
    app-d65d9b879-b65f2   1/1     Running   0          37s 
    db-7bbcdc75c-q2kt5    1/1     Running   0          37s 
    
  4. Open the app in your browser:

    $ minikube service app|-----------|------|-------------|--------------| 
    | NAMESPACE | NAME | TARGET PORT |     URL      | 
    |-----------|------|-------------|--------------| 
    | default   | app  |             | No node port | 
    |-----------|------|-------------|--------------| 
    😿  service default/app has no node port 
    🏃  Starting tunnel for service app. 
    |-----------|------|-------------|------------------------| 
    | NAMESPACE | NAME | TARGET PORT |          URL           | 
    |-----------|------|-------------|------------------------| 
    | default   | app  |             | http://127.0.0.1:55446 | 
    |-----------|------|-------------|------------------------| 
    🎉  Opening service default/app in default browser... 
    

Challenge 2: Hack the App

The sample application is rather basic. It includes a homepage with a list of items (for example, pillows) and a set of product pages with details like a description and the price. The data is stored in the MariaDB database. Each time a page is requested, an SQL query is issued against the database.

  • For the homepage, all items in the database are retrieved.
  • For a product page, the item is fetched by ID.

When you open the pillows product page, you may notice the URL ends in /product/1. The 1 is the product’s ID. To prevent direct insertion of malicious code into the SQL query, it’s a best practice to sanitize user input before forwarding requests to backend services. But what if the app isn’t properly configured, and the input is not escaped before it’s inserted into the SQL query against the database?

Exploit 1

To find out whether the app is properly escaping input, run a simple experiment by changing the ID to one that doesn’t exist in the database.

Manually change the last element in the URL from 1 to -1. The error message Invalid product id "-1" indicates that the product ID is not being escaped – instead, the string gets inserted directly into the query. That’s not good unless you’re a hacker!

Assume the database query is something like:

SELECT * FROM some_table WHERE id = "1"

To exploit the vulnerability caused by not escaping the input, replace 1 with -1" <malicious_query> -- // such that:

  • The quotation mark (") after -1 completes the first query.
  • You can add your own malicious query after the quotation mark.
  • The -- // sequence discards the rest of the query.

So, for example, if you change the final element in the URL to ‑1" or 1 -- //, the query compiles to:

SELECT * FROM some_table WHERE id = "-1" OR 1 -- //" 
                                     -------------- 
                                     ^  injected  ^ 

This selects all rows from the database, which is useful in a hack. To find out if this is the case, change the URL ending to ‑1". The resulting error message gives you more useful information about the database:

Fatal error: Uncaught mysqli_sql_exception: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '"-1""' at line 1 in /var/www/html/product.php:23 Stack trace: #0 /var/www/html/product.php(23): mysqli->query('SELECT * FROM p...') #1 {main} thrown in /var/www/html/product.php on line 23

Now, you can start manipulating the injected code in an attempt to order the database results by ID:

-1" OR 1 ORDER BY id DESC -- //

The result is the product page for the last item in the database.

Exploit 2

Forcing the database to order results is interesting, but not especially useful if hacking is your goal. Trying to extract usernames and password from the database is much more worth your while.

It’s safe to assume there’s a table of users in the database with usernames and passwords. But how do you extend your access from the products table to the users table?

The answer is by injecting code like this:

-1" UNION SELECT * FROM users -- //

where

  • ‑1" forces the return of an empty set from the first query.
  • UNION forces two database tables together – in this case, products and users – which enables you to obtain information (passwords) that isn’t in the original (products) table.
  • SELECT * FROM users selects all the rows in the users table.
  • The -- // sequence discards everything after the malicious query.

When you modify the URL to end in the injected code, you get a new error message:

Fatal error: Uncaught mysqli_sql_exception: The used SELECT statements have a different number of columns in /var/www/html/product.php:23 Stack trace: #0 /var/www/html/product.php(23): mysqli->query('SELECT * FROM p...') #1 {main} thrown in /var/www/html/product.php on line 23

This message reveals that the products and users tables don’t have the same number of columns, so the UNION instruction can’t be executed. But you can discover the number of columns through trial and error by adding columns (field names) one at a time as parameters to the SELECT instruction. A good guess at a field name in a users table is password, so try that:

# select 1 column-1" UNION SELECT password FROM users; -- //
# select 2 columns 
-1" UNION SELECT password,password FROM users; -- //
# select 3 columns 
-1" UNION SELECT password,password,password FROM users; -- /
# select 4 columns 
-1" UNION SELECT password,password,password,password FROM users; -- //
# select 5 columns
-1" UNION SELECT password,password,password,password,password FROM users; -- //

The last query succeeds (telling you there are five columns in the users table) and you see a user password:

At this point you don’t know the username that corresponds to this password. But knowing the number of columns in the users table, you can use the same types of query as before to expose that information. Assume that the relevant field name is username. And that turns out to be right – the following query exposes both the username and password from the users table. Which is great – unless this app is hosted on your infrastructure!

-1" UNION SELECT username,username,password,password,username FROM users where id=1 -- //

Challenge 3: Use an NGINX Sidecar Container to Block Certain Requests

The developer of the online store app obviously needs to pay more attention to sanitizing user input (such as use of parameterized queries), but as a Kubernetes engineer you can also help prevent SQL injection by blocking the attack from reaching the app. That way, it doesn’t matter as much that the app is vulnerable.

There are many ways to protect your apps. For the rest of this lab, we focus on two:

Deploy NGINX Open Source as a Sidecar

  1. Create a YAML file called 2-app-sidecar.yaml with the following contents (or copy from GitHub). Important aspects of the configuration include:

    • A sidecar container running NGINX Open Source is started on port 8080.
    • NGINX forwards all traffic to the app.
    • Any request that includes (among other character strings) SELECT or UNION is denied (see the first location block in the ConfigMap section).
    • The service for the app routes all traffic to the NGINX container first.
    apiVersion: apps/v1 
    kind: Deployment 
    metadata: 
      name: app 
    spec: 
      selector: 
        matchLabels: 
          app: app 
      template: 
        metadata: 
          labels: 
            app: app 
        spec: 
          containers: 
            - name: app 
              image: f5devcentral/microservicesmarch:1.0.3 
              ports: 
                - containerPort: 80 
              env: 
                - name: MYSQL_USER 
                  value: dan 
                - name: MYSQL_PASSWORD 
                  value: dan 
                - name: MYSQL_DATABASE 
                  value: sqlitraining 
                - name: DATABASE_HOSTNAME 
                  value: db.default.svc.cluster.local 
            - name: proxy # <-- sidecar 
              image: "nginx" 
              ports: 
                - containerPort: 8080 
              volumeMounts: 
                - mountPath: /etc/nginx 
                  name: nginx-config 
          volumes: 
            - name: nginx-config 
              configMap: 
                name: sidecar 
    --- 
    apiVersion: v1 
    kind: Service 
    metadata: 
      name: app 
    spec: 
      ports: 
        - port: 80 
          targetPort: 8080 # <-- the traffic is routed to the proxy 
          nodePort: 30001 
      selector: 
        app: app 
      type: NodePort 
    --- 
    apiVersion: v1 
    kind: ConfigMap 
    metadata: 
      name: sidecar 
    data: 
      nginx.conf: |- 
        events {} 
        http { 
          server { 
            listen 8080 default_server; 
            listen [::]:8080 default_server; 
    
            location ~* "(\'|\")(.*)(drop|insert|md5|select|union)" { 
                deny all; 
            } 
    
            location / { 
                proxy_pass http://localhost:80/; 
            } 
          } 
        } 
    --- 
    apiVersion: apps/v1 
    kind: Deployment 
    metadata: 
      name: db 
    spec: 
      selector: 
        matchLabels: 
          app: db 
      template: 
        metadata: 
          labels: 
            app: db 
        spec: 
          containers: 
            - name: db 
              image: mariadb:10.3.32-focal 
              ports: 
                - containerPort: 3306 
              env: 
                - name: MYSQL_ROOT_PASSWORD 
                  value: root 
                - name: MYSQL_USER 
                  value: dan 
                - name: MYSQL_PASSWORD 
                  value: dan 
                - name: MYSQL_DATABASE 
                  value: sqlitraining 
    
    --- 
    apiVersion: v1 
    kind: Service 
    metadata: 
      name: db 
    spec: 
      ports: 
        - port: 3306 
          targetPort: 3306 
      selector: 
        app: db
    
  2. Deploy the sidecar:

    $ kubectl apply -f 2-app-sidecar.yaml deployment.apps/app configured 
    service/app configured 
    configmap/sidecar created 
    deployment.apps/db unchanged 
    service/db unchanged 
    

Test the Sidecar as a Filter

Test whether the sidecar is filtering traffic by returning to the app and trying the SQL injection again. NGINX blocks the request before it reaches the app!

-1" UNION SELECT username,username,password,password,username FROM users where id=1 -- // 

Challenge 4: Configure NGINX Ingress Controller to Filter Requests

Protecting your app as in Challenge 3 is interesting as an educational experience, but we don’t recommend it for production because:

  • It is not a full security solution.
  • It is not scalable (you can’t easily apply this protection to multiple apps).
  • Updating it is complicated and inefficient.

A much better solution is using NGINX Ingress Controller to extend the same protection to all of your apps! Ingress controllers can be used to centralize all kinds of security features, from blocking requests like a web application firewall (WAF) does to authentication and authorization.

In this challenge, you deploy NGINX Ingress Controller, configure traffic routing, and verify that the filter blocks the SQL injection.

Deploy NGINX Ingress Controller 

The fastest way to install NGINX Ingress Controller is with Helm.  

  1. Add the NGINX repository to Helm: 

    $ helm repo add nginx-stable https://helm.nginx.com/stable  
    
  2. Download and install the NGINX Open Source‑based NGINX Ingress Controller, which is maintained by F5 NGINX. Note the enableSnippets=true parameter: snippets are used to configure NGINX to block the SQL injection. The final line of output confirms successful installation.

    $ helm install main nginx-stable/nginx-ingress \ --set controller.watchIngressWithoutClass=true
    --set controller.service.type=NodePort \ 
    --set controller.service.httpPort.nodePort=30005 \ 
    --set controller.enableSnippets=true
    NAME: main  
    LAST DEPLOYED: Day Mon DD hh:mm:ss YYYY  
    NAMESPACE: default  
    STATUS: deployed  
    REVISION: 1  
    TEST SUITE: None  
    NOTES: The NGINX Ingress Controller has been installed.  
    
  3. Confirm that the NGINX Ingress Controller pod deployed, as indicated by the value Running in the STATUS column. 

    $ kubectl get pods   NAME                                READY   STATUS  ...
    main-nginx-ingress-779b74bb8b-mtdkr 1/1     Running ...
    
          ... RESTARTS   AGE 
          ... 0          18s
    

Route Traffic to Your App

  1. Create a YAML file called 3-ingress.yaml with the following contents (or copy from GitHub). It defines the Ingress manifest required to route traffic to the app (not through the sidecar proxy this time). Notice the annotations: block where a snippet is used to customize the NGINX Ingress Controller configuration with the same location block as in the ConfigMap definition in Challenge 3: it rejects any request that includes (among other character strings) SELECT or UNION.

    apiVersion: v1 kind: Service 
    metadata: 
      name: app-without-sidecar 
    spec: 
      ports: 
        - port: 80 
          targetPort: 80 
      selector: 
        app: app 
    --- 
    apiVersion: networking.k8s.io/v1 
    kind: Ingress 
    metadata: 
      name: entry 
      annotations: 
        nginx.org/server-snippets: | 
          location ~* "(\'|\")(.*)(drop|insert|md5|select|union)" { 
              deny all; 
          } 
    spec: 
      ingressClassName: nginx 
      rules: 
        - host: "example.com" 
          http: 
            paths: 
              - backend: 
                  service: 
                    name: app-without-sidecar 
                    port: 
                      number: 80 
                path: / 
                pathType: Prefix 
    
  2. Deploy the Ingress resource: 
  3. $ kubectl apply -f 3-ingress.yaml service/app-without-sidecar created 
    ingress.networking.k8s.io/entry created 
    

Verify Filter Operation

  1. Launch a disposable BusyBox container to issue a request to the NGINX Ingress Controller pod with the correct hostname.

    $ kubectl run -ti --rm=true busybox --image=busybox$ wget --header="Host: example.com" -qO- main-nginx-ingress 
    <!DOCTYPE html> 
    <html lang="en"> 
    
    <head> 
    # ...
    
  2. Attempt the SQL injection. The 403 Forbidden status code confirms that NGINX blocks the attack!

     

    $ wget --header="Host: example.com" -qO- 'main-nginx-ingress/product/-1"%20UNION%20SELECT%20username,username,password,password,username%20FROM%20users%20where%2 0id=1%20--%20//' 
    wget: server returned error: HTTP/1.1 403 Forbidden 
    

Next Steps

Kubernetes is not secure by default. An Ingress controller can mitigate SQL injection (and many other) vulnerabilities. But keep in mind that the kind of WAF‑like functionality you just implemented with NGINX Ingress Controller does not replace an actual WAF, nor is it a replacement for securely architecting apps. A savvy hacker can still make the UNION hack work with some small changes to the code. For more on this topic, see A Pentester’s Guide to SQL Injection (SQLi).

That said, an Ingress controller is still a powerful tool for centralizing most of your security, leading to greater efficiency and security including centralized authentication and authorization use cases (mTLS, single sign‑on) and even a robust WAF like F5 NGINX App Protect WAF.

The complexity of your apps and architecture might require more fine‑grained control. If your organization requires Zero Trust and end-to-end encryption, consider a service mesh like the always‑free F5 NGINX Service Mesh to control communication between services in the Kubernetes cluster (east‑west traffic). We explore service meshes in Unit 4, Advanced Kubernetes Deployment Strategies.

For details on obtaining and deploying NGINX Open Source, visit nginx.org.

To try the NGINX Ingress Controller based on NGINX Plus with NGINX App Protect, start your free 30-day trial today or contact us to discuss your use cases. 

To try the NGINX Ingress Controller based on NGINX Open Source, see NGINX Ingress Controller Releases at our GitHub repo or download a prebuilt container from DockerHub. 


"This blog post may reference products that are no longer available and/or no longer supported. For the most current information about available F5 NGINX products and solutions, explore our NGINX product family. NGINX is now part of F5. All previous NGINX.com links will redirect to similar NGINX content on F5.com."