AOSSIE-Org / AOSSIE-Org/PictoPy

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

Abierto
#1,216 3 comentarios 0 reacciones 0 asignados Ver en GitHub
Lenguaje dominante
Python
Estrellas
283
Forks
679
Merge medio
7 d 2 h
PR fusionados (30 d)
3

Descripción

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

Guía de contribución

Abrir la guía de contribución

Evaluación

Este issue todavía no se ha evaluado.

Recibe los nuevos issues en tu correo

Un resumen breve de issues de GitHub para principiantes.