🖥️ Interactive Console and WAF

Dynamic SQL engine protected by a simulated Web Application Firewall.

To demonstrate the processing of the ingested data, the Pipeline exposes an endpoint that allows executing real SQL statements directly against the PostgreSQL database from an interactive terminal.

Given the critical risk this represents by opening the database to the outside, the endpoint is shielded by multiple security layers and a regular expression filter at the controller level.

🔌 Dynamic Query Endpoint

POST · /api/v1/etl/query

Executes a read-only SQL query and returns the serialized results.

📦 Request Payload

The endpoint expects a simple JSON object with the query to execute:

{
  "query": "SELECT vehicle_vin, odometer_km FROM logiflow_telemetry WHERE vehicle_status = 'MOVING'"
}

🛡️ Security Filter (Simulated WAF)

To prevent SQL injections and the execution of destructive statements, the controller intercepts the raw query and passes it through a series of strict validations before sending it to JdbcTemplate.

🛑 Firewall Rules

1. Read Exclusivity: The statement must strictly start with the SELECT keyword.
2. Single Statements: The stacking of multiple queries through the intermediate use of semicolons (;) is prohibited.
3. Keyword Blacklist: The text is scanned using regular expressions against dangerous commands to neutralize structure manipulation or accesses.

// List of forbidden keywords in EtlController.java
String[] forbiddenKeywords = {"INSERT", "UPDATE", "DELETE", "DROP", "ALTER", "TRUNCATE", "GRANT", "REVOKE", "COMMIT", "ROLLBACK", "EXEC", "UNION", "SLEEP", "PG_SLEEP"};
for (String keyword : forbiddenKeywords) {
    if (upperSql.matches(".*\\b" + keyword + "\\b.*")) {
        return ResponseEntity.status(HttpStatus.FORBIDDEN)
                .body(Map.of("error", "[SECURITY ALERT] Possible SQL injection detected."));
    }
}

🚦 Network Saturation Protection

As a last line of defense, the transaction at the database level is marked as read-only (@Transactional(readOnly = true)). Furthermore, if the user’s original query omits a limit clause, the server automatically injects a LIMIT 15.

This prevents network saturation and memory collapse on the frontend in case a massive SELECT * is executed on a table with tens of thousands of logistics records.

// Dynamic limit injection in EtlController.java
if (!upperSql.contains("LIMIT")) {
    finalSql = finalSql + " LIMIT 15";
}