Injection via string interpolation in Insight Worker and related modules

> [!NOTE]
> Migrated from [augurlabs/augur#3585](https://github.com/augurlabs/augur/issues/3585)
> Originally opened by `@Sukuna0007Abhi` on 2026-01-16

---

### Description
Our codebase constructs SQL queries by interpolating repository-derived values (e.g., repo_id) directly into SQL strings using .format() or f-strings. This allows SQL injection if an attacker can influence those values (for example: repo_id = "1; DROP TABLE commits; --"). I think it's a low priority security issue...

I will work on this...

### Affected

## tasks.py

```
table_values_sql = s.sql.text("""SELECT * FROM repo_insights_records WHERE repo_id={}""".format(repo_id))
```

**multiple other .format() uses in the same file (e.g., .format(insight['repo_id']))**

## tasks.py

```
repoSQL = s.sql.text(""" ... WHERE repo_id = {} """.format(repo_id))
```

## messages.py

```
SQL using {repo_id} and '{since}' inside f-strings / .format() (e.g., select pr_comments_url ... WHERE repo_id={repo_id} AND pr_updated_at > timestamptz(timestamp '{since}'))
```

## tasks.py

```
Similar to insight_worker, uses f-string interpolation for repo_id in SQL queries (e.g., SELECT with :repo_id placeholders after parameterization).
```

## events.py

```
get_ranked_issues = s.text(f""" ... where rn=1 and repo_id={repo_id} and cntrb_id is not NULL """)

```

## lib.py

```
update_issue_closed_cntrbs_by_repo_id() constructs a query with f-string-interpolated repo_id
```

### Check

1. A value that ends up in code as repo_id (or similar) is set to a malicious string (via import, DB corruption, or an input path).
2. The code constructs SQL with .format() or f-strings, e.g.:

## insight worker:

```table_values_sql = s.sql.text("""SELECT * FROM repo_insights_records WHERE repo_id={}""".format(repo_id))
pd.read_sql(table_values_sql, conn, params={})
```

## lib.py:

```get_ranked_issues = s.text(f"""... where rn=1 and repo_id={repo_id} ...""")
result = conn.execute(get_ranked_issues).fetchall()
```

3. The DB executes the constructed string exactly — the malicious fragment becomes executable SQL.

## Effects : because if stack statement is block; still Boolean/UNION can leak or confirm the injection..
If DB allows :
#### load : `1; DROP TABLE commits; --`
#### Resulting: `SELECT * FROM repo_insights_records WHERE repo_id=1; DROP TABLE commits; --`

#### load: `; DROP TABLE repo_insights_records; --`
#### Resulting: If used inside a quoted expression, it terminates strings and executes DROP.

#### load: `0 OR 1=1`
#### Resulting : Changes WHERE to true , returns all rows...

#### laod: `1 UNION SELECT password FROM users --` can be used but required column alignment...

Note: DB drivers block stacked statements; even if stacking is blocked, payloads like 0 OR 1=1, UNION SELECT ..., or time-based payloads can still cause issues...

### Where this will executed...

- pd.read_sql(...) will execute whatever SQL text you give it. If that text contains injected SQL, it gets executed at DB level.
- conn.execute(s.text(...)) will execute the final string.

So if that SQL string contains injected payloads (e.g., ; DROP TABLE ... or OR 1=1), those fragments will be delivered to the DB and can be executed

Example of running :
Error part:
```
table_values_sql = s.sql.text("SELECT * FROM repo_insights_records WHERE repo_id = {}".format(repo_id))
insight_table_values = pd.read_sql(table_values_sql, conn, params={})
```
If `repo_id = "1;DROP TABLE commits; --`, the DB receives :

```
SELECT * FROM repo_insights_records WHERE repo_id=1; DROP TABLE commits; --
```
and the DROP may execute...

I think the solution might be to replace the string interpolation with bind parameters..