Static Analysis

// SCENARIO — wiring SQLi-focused SAST into CI to catch concatenation before it ships.
// We focus on configuration + defensive engineering.

// ─── 1) Semgrep — fast, language-agnostic, easy custom rules ──
//
// Install:    brew install semgrep
// Run rules:  semgrep --config p/owasp-top-ten .
//             semgrep --config p/r2c-security-audit .

// Custom rule — block string concat with SQL keywords (Node)
// .semgrep/sql-concat.yml
rules:
    - id: nodejs-sql-string-concat
      patterns:
          - pattern-either:
                - pattern: db.query(`...SELECT...${EXPR}...`)
                - pattern: db.query('...SELECT...' + EXPR + '...')
                - pattern: db.query(`...INSERT...${EXPR}...`)
                - pattern: db.execute(`...UPDATE...${EXPR}...`)
      message: Use parameterised queries — '?' placeholders, never string interpolation.
      languages: [javascript, typescript]
      severity: ERROR
      metadata:
          cwe: 'CWE-89'
          owasp: 'A03:2021 Injection'

// Custom rule — Python (sqlite3 / psycopg)
rules:
    - id: python-sql-fstring
      patterns:
          - pattern-either:
                - pattern: cursor.execute(f'...SELECT...{EXPR}...')
                - pattern: cursor.execute(f'...INSERT...{EXPR}...')
                - pattern: cursor.execute('...SELECT...' + EXPR + '...')
                - pattern: cursor.execute('...SELECT...%s...' % EXPR)
      message: Use parameter binding — pass values as a SECOND argument to execute().
      languages: [python]
      severity: ERROR

// Run in CI
// .github/workflows/semgrep.yml
name: semgrep
on: [push, pull_request]
jobs:
    scan:
        runs-on: ubuntu-latest
        container: returntocorp/semgrep
        steps:
            - uses: actions/checkout@v4
            - run: semgrep --config p/owasp-top-ten --config .semgrep/ --error

// ─── 2) CodeQL — GitHub's deep dataflow analysis ──────────────
// Tracks user input from a SOURCE (req.body, req.query) through to a SINK (db.query)
// and warns when no SANITIZER step intervened.

// .github/workflows/codeql.yml
name: codeql
on: [push, pull_request]
jobs:
    analyze:
        runs-on: ubuntu-latest
        permissions: { security-events: write }
        steps:
            - uses: actions/checkout@v4
            - uses: github/codeql-action/init@v3
              with: { languages: javascript, queries: 'security-and-quality' }
            - uses: github/codeql-action/analyze@v3

// CodeQL queries for SQLi:
//   js/sql-injection
//   py/sql-injection
//   java/sql-injection
//   csharp/sql-injection
//   go/sql-injection
//
// Findings show up in the GitHub Security tab.

// ─── 3) Custom CodeQL — find ALL execute() calls reaching string concat ──
// (Advanced; CodeQL has a steeper learning curve than Semgrep.)
import javascript
from CallExpr c, Expr e
where c.getCalleeName() = "execute" or c.getCalleeName() = "query"
     and e = c.getArgument(0)
     and e instanceof TemplateLiteral
select c, "Possible SQLi: template literal used as a query."

// ─── 4) ESLint plugins (JavaScript / TypeScript) ──────────────
// • eslint-plugin-security
// • @typescript-eslint with custom rules

// eslint.config.js
import security from 'eslint-plugin-security';
export default [
    { plugins: { security }, rules: security.configs.recommended.rules },
];

// Catches: 'detect-sql-literal-injection', 'detect-non-literal-fs-filename', etc.

// ─── 5) Snyk Code — proprietary SAST with strong dataflow ─────
// • Web UI + CLI: snyk code test
// • CI integration: snyk-cli action
// • Pricing: free tier for open source; paid for private repos

// ─── 6) SonarQube / SonarCloud — multi-language, dashboards ──
// • Includes SQLi detection rules per language
// • Pull-request decoration with findings inline
// • Quality gate: 'no new vulnerabilities on changed lines'

// ─── 7) GitHub Advanced Security — Dependency + Code + Secret scanning ──
// Includes CodeQL + secret scanning + Dependabot. Highest signal for GitHub-hosted repos.

// ─── 8) Language-specific tools ───────────────────────────────
// Go:      gosec (pattern-based)
// Rust:    cargo-audit (deps); some Clippy lints; no widely-used SAST
// Java:    SpotBugs + FindSecBugs plugin; CodeQL
// PHP:     Psalm + psalm-plugin-laminas; PHPStan with extensions
// Ruby:    Brakeman (Rails-specific)
// .NET:    Roslyn analyzers + SecurityCodeScan

// ─── 9) Triaging SAST findings ────────────────────────────────
// Three buckets per finding:
//   • True positive — fix the code
//   • False positive — annotate to suppress + document why
//   • Won't fix — risk-accepted by security + engineering leadership
//
// Annotate suppressions inline so reviewers see them in diffs:
//   // semgrep-disable-next-line nodejs-sql-string-concat
//   // explanation: this string contains only schema metadata, no user input

// ─── 10) Pre-commit hooks — catch BEFORE CI ──────────────────
// .pre-commit-config.yaml
repos:
    - repo: https://github.com/returntocorp/semgrep
      rev: v1.45.0
      hooks:
          - id: semgrep
            args: ['--config', 'p/owasp-top-ten', '--error']

// Fast feedback; runs only on changed files.

// ─── 11) Combining with DAST ─────────────────────────────────
// SAST catches bugs at code level. DAST catches bugs at runtime. Use both:
//   • SAST in CI on every PR
//   • DAST nightly on staging
//   • IAST (runtime instrumentation) in pre-prod for the hardest-to-find bugs
//   • SCA (Dependabot, Snyk Open Source) for known-CVE deps

// ─── 12) Eliminate the bug CLASS, not just the bugs ──────────
// Best: design the system so concat-style SQLi is IMPOSSIBLE.
//   • Use a query builder or ORM that rejects raw strings
//   • Centralise the DB layer (one repository per aggregate)
//   • Lint for `db.query(`...${...}...`)` and disallow entirely
//   • Code-review checklist line: 'Is every dynamic value a bind parameter?'
//
// SAST then becomes a regression net, not the primary control.

// ─── 13) Reporting + metrics ─────────────────────────────────
// Track:
//   • Number of new SAST findings per PR (target: 0 criticals)
//   • Time-to-fix per severity
//   • False-positive rate (helps you tune rules)
//   • Rule coverage by language / repo
//   • Engineer awareness — train teams on the most common findings each quarter

// ─── 14) Common bugs / mistakes ──────────────────────────────
// • SAST disabled because of false positives — tune rules + suppress with explanations, don't disable
// • Rule coverage gaps — e.g. Semgrep config doesn't include the language your repo uses
// • Findings ignored because nobody owns them — assign by code ownership
// • SAST only on main branch — bugs already merged; run on PRs to catch BEFORE merge
// • Mixing SAST with linter exit codes — separate severities; lint warnings shouldn't fail CI on SAST criticals
// • SAST without DAST/SCA — half the picture; combine
// • Pure pattern matching missing dataflow — invest in CodeQL for high-value code paths

// Semgrep / CodeQL / SonarQube ship SQLi rules by default.
// Wire them into CI; fail the PR on new High findings.