Preventing SQL Injection: A Developer's Security Guide

SQL Injection Prevention

SQL injection remains one of the most dangerous and common web vulnerabilities.

What is SQL Injection?

Attackers insert malicious SQL through user input:

// Vulnerable code
const query = SELECT  FROM users WHERE id = ${userId};
// Attacker input: "1; DROP TABLE users; --"
// Resulting query:
// SELECT  FROM users WHERE id = 1; DROP TABLE users; --

Prevention: Parameterized Queries

Node.js (pg):

// ❌ WRONG: String concatenation
const query = SELECT  FROM users WHERE email = '${email}';
// ✅ CORRECT: Parameterized query
const query = 'SELECT  FROM users WHERE email = $1';
const result = await client.query(query, [email]);

Python (psycopg2):

# ❌ WRONG
cursor.execute(f"SELECT  FROM users WHERE email = '{email}'")
# ✅ CORRECT
cursor.execute("SELECT  FROM users WHERE email = %s", (email,))

ORMs and Query Builders

// Prisma (safe by default)
const user = await prisma.user.findUnique({
  where: { email: userInput }
});
// Knex.js
const users = await knex('users')
  .where('email', userInput)
  .select('');

// Be careful with raw queries!
// ❌ Still vulnerable
knex.raw(SELECT  FROM users WHERE email = '${email}');
// ✅ Safe raw query
knex.raw('SELECT * FROM users WHERE email = ?', [email]);

Additional Protections

• Least privilege: DB user should only have needed permissions
• Input validation: Validate data types and formats
• WAF: Web Application Firewall as additional layer
• Error handling: Don't expose SQL errors to users