SQL Injection in PaginatedUtmLinksPresenter via Unvalidated Sort Direction #7

Vulnerability: SQL Injection in ORDER BY clause.

File: /app/app/presenters/paginated_utm_links_presenter.rb, Line 63

Sink: order(Arel.sql("\#{sort_key} \#{sort_direction}"))

Source: The sort_direction variable originates from the params[:sort][:direction] parameter, likely passed through the Api::Internal::UtmLinksController#index action.

Analysis: The code validates the sort_key against an allow-list (SORT_KEYS). However, the sort_direction variable, derived from params[:sort][:direction], is directly interpolated into the Arel.sql fragment without being validated against expected values like 'asc' or 'desc'.

Impact: An attacker can manipulate the sort[direction] request parameter to inject arbitrary SQL into the ORDER BY clause. This can be used for time-based blind SQL injection to exfiltrate data, cause denial of service, or potentially perform other database operations depending on the context and database permissions.

Proof of Concept (Conceptual):
A request targeting the relevant endpoint (e.g., /api/internal/utm_links) with parameters like:
?sort[key]=created_at&sort[direction]=desc,(SELECT+CASE+WHEN+(1=1)+THEN+SLEEP(5)+ELSE+SLEEP(0)+END)
If the database is MySQL/MariaDB, this could cause a 5-second delay, confirming the injection. The exact payload needs adjustment based on the specific database system.

Recommendation: Strictly validate the sort_direction parameter against an allow-list (e.g., ['asc', 'desc']) before using it in the SQL query. If the value is not 'asc' or 'desc', default to a safe value (e.g., 'asc') or raise an error.