Lab 3.2·Pipeline & RuntimeCRITICAL

The injection bug review missed

A new product-search endpoint builds its SQL query by concatenating the search term. Two reviewers approved it on a busy Friday.

Weakness: CWE-89 · SQL injection · OWASP A03 Injection
Target: GET /search in sec-lab/app

static analysis (SAST) with Semgreptaint: sources and sinksparameterised queriesSAST as a PR gate

Run these techniques only against the lab you own. Using them on systems you don't have permission to test is illegal.

The threat

How this weakness gets abused

01Why string-built queries are dangerous
When user input is concatenated into SQL text, the database can't tell data from code. Input containing SQL syntax changes what the query does: reading other tables, bypassing filters, or modifying data. SQL injection has been in the OWASP Top 10 since the list began, and is still regularly found in new code.
sec-lab/app/server.js (the code under review)add to filejs
```
app.get("/search", async (req, res) => {
  const q = req.query.q ?? "";
  const { rows } = await db.query(
    "select id, name, price from products where name ilike '%" + q + "%'",
  );
  res.json(rows);
});
```

What's at risk

Potential read access to any table the database user can see, including customers and orders, and possibly writes.

Detect

How you'd catch it — before or during

01Semgrep finds the pattern in seconds
Semgrep matches code structure, not text. Its community rulesets include taint rules that follow data from SOURCES (req.query, req.body) to SINKS (a database query built from a string), and report the path.
terminal
$ docker run --rm -v "$PWD:/src" semgrep/semgrep:1.128.0 semgrep scan --config p/javascript --config p/expressjs --error /src/app
── output ──
┌─────────────────┐
│ 1 Code Finding │
└─────────────────┘

app/server.js
❯❱ javascript.express.security.injection.tainted-sql-string.tainted-sql-string
Detected user input used to manually construct a SQL string. This is usually bad practice
because manual construction could accidentally result in a SQL injection. ... use
parameterized queries or prepared statements instead.

24┆ "select id, name, price from products where name ilike '%" + q + "%'",

Defend

Fix the cause, not just this instance

01Parameterise — the database separates code from data
With placeholders ($1), the query text is fixed and the value is sent separately; the database never interprets it as SQL, whatever it contains. Add input limits too (length, type) as defence in depth. For dynamic identifiers like sort columns, use an allowlist; placeholders only work for values.
Vulnerable
server.jsadd to filejs
await db.query( "select id, name, price from products where name ilike '%" + q + "%'", );
Hardened
server.jsadd to filejs
const q = String(req.query.q ?? "").slice(0, 100); const { rows } = await db.query( "select id, name, price from products where name ilike $1 limit 50", [`%${q}%`], );

02Run SAST on every pull request

Scan only changed files on PRs for speed (--baseline-commit), fail on new findings, and show them inline with SARIF. Keep the rule set focused (security rules, not style) so developers trust it.

Hardened

.github/workflows/sast.ymlwhole fileyaml

on: pull_request
permissions: { contents: read, security-events: write }
jobs:
  semgrep:
    runs-on: ubuntu-latest
    container: semgrep/semgrep:1.128.0
    steps:
      - uses: actions/checkout@<sha> # v4
        with: { fetch-depth: 0 }
      - run: semgrep scan --config p/javascript --config p/expressjs --error --sarif -o semgrep.sarif --baseline-commit "origin/${{ github.base_ref }}"
      - uses: github/codeql-action/upload-sarif@<sha> # v3
        if: always()
        with: { sarif_file: semgrep.sarif }

Verify

Prove the fix with the same checks

01The scan passes and the endpoint behaves
Also add an integration test asserting that search terms containing quotes and SQL keywords return an ordinary (empty) result rather than an error or extra rows. That keeps the fix from regressing even if the scanner's rules change.
terminal
$ docker run --rm -v "$PWD:/src" semgrep/semgrep:1.128.0 semgrep scan --config p/javascript --config p/expressjs --error -q /src/app && echo clean
── output ──
clean

The concepts

SAST, SCA, DAST, IAST

SAST analyses your source code for vulnerable patterns (Semgrep, CodeQL). SCA analyses your dependencies (Chapter 1). DAST tests the running application from outside (OWASP ZAP). IAST instruments the app while tests run. Each finds different things; SAST in PRs is the cheapest way to stop injection-class bugs before they merge.

Writing your own rules

The biggest SAST win is often a custom rule for YOUR codebase's dangerous patterns: 'never call db.query with a template string', 'every Express route must use the auth middleware'. Semgrep rules are YAML with code-like patterns, and can be written in minutes.

Your turn

Write a Semgrep rule that flags any db.query(...) whose first argument is a template literal.

Interview questions

How do you prevent SQL injection?

0/4 · 0%

Command Palette