graph TD
    A[Codebase] --> B{Analysis Engine}
    B -->|Data Flow| C[CodeQL: Build Database]
    B -->|Pattern Match| D[Semgrep: Parse AST]
    C --> E[Run CodeQL Queries]
    D --> F[Run Semgrep Rules]
    E --> G[Taint Tracking: Source → Sink]
    F --> H[Pattern Matches + Metavariables]
    G --> I[Merge Findings]
    H --> I
    I --> J[Deduplicate + Prioritize]
    J --> K[Remediation Report]
    K --> L[CI/CD Gate: Pass/Fail]

# .semgrep/agent-rules.yml
rules:
  - id: sql-injection-f-string
    patterns:
      - pattern: |
          $CURSOR.execute(f"...", ...)
    message: >
      SQL query uses f-string interpolation, which is vulnerable to SQL injection.
      Use parameterized queries instead.
    severity: ERROR
    languages: [python]
    metadata:
      cwe: ["CWE-89"]
      confidence: HIGH

  - id: missing-auth-middleware
    patterns:
      - pattern: |
          @app.route($PATH, ...)
          def $FUNC(...):
              ...
      - pattern-not-inside: |
          @require_auth
          @app.route($PATH, ...)
          def $FUNC(...):
              ...
    message: >
      Route handler $FUNC lacks @require_auth decorator.
      All routes must be authenticated unless explicitly exempted.
    severity: WARNING
    languages: [python]
    metadata:
      cwe: ["CWE-306"]

  - id: no-eval-user-input
    patterns:
      - pattern: eval($X)
      - pattern-where-python: |
          not $X.startswith('"')
    message: "eval() called with potentially dynamic input"
    severity: ERROR
    languages: [python]
    metadata:
      cwe: ["CWE-95"]

/**
 * @name SQL injection from request parameter
 * @description Finds SQL queries constructed from HTTP request parameters
 * @kind path-problem
 * @severity error
 * @id agent-skills/sql-injection
 * @tags security
 *       cwe-089
 */

import python
import semmle.python.dataflow.new.TaintTracking
import semmle.python.ApiGraphs

class SqlInjectionConfig extends TaintTracking::Configuration {
  SqlInjectionConfig() { this = "SqlInjectionConfig" }

  override predicate isSource(DataFlow::Node source) {
    exists(API::CallNode call |
      call = API::moduleImport("flask").getMember("request").getMember("args").getMember("get").getACall() and
      source = call
    )
  }

  override predicate isSink(DataFlow::Node sink) {
    exists(API::CallNode call |
      call = API::moduleImport("sqlite3").getMember("Cursor").getMember("execute").getACall() and
      sink = call.getArg(0)
    )
  }
}

from SqlInjectionConfig config, DataFlow::PathNode source, DataFlow::PathNode sink
where config.hasFlowPath(source, sink)
select sink.getNode(), source, sink, "SQL query depends on $@.", source.getNode(), "user input"

# GitHub Actions
- name: Semgrep Scan
  uses: semgrep/semgrep-action@v1
  with:
    config: >-
      p/owasp-top-ten
      p/r2c-security-audit
      .semgrep/agent-rules.yml

- name: CodeQL Analysis
  uses: github/codeql-action/analyze@v3
  with:
    languages: python, javascript
    queries: +.codeql/custom-queries