ORMs Done Right

// 1) Prisma — typed query builder, parameterised by construction
await prisma.user.findMany({
    where: {
        email: { contains: search },                   // safe
        active: true,
    },
    orderBy: { createdAt: 'desc' },
    take: 20,
});

// Raw queries — tagged templates are SAFE
await prisma.$queryRaw`SELECT * FROM users WHERE email = ${email}`;
// → SELECT * FROM users WHERE email = $1

// UNSAFE — $queryRawUnsafe takes a string + values
await prisma.$queryRawUnsafe(`SELECT * FROM users WHERE email = '${email}'`);  // INJECTION
await prisma.$queryRawUnsafe('SELECT * FROM users WHERE email = ?', email);   // SAFE

// 2) Drizzle — similar contract
import { sql, eq } from 'drizzle-orm';
import { users } from './schema';

await db.select().from(users).where(eq(users.email, email));         // safe
await db.execute(sql`SELECT * FROM users WHERE email = ${email}`);  // safe — sql tagged template
// Avoid sql.raw() with user data

// 3) SQLAlchemy (Python)
from sqlalchemy import text, select
from sqlalchemy.orm import Session
from .models import User

with Session(engine) as s:
    # ORM API — safe
    users = s.scalars(select(User).where(User.email == email)).all()

    # Core text() with bound params — safe
    rows = s.execute(
        text('SELECT * FROM users WHERE email = :email'),
        {'email': email},
    ).all()

    # UNSAFE — f-string into SQL
    bad = s.execute(text(f"SELECT * FROM users WHERE email = '{email}'"))

# 4) Eloquent (Laravel)
User::where('email', $email)->first();                 // safe
DB::select('SELECT * FROM users WHERE email = ?', [$email]);   // safe
DB::select("SELECT * FROM users WHERE email = '$email'");      // INJECTION

# 5) ActiveRecord (Rails)
User.where(email: email).first             # safe
User.where('email = ?', email).first       # safe
User.where("email = '#{email}'").first      # INJECTION

# Don't use string interpolation in where clauses; Rails will warn you.

# 6) Entity Framework (C#)
await db.Users.Where(u => u.Email == email).FirstOrDefaultAsync();          // safe
await db.Users.FromSqlInterpolated($"SELECT * FROM Users WHERE Email = {email}")  // safe — interpolated
                                                                                  // becomes parameterised
await db.Users.FromSqlRaw($"SELECT * FROM Users WHERE Email = '{email}'")        // INJECTION
await db.Users.FromSqlRaw("SELECT * FROM Users WHERE Email = {0}", email)         // safe

# 7) Hibernate / JPA (Java)
entityManager.createQuery(
    "FROM User WHERE email = :email", User.class)
    .setParameter("email", email)
    .getSingleResult();                  // safe

// Bad: building HQL with concatenation
// session.createQuery("FROM User WHERE email = '" + email + "'")    // INJECTION

# 8) Dynamic identifiers — even ORMs can't parameterise table/column names
// Always allowlist these — see the allowlist lesson
const SORTS = { id: 'id', created: 'createdAt', total: 'total' };
const sort = SORTS[req.query.sort] ?? 'createdAt';
await prisma.order.findMany({ orderBy: { [sort]: 'desc' } });

# 9) Best practices
#   • Default to the typed query builder — it can't generate SQLi
#   • If you need raw SQL: tagged template or bound parameters, never concatenation
#   • Ban `$queryRawUnsafe` / `FromSqlRaw with interpolation` / `String#format` via lint rules
#   • Restrict DB user permissions (no DROP, no extension installation)
#   • Log + alert on errors with SQL syntax errors (someone might be probing)

# 10) Detect ORM bypasses in code review
# Look for: f-strings, template literals, string concatenation, sprintf with %s where the param is user-controlled
# Look for: raw / FromSqlRaw / queryRawUnsafe / executeRaw / DB::statement / find_by_sql
# Allowed: tagged templates, named params (:foo), positional params (?), bound parameters

// Good
User::where('email', $email)->first();      // Laravel Eloquent — bound
User.objects.filter(email=email).first()    // Django ORM — bound
// Bad — raw query with concatenation
User::query()->whereRaw("email = '$email'")  // STOP — bind values instead