SQL Injection & Safe Queries¶

SQL injection is decades old and still a top vulnerability. It happens when user input is concatenated into a query string.

The Classic Hole¶

1// ❌ NEVER do this
2const q = `SELECT * FROM users WHERE email = '${email}'`;
3db.query(q);

A user enters as their email:

The query becomes ... WHERE email = '' OR '1'='1' — which matches every row. Worse inputs can drop tables or read other users' data.

The Fix: Parameterized Queries¶

Never build SQL by string concatenation. Use placeholders and pass values separately:

1// ✅ parameterized
2db.query("SELECT * FROM users WHERE email = $1", [email]);

The driver sends the query and the data on separate channels. Input is treated as a value, never as executable SQL.

ORMs Parameterize for You¶

1await prisma.user.findMany({ where: { email } }); // safe by construction

This is a major reason to use a query builder or ORM: parameterization is the default.

The Danger Zone: Raw Queries¶

When you must drop to raw SQL, use the tagged-template / parameter form — not string interpolation:

1// ✅ Prisma tagged template — values are parameterized
2await prisma.$queryRaw`SELECT * FROM users WHERE email = ${email}`;
3
4// ❌ $queryRawUnsafe with concatenation — back to square one
5await prisma.$queryRawUnsafe(`SELECT * FROM users WHERE email = '${email}'`);

You Can't Parameterize Identifiers¶

Placeholders work for values, not column or table names. If you must build a dynamic ORDER BY column, whitelist allowed columns:

1const ALLOWED = { createdAt: "created_at", title: "title" } as const;
2const col = ALLOWED[sortKey] ?? "created_at"; // reject anything else

This connects directly to safe sorting in Chapter 8.