Detect SQL Injection with Parameterized Queries
Replace string concatenation in SQL with parameterized queries so a user input of ' OR 1=1 -- cannot escape the query structure.
Task
Implement db.query('SELECT * FROM users WHERE id = $1', [req.params.id]) to close a known attack surface before the next security review finds it.
When to use this
A penetration test report flags the missing db.query('SELECT * FROM users WHERE id = $1', [req.params.id]) as a medium-severity finding on a public endpoint.
Prerequisites
- A running application with the vulnerable endpoint
- Access to security headers or middleware config
Steps
1. Configure db.query('SELECT * FROM users WHERE id = $1', [req.params.id]) in the project configuration file with the recommended defaults.
db.query('SELECT * FROM users WHERE id = $1', [req.params.id])
Expected output: The configuration is accepted and db.query('SELECT * FROM users WHERE id = $1', [req.params.id]) runs without errors on the first invocation.
2. Run the check locally to verify db.query('SELECT * FROM users WHERE id = $1', [req.params.id]) produces the expected result before pushing.
pnpm test
Expected output: Local run matches CI behavior; no surprises after push.
3. Add the gate to the CI pipeline so db.query('SELECT * FROM users WHERE id = $1', [req.params.id]) blocks merge on failure.
- name: Run add sql injection detection
run: pnpm test
Expected output: Pull requests with failing db.query('SELECT * FROM users WHERE id = $1', [req.params.id]) are blocked from merging.
Troubleshooting
Problem
The check passes locally but fails in CI
Solution
Pin dependency versions and the runtime to match CI exactly; reproduce with the same Docker image or act.
Problem
Configuration is rejected with a schema error
Solution
Check the tool version matches the config schema; older versions may not support newer fields.
Limitations
- The gate only runs on events that trigger the workflow; locally pushed commits bypass it.
- Configuration drift is possible if someone edits settings outside version control.
Close the attack surface
Start by measuring the problem. Replace string concatenation in SQL with parameterized queries so a user input of ’ OR 1=1 – cannot escape the query structure. The most common mistake is adding the gate without first confirming it catches the issue you care about. Run db.query(‘SELECT * FROM users WHERE id = $1’, [req.params.id]) against the current codebase and inspect the output before wiring it into CI.
db.query('SELECT * FROM users WHERE id = $1', [req.params.id])Verify and monitor
Once the gate is in place, make failures actionable. A failing db.query(‘SELECT * FROM users WHERE id = $1’, [req.params.id]) should point to the exact file and line, not a generic error. Pin the tool version in the lockfile so a upstream release does not silently change behavior. Review the gate output in every pull request just like a test result.