How to Fix a SQL Injection Vulnerability Without Breaking Production

A search endpoint on Atlas Market built its query with string interpolation. Here's how I proved the vulnerability with a failing test, fixed it with parameterized queries, and shipped without breaking a single caller.

The listing search on Atlas Market worked fine — until you typed a single quote into the box. That one character was enough to prove the whole endpoint was building SQL out of raw user input.

The context

Atlas Market lets anyone browse a live grid of listings. The search box hit an endpoint that filtered by title. The bug was not exotic: the query was assembled with a template literal, so the user controlled part of the SQL string, not just its data.

1// search endpoint — DO NOT ship this2app.get("/api/listings", (req, res) => {3  const q = req.query.q;4  const sql = `SELECT * FROM listings WHERE title LIKE '%${q}%'`;5  db.query(sql).then(rows => res.json(rows));6});

Proving the bug first

Before touching the fix, I wrote a failing test that encoded the exact attack. This does two things: it documents the vulnerability as an executable fact, and it guarantees a regression will be caught if anyone reintroduces the pattern.

1it("rejects SQL control characters in search", async () => {2  const res = await request(app)3    .get("/api/listings")4    .query({ q: "'; DROP TABLE listings;--" });5  expect(res.status).toBe(200);6  await expect(tableExists("listings")).resolves.toBe(true);7});

The fix — parameterized queries

The fix is not to escape or sanitize the input by hand — it is to stop building SQL out of it at all. Parameterized queries send the statement and the values on separate channels, so the driver never treats input as code.

1app.get("/api/listings", (req, res) => {2  const q = `%${req.query.q ?? ''}%`;3  db.query(4    'SELECT * FROM listings WHERE title LIKE $1 LIMIT 50',5    [q],6  ).then(rows => res.json(rows));7});

Note the extra LIMIT 50 — the old endpoint would happily stream the entire table, so remediation was also a chance to cap the blast radius of a scraping attempt.

The impact

• The failing test went green; the DROP TABLE payload became inert data.
• A repo-wide grep for template-literal SQL surfaced two more endpoints, both fixed the same way.
• Callers were untouched — same route, same response shape, zero breaking changes.
• Added a lint rule to flag raw query strings so the pattern cannot come back quietly.

A bug is not fixed until a test would fail without the fix.

What I would do next

Parameterization closes injection, but defense in depth means going further: least-privilege database roles so the app user cannot DROP anything, query timeouts, and centralized logging on the search path to catch probing early.

If you're auditing your own stack, another easy win in the same spirit is dropping mandatory password rotation — see why the 90-day rotation policy needs to die. And if you'd rather have someone else run this kind of audit against your app, that's what I do for clients.