Fix SQL Injection (Legacy & Modern) in Astro

Astro's SSR capabilities and API routes are prime targets for SQLi if you treat user input as trusted. Whether you're using legacy raw drivers or modern ORMs, concatenating strings into queries is an invitation for database exfiltration. Secure your Astro endpoints by moving from raw string interpolation to parameterized queries or type-safe query builders.

The Vulnerable Pattern

// src/pages/api/search.ts
import { db } from './db-client';
export async function GET({ request }) {
const url = new URL(request.url);
const queryParam = url.searchParams.get(‘q’);
// CRITICAL VULNERABILITY: Raw string interpolation
// Payload: ’ UNION SELECT password, null FROM users —
const sql = SELECT title, content FROM posts WHERE title = '${queryParam}';
const results = await db.execute(sql);
return new Response(JSON.stringify(results));
}

The Secure Implementation

The vulnerable example uses template literals to inject user input directly into the SQL string, allowing an attacker to break the query context and execute arbitrary SQL. The secure version leverages Astro DB (powered by Drizzle ORM), which utilizes prepared statements. In a prepared statement, the SQL command and the data are sent to the database separately. The database engine treats the input strictly as data, making it impossible for a payload like `' OR 1=1` to alter the logic of the command. If using legacy drivers like 'pg' or 'mysql2', always use the placeholder syntax (e.g., 'SELECT * FROM users WHERE id = $1') instead of string concatenation.

// src/pages/api/search.ts
import { db, posts, eq } from 'astro:db';
export async function GET({ request }) {
const url = new URL(request.url);
const queryParam = url.searchParams.get(‘q’);
if (!queryParam) return new Response(‘Missing query’, { status: 400 });
// MITIGATION: Using Astro DB (Drizzle-based) with parameterized logic
// This ensures the input is escaped and treated as a literal value
const results = await db.select().from(posts).where(eq(posts.title, queryParam));
return new Response(JSON.stringify(results));
}