How do you prevent injection attacks and the N+1 query problem?

Detailed Answer

Answer: Two of the most common database issues in Node apps — one security, one performance.

1. Injection — use parameterized queries: Never build queries by string interpolation with user input:

// ❌ SQL injection — input like "1; DROP TABLE users" is catastrophic
db.query(`SELECT * FROM users WHERE email = '${email}'`);

// ✅ Parameterized — the driver sends data separately from the query
db.query('SELECT * FROM users WHERE email = $1', [email]);

Parameterization ensures input is treated as data, never executable SQL.
ORMs/query builders parameterize by default — but their raw escape hatches ($queryRawUnsafe, string concatenation) reintroduce the risk.
NoSQL is also vulnerable (NoSQL injection): validate/cast input so an attacker can't pass an object like { "$gt": "" } where you expect a string.

2. The N+1 query problem: Fetching a list, then querying related data per item, produces 1 + N queries:

// ❌ N+1: 1 query for posts, then 1 per post for its author
const posts = await getPosts();               // 1 query
for (const p of posts) {
  p.author = await getUser(p.authorId);       // N queries
}

Fix by loading related data in one go:

// ✅ Single JOIN
SELECT posts.*, users.name FROM posts JOIN users ON users.id = posts.author_id;

// ✅ ORM eager-loading (Prisma)
const posts = await prisma.post.findMany({ include: { author: true } });

// ✅ Batch the lookups
const authors = await getUsersByIds(posts.map(p => p.authorId)); // 1 query

In GraphQL, use DataLoader to batch and cache per-request lookups.

Why it matters: N+1 turns a fast endpoint into hundreds of round-trips under load; injection is a top OWASP risk that can leak or destroy the whole database.

How do you prevent injection attacks and the N+1 query problem?

Quick Answer

Detailed Answer