AOSSIE-Org / AOSSIE-Org/PictoPy

[Security/Bug]: SQL Injection vulnerability in dynamic UPDATE queries via unvalidated column names

Đang mở
#1,216 3 bình luận 0 reaction 0 người được giao Xem trên GitHub
Ngôn ngữ chính
Python
Star
283
Fork
679
Merge trung bình
7 ngày 2 giờ
Pull request đã merge (30 ngày)
3

Mô tả

### Is there an existing issue for this?

- [x] I have searched the existing issues

### What happened?

## Description
During a recent static security analysis of the `pictopy` repository, a genuine **SQL Injection (CWE-89)** risk was identified in the backend's database update logic.

While the *values* in the SQL queries are safely parameterized, the **column names** are being injected directly into the SQL string using Python `f-strings` and `.join()`. Because parameterization (`?`) only protects values and not identifiers (like table or column names), this creates an injection vector if the column names are derived from untrusted user input (e.g., parsed directly from a JSON request payload).

## Vulnerability Details
**File:** `backend/app/database/face_clusters.py` (and potentially other files using dynamic updates)
**Lines:** ~223-225

```python
cursor.execute(
f"UPDATE face_clusters SET {', '.join(update_fields)} WHERE cluster_id = ?",
update_values,
)

```
# Attack Scenario:
If a malicious user sends a request payload where the JSON key (which populates update_fields) is manipulated—for example, {"cluster_name = 'hacked' --": "value"}—the resulting SQL query structure will be altered, bypassing the intended logic and allowing arbitrary database manipulation.
Expected Behavior
Column names in UPDATE queries must be strictly validated against a hardcoded allowlist before being dynamically formatted into the SQL string.
Proposed Solution
Implement an allowlist (e.g., ALLOWED_COLUMNS) and filter the requested update fields before constructing the query.
Here is an example of how this can be secured:
# 1. Define safe columns for this specific table
ALLOWED_COLUMNS = {"cluster_name", "face_image_base64", "is_hidden"}

safe_fields = []
safe_values =

### Record

- [x] I agree to follow this project's Code of Conduct

Hướng dẫn đóng góp

Mở hướng dẫn đóng góp

Đánh giá

Issue này chưa được đánh giá.

Nhận issue mới trong hộp thư của bạn

Bản tóm tắt ngắn những issue GitHub phù hợp với người mới.