cybersecurity

Strategies for Avoiding Cache during Time-Based SQL Injection

Time-based SQL injection is a type of attack where the attacker checks if a website has a vulnerability by measuring how long it takes for the database to respond. In this attack, the attacker injects SQL code into a web input field, like a search box or form, to make the database respond slowly on purpose.

Instead of showing errors or returning data directly, as with other SQL injection methods, time-based SQL injection only shows the attacker whether a delay happened. For example, the attacker might use an SQL query like this:

1' OR IF(1=1, SLEEP(5), 0) --

In this example, if the database is vulnerable, it will wait (or “sleep”) for 5 seconds before responding. This delay tells the attacker that their code worked, confirming that the database is at risk. Time-based SQL injection effectiveness is highly dependent on direct and uninterrupted interaction with the database, which modern environments with reverse proxies, cache servers, and other optimizations can disrupt. Modern web environments often include features like reverse proxies, caching, and content delivery networks (CDNs) to improve performance and handle high traffic. These features can interfere with security testing methods, including time-based SQL Injection.

Learn more about blind SQL injection

How caching and proxies affect time-based SQL injection

A common example is a reverse proxy with caching. When a tester (or hacker) sends a request with a time-based SQL injection payload, such as 1' OR IF(1=1, SLEEP(5), 0) --, the first request might reach the database and cause a 5 second delay, suggesting a possible vulnerability. But when caching is enabled, the proxy might store this response. For repeated identical requests, the response could be served from the cache without being executed by the database. This could cause the tester to mistakenly believe that the vulnerability is a false positive or doesn’t exist because the expected delay does not occur.

Testing scenario for checking time-based SQL injection

This test shows how a reverse proxy with caching, like Nginx, can affect testing for time-based SQL injection vulnerabilities. By configuring Nginx as a reverse proxy with caching enabled, we can see how repeated requests may be handled differently.

Configuring Nginx for reverse proxy and caching

Nginx is set up to serve as a reverse proxy for a backend PHP application connected to a database:

proxy_cache_path /var/cache/nginx levels=1:2 keys_zone=my_cache:10m max_size=1g inactive=60s;

server {
    listen 80;
    # Configure the backend service
    location / {
        proxy_pass http://web:80;
        proxy_cache my_cache;  # Enable caching
        proxy_cache_bypass $arg_cache_bypass;  # Allows cache bypass if needed
        proxy_cache_valid 200 10s;  # Cache successful responses for 10 seconds
        add_header X-Cache-Status $upstream_cache_status;
    }
}

Docker Compose setup

Here is a basic example of a docker-compose.yml file that sets up an environment with Nginx as a reverse proxy, a PHP application with a vulnerable endpoint, and a MySQL database.

DB service (MySQL database):

db:
        image: mysql:5.7
        environment:
            MYSQL_ROOT_PASSWORD: secret
            MYSQL_DATABASE: test
            MYSQL_USER: tim
            MYSQL_PASSWORD: test
        ports:
        - 3306:3306
        volumes:
        - ./dump:/docker-entrypoint-initdb.d
        restart: always
        container_name: mysql_database

This initializes a MySQL database with data from dump.sql, providing a test database for SQL injection.

Web service (PHP web application):

web:
        build:
            context: ./
        container_name: php_web
        depends_on:
        - db
        volumes:
        - ./php/:/var/www/html/
        ports:
        - 8080:80
        stdin_open: true
        tty: true
        restart: always

This service runs the vulnerable PHP application, linking it with the database and making it accessible on port 8080.

RP service (Nginx Reverse Proxy):

rp:
        image: nginx:latest
        depends_on:
        - web
        ports:
        - 80:85
        volumes:
        - ./nginx.conf:/etc/nginx/nginx.conf
        restart: always
        container_name: reverse_proxy

Acts as a reverse proxy. It uses the custom configuration from nginx.conf to enable caching.

Running payloads

As you can see in the lower right corner, the payload executed successfully, and the response was delayed by 5 s (X-Cache-Status: MISS).

But the second response was delayed only 13 ms due to the cache server’s response.

Cache bypass techniques

Add unique parameters: By adding a random or unique parameter to each request, caching mechanisms treat each request as new. For example, you can add &cachebuster=12345 at the end of the URL, where 12345 is a random number that changes with each request.

Using float numbers: By making small adjustments in the delay value (e.g., 5 vs. 5.0), caching mechanisms treat each request as unique due to the difference in the query structure. For example, using SLEEP(5) in one request and SLEEP(5.0) in the next can prevent the caching layer from recognizing these as identical.

Using arithmetic operations: By adding arithmetic expressions within the delay function (e.g., SLEEP(3+2) or SLEEP(10/2) instead of SLEEP(5)), caching mechanisms interpret each request as unique due to the difference in query structure.

Adding comments in payloads: By inserting random comments (e.g., /*234*/) within the SQL payload, you can make each request appear unique to caching mechanisms, even though the query logic remains unchanged. Here’s an example using 1' OR IF(1=1, SLEEP(5), 0) -- /*234*/:

Using redundant expressions in payloads: Adding harmless expressions, such as 123=123, makes each request appear unique to caching mechanisms while keeping the SQL logic unchanged. Here’s how it works for the query 1' OR IF(1=1, SLEEP(5), 0) AND 123=123 --:

Why we need this approach

When testing for time-based SQL injection vulnerabilities using ZAP, I encountered an issue after introducing a cache server. When I repeated a test scan with the cache server, the response was served directly from the cache for identical requests after the first one, returning instantly without re-executing the SQL query. As a result, ZAP did not report the SQL injection vulnerability, as the caching mechanism masked the delay that would typically indicate successful injection. This shows how caching layers can disrupt time-based testing by providing immediate, cached responses that bypass the backend entirely, leading to missed detections.

Scan results without a cache server (vulnerability found):