Fix SQL Injection (Legacy & Modern) in Fresh

SQL Injection (SQLi) remains the primary vector for data exfiltration in modern web stacks. In Deno's Fresh framework, the vulnerability typically manifests in Handlers where user-controlled input from 'ctx.params' or 'req.url' is concatenated directly into database driver queries. While Fresh provides the UI layer, the backend logic often relies on drivers like 'deno_postgres' or 'deno_mysql'. If you are building query strings with template literals, you are handing the keys to your database to anyone with a browser.

The Vulnerable Pattern

// routes/api/user.ts
import { Handlers } from "$fresh/server.ts";
import { client } from "../../utils/db.ts";
export const handler: Handlers = {
async GET(req, ctx) {
const url = new URL(req.url);
const userId = url.searchParams.get(“id”);
// VULNERABLE: Direct string interpolation allows OOB injection
// An attacker could pass ?id=1' OR '1'='1
const query = `SELECT username, email FROM users WHERE id = '${userId}'`;
const result = await client.queryObject(query);

return Response.json(result.rows);
},
};

The Secure Implementation

The fix shifts the responsibility of data escaping from the developer to the database driver. By using the '$1' placeholder (in Postgres) or '?' (in MySQL) and passing the variables in a separate array, the database engine compiles the SQL command structure first and then inserts the parameters. This prevents 'break-out' characters like single quotes or semicolons from altering the logic of the SQL statement. For modern Fresh apps, always leverage an ORM like Drizzle or Prisma, or stick strictly to parameterized queries in your Handlers.

// routes/api/user.ts
import { Handlers } from "$fresh/server.ts";
import { client } from "../../utils/db.ts";
export const handler: Handlers = {
async GET(req, ctx) {
const url = new URL(req.url);
const userId = url.searchParams.get(“id”);
if (!userId) return new Response("Missing ID", { status: 400 });

// SECURE: Use parameterized queries (Prepared Statements)
// The driver handles escaping and ensures userId is treated as data, not code.
const result = await client.queryObject(
  "SELECT username, email FROM users WHERE id = $1",
  [userId]
);

return Response.json(result.rows);
},
};