AOSSIE-Org / AOSSIE-Org/PictoPy

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

Ouverte
#1,216 3 commentaires 0 réactions 0 personnes assignées Voir sur GitHub
Langage dominant
Python
Étoiles
283
Forks
679
Merge moyen
7 j 2 h
PR mergées (30 j)
3

Description

### 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

Guide de contribution

Ouvrir le guide de contribution

Évaluation

Cette issue n'a pas encore été évaluée.

Recevez les nouvelles issues par e-mail

Un résumé court des issues GitHub adaptées aux débutants.