EP 13: SQL Injection Isn't Dead - It Just Evolved

The Myth: "I use an ORM, I'm safe"

Many developers operate under a dangerous assumption: because they use an Object-Relational Mapper (ORM) like Prisma, Drizzle, or Sequelize, SQL Injection (SQLi) is a relic of the past.

While it's true that ORMs automatically parameterize standard find and create operations, they are not a silver bullet. Modern SQLi has simply moved from the "front door" (login forms) to the "side doors" (complex filters, raw queries, and metadata).

The Modern Vulnerability: Where ORMs Fail

In a real-world system, business logic often outgrows the simple abstractions of an ORM. This is where the "Escape Hatches" become dangerous.

1. The "Raw Query" Trap

When performance matters or a query is too complex for the ORM's syntax, developers reach for db.raw() or db.execute().

// DANGEROUS: String interpolation inside a raw query
const users = await prisma.$queryRaw(`SELECT * FROM User WHERE id = ${userInput}`);

If userInput is 1 OR 1=1, an attacker has successfully bypassed your authentication logic.

2. Order By & Group By Injection

Many developers sanitize the WHERE clause but forget the ORDER BY or GROUP BY parameters. Many ORMs treat these as "literal" strings.

• The Attack: An attacker passes (CASE WHEN (SELECT ASCII(SUBSTRING(password,1,1)) FROM users WHERE username='admin')=97 THEN id ELSE username END) into your sorting parameter.
• The Result: This is Blind SQLi. By watching how the list sorts, an attacker can extract your admin password character by character.

3. Second-Order SQLi

This is the "Long Game" of injection. The malicious payload is stored in the database first (e.g., as a user's "Bio") and is only executed later when a different, poorly coded administrative script or background job pulls that data and uses it in a query.

---

The Defense Strategy: Protecting Your Data

1. Prepared Statements (The Gold Standard)

Never use template literals or string concatenation to build queries. Use Parameterized Queries where the data and the command are sent to the database separately.

// SECURE: The '?' is a placeholder, and input is treated as data, not code
const users = await db.execute('SELECT * FROM User WHERE id = ?', [userInput]);

2. Strict Input Whitelisting

If you allow users to choose which column to sort by or which table to query, do not trust their input. Use a "White-List" approach.

const allowedSortFields = ['createdAt', 'username', 'email'];
const sortField = allowedSortFields.includes(userInput) ? userInput : 'createdAt';

// Now use the safe 'sortField' variable

3. The Principle of Least Privilege

In System Design, your "Blast Radius" should be as small as possible. Your web application should connect to the database using a user account that only has permissions to SELECT, INSERT, and UPDATE.

• It should never be logged in as root or db_owner.
• It should not have permission to DROP TABLES or GRANT PERMISSIONS.

Why this is "System" Design:

Security isn't a "feature" you add at the end; it's an architectural choice. By designing your database interactions with Zero-Trust principles, you ensure that even if a developer makes a mistake in one endpoint, the entire database remains safe.

Takeaway

SQL Injection remains one of the top threats on the OWASP Top 10 list for a reason: it's incredibly effective. Don't let the convenience of an ORM lull you into a false sense of security. Always assume that any input coming from the client—whether it’s a search query or a sort parameter—is an attempt to break your system.