Security Track · Day 2 Code Audits · 20 min
Automated SAST & Secrets Detection
Static Analysis & Gitleaks — Day 2
Audit vulnerable-store-api with Gitleaks for high-entropy tokens and author custom Semgrep AST rules to detect unparameterized SQL injections.
About this lab
In this lab playbook, you will inspect source code without executing it (Static Application Security Testing). You will use Gitleaks to scan commit history for inadvertently hardcoded API tokens, and write custom Abstract Syntax Tree (AST) rules in Semgrep to catch unparameterized SQL queries in server.js.
📦Sample Codebase & Working Solution (Day 2)Available Now
View on GitHubThe official reference codebase for Day 2 is hosted in the repository. Clone the repository and navigate to day-2-sast-secrets to run the working solution.
Git Clone — Working Solution
git clone https://github.com/letstrnsfrm-ai/security-compliance-roadmap.git
cd security-compliance-roadmap/day-2-sast-secrets🏭Production Context & Enterprise Implementation
💡 Why We Are Doing This: Leaked API tokens on public GitHub repos get compromised in under 60 seconds by automated threat bots. Unsanitized SQL query string concatenations remain the #1 critical vector in OWASP Top 10 A03: Injection.
🏢 Real-World Production Usecase: Enforcing pre-commit hooks and PR status checks that parse commit diffs for Shannon entropy spikes (secrets) and dangerous AST function invocations.
⚙️ How Implemented in Production: Gitleaks scanning staged git trees + Semgrep CI scanning PR branches and exporting standard OASIS SARIF files directly to GitHub Security tabs.
Phase 1
Inject Hardcoded Secrets & Insecure SQL Queries
Create an Express server file (server.js) simulating vulnerable legacy code containing hardcoded credentials and string-concatenated SQL queries.
Step 1.1: Create Vulnerable server.js
server.js (Deliberately Vulnerable Code)
const express = require('express');
const sqlite3 = require('sqlite3').verbose();
const app = express();
app.use(express.json());
// ⚠️ VULNERABILITY 1: Hardcoded AWS Production Key & JWT Secret
const AWS_SECRET_KEY = "AKIAIOSFODNN7EXAMPLE_SECRET_KEY_PROD";
const JWT_PRIVATE_SECRET = "super-secret-production-signing-key-12345";
const db = new sqlite3.Database(':memory:');
db.serialize(() => {
db.run("CREATE TABLE users (id INT, username TEXT, password TEXT, role TEXT)");
db.run("INSERT INTO users VALUES (1, 'admin', 'SuperAdminSecret!', 'architect')");
db.run("INSERT INTO users VALUES (2, 'john', 'UserPass123', 'reader')");
});
// ⚠️ VULNERABILITY 2: Unparameterized SQL Query (Direct String Concatenation)
app.get('/api/users', (req, res) => {
const username = req.query.username || '';
const query = "SELECT id, username, role FROM users WHERE username = '" + username + "'";
db.all(query, [], (err, rows) => {
if (err) return res.status(500).json({ error: err.message });
res.json(rows);
});
});
app.listen(3001, () => console.log('Store API running on port 3001'));Phase 2
Audit High-Entropy Secrets with Gitleaks
Run Gitleaks locally to detect leaked credentials across your files and git commit history.
Step 2.1: Run Gitleaks
Step 2.2: Inspect Findings
Gitleaks Finding Output
Finding: AKIAIOSFODNN7EXAMPLE_SECRET_KEY_PROD
RuleID: aws-access-token
Secret: AKIAIOSFODNN7EXAMPLE_SECRET_KEY_PROD
File: server.js
Line: 7
Commit: HEAD
Entropy: 4.312910Phase 3
Author Custom AST Rule in Semgrep
Semgrep analyzes code syntax trees (ASTs) rather than raw text regexes. Write a custom Semgrep rule to detect direct SQL string concatenation.
Step 3.1: Define AST Rule
.semgrep/no-unparameterized-sql.yml
rules:
- id: no-unparameterized-sql
languages: [javascript, typescript]
message: "Detected unparameterized SQL query constructed via string concatenation. Use parameterized queries (?, $1) to prevent SQL Injection."
severity: ERROR
metadata:
cwe: "CWE-89: Improper Neutralization of Special Elements used in an SQL Command"
owasp: "A03:2021 - Injection"
patterns:
- pattern-either:
- pattern: $DB.all($QUERY + $INPUT, ...)
- pattern: $DB.run($QUERY + $INPUT, ...)
- pattern: $DB.all(`... ${$INPUT} ...`, ...)
- pattern: $DB.run(`... ${$INPUT} ...`, ...)Step 3.2: Execute Semgrep Scan
Phase 4
Remediate Secrets & Parameterize SQL Queries
Extract secrets into environment variables and refactor the SQLite query to use parameterized query placeholders.
Step 4.1: Refactor server.js
server.js (Secure Refactor)
const express = require('express');
const sqlite3 = require('sqlite3').verbose();
const app = express();
app.use(express.json());
// ✅ SECURE 1: Loaded from Environment Variables with Fallbacks
const AWS_SECRET_KEY = process.env.AWS_SECRET_KEY || '';
const JWT_PRIVATE_SECRET = process.env.JWT_PRIVATE_SECRET || '';
const db = new sqlite3.Database(':memory:');
db.serialize(() => {
db.run("CREATE TABLE users (id INT, username TEXT, password TEXT, role TEXT)");
db.run("INSERT INTO users VALUES (1, 'admin', 'SuperAdminSecret!', 'architect')");
db.run("INSERT INTO users VALUES (2, 'john', 'UserPass123', 'reader')");
});
// ✅ SECURE 2: Parameterized SQL Query with Prepared Statement Placeholder (?)
app.get('/api/users', (req, res) => {
const username = req.query.username || '';
const query = "SELECT id, username, role FROM users WHERE username = ?";
db.all(query, [username], (err, rows) => {
if (err) return res.status(500).json({ error: err.message });
res.json(rows);
});
});
app.listen(3001, () => console.log('Store API running on port 3001'));Step 4.2: Verify Zero Findings
Terminal — Verify Scans
semgrep scan --config .semgrep/
# Output: 0 findings. Ran 1 rule across 1 file.
gitleaks dir --verbose
# Output: No leaks found.Day 2 Key Learnings & Outcomes
- Understood high-entropy credential leakage risks and automated scanning with Gitleaks.
- Discovered why raw regex patterns produce false positives and why AST-based SAST (Semgrep) provides deterministic findings.
- Created a custom Semgrep security rule (.semgrep/no-unparameterized-sql.yml) mapped to CWE-89.
- Refactored server.js to use environment variables and parameterized SQL placeholders, achieving a zero-finding scan.
🧠 Knowledge Check
What makes Abstract Syntax Tree (AST) scanning in Semgrep superior to regular expressions for SQL injection detection?