|
1
|
"""Custom SQL-based ticket reports. |
|
2
|
|
|
3
|
Fossil supports running SQL queries against its built-in ticket table. |
|
4
|
This model lets admins define reusable reports that project members can |
|
5
|
execute. Queries run in read-only mode against the Fossil SQLite file. |
|
6
|
""" |
|
7
|
|
|
8
|
import re |
|
9
|
|
|
10
|
from django.db import models |
|
11
|
|
|
12
|
from core.models import ActiveManager, Tracking |
|
13
|
|
|
14
|
# Statements that are never allowed in a report query. |
|
15
|
_FORBIDDEN_KEYWORDS = re.compile( |
|
16
|
r"\b(INSERT|UPDATE|DELETE|DROP|ALTER|CREATE|REPLACE|ATTACH|DETACH|REINDEX|VACUUM|PRAGMA)\b", |
|
17
|
re.IGNORECASE, |
|
18
|
) |
|
19
|
|
|
20
|
|
|
21
|
class TicketReport(Tracking): |
|
22
|
"""Custom SQL-based ticket report.""" |
|
23
|
|
|
24
|
repository = models.ForeignKey("fossil.FossilRepository", on_delete=models.CASCADE, related_name="ticket_reports") |
|
25
|
title = models.CharField(max_length=200) |
|
26
|
description = models.TextField(blank=True, default="") |
|
27
|
sql_query = models.TextField(help_text="SQL query against the Fossil ticket table. Use {status}, {type} as placeholders.") |
|
28
|
is_public = models.BooleanField(default=True, help_text="Visible to all project members") |
|
29
|
|
|
30
|
objects = ActiveManager() |
|
31
|
all_objects = models.Manager() |
|
32
|
|
|
33
|
class Meta: |
|
34
|
ordering = ["title"] |
|
35
|
|
|
36
|
def __str__(self): |
|
37
|
return self.title |
|
38
|
|
|
39
|
@staticmethod |
|
40
|
def validate_sql(sql: str) -> str | None: |
|
41
|
"""Return an error message if *sql* is unsafe, or None if it passes. |
|
42
|
|
|
43
|
Rules: |
|
44
|
- Must start with SELECT (after stripping whitespace/comments). |
|
45
|
- Must not contain any write/DDL keywords. |
|
46
|
- Must not contain multiple statements (semicolons aside from trailing). |
|
47
|
""" |
|
48
|
stripped = sql.strip().rstrip(";").strip() |
|
49
|
if not stripped: |
|
50
|
return "Query cannot be empty." |
|
51
|
|
|
52
|
if not re.match(r"(?i)^\s*SELECT\b", stripped): |
|
53
|
return "Query must start with SELECT." |
|
54
|
|
|
55
|
if _FORBIDDEN_KEYWORDS.search(stripped): |
|
56
|
return "Query contains forbidden keywords (INSERT, UPDATE, DELETE, DROP, ALTER, etc.)." |
|
57
|
|
|
58
|
# Reject multiple statements: strip string literals then check for semicolons. |
|
59
|
no_strings = re.sub(r"'[^']*'", "", stripped) |
|
60
|
if ";" in no_strings: |
|
61
|
return "Query must not contain multiple statements." |
|
62
|
|
|
63
|
return None |
|
64
|
|