SQL Injection Prevention

// ❌ VULNERABLE - Never do this!
const userId = req.params.id;
const query = `SELECT * FROM users WHERE id = ${userId}`;
// If userId = "1 OR 1=1" → Returns all users!

// ✅ SAFE - Parameterized query (MySQL)
const mysql = require('mysql2/promise');

async function getUser(userId) {
  const connection = await mysql.createConnection(config);
  
  // Parameterized query with ?
  const [rows] = await connection.execute(
    'SELECT * FROM users WHERE id = ?',
    [userId]
  );
  
  return rows[0];
}

// ✅ SAFE - Named parameters (PostgreSQL with pg)
const { Pool } = require('pg');
const pool = new Pool();

async function getUserPg(userId) {
  const result = await pool.query(
    'SELECT * FROM users WHERE id = $1',
    [userId]
  );
  return result.rows[0];
}

// ✅ SAFE - ORM (Prisma)
const user = await prisma.user.findUnique({
  where: { id: userId }
});

// ✅ SAFE - ORM (Sequelize)
const user = await User.findOne({
  where: { id: userId }
});

// Multiple parameters
async function searchUsers(name, email) {
  const [rows] = await connection.execute(
    'SELECT * FROM users WHERE name LIKE ? AND email = ?',
    [`%${name}%`, email]
  );
  return rows;
}

// ❌ If you MUST escape manually (not recommended)
const mysql = require('mysql');
const escaped = mysql.escape(userInput);
const query = `SELECT * FROM users WHERE name = ${escaped}`;

// ✅ Better: Use escapeId for identifiers (table/column names)
const table = mysql.escapeId(userProvidedTable);
const column = mysql.escapeId(userProvidedColumn);
const query = `SELECT * FROM ${table} WHERE ${column} = ?`;

import psycopg2
import mysql.connector

# ❌ VULNERABLE - Never concatenate!
user_id = request.args.get('id')
query = f"SELECT * FROM users WHERE id = {user_id}"
# SQL Injection risk!

# ✅ SAFE - Parameterized (PostgreSQL)
def get_user_pg(user_id):
    conn = psycopg2.connect(database="mydb")
    cursor = conn.cursor()
    
    cursor.execute(
        "SELECT * FROM users WHERE id = %s",
        (user_id,)
    )
    
    return cursor.fetchone()

# ✅ SAFE - Parameterized (MySQL)
def get_user_mysql(user_id):
    conn = mysql.connector.connect(database="mydb")
    cursor = conn.cursor()
    
    cursor.execute(
        "SELECT * FROM users WHERE id = %s",
        (user_id,)
    )
    
    return cursor.fetchone()

# ✅ SAFE - ORM (SQLAlchemy)
from sqlalchemy import select
user = session.execute(
    select(User).where(User.id == user_id)
).scalar()

# ✅ SAFE - Django ORM
user = User.objects.get(id=user_id)

# Multiple parameters
def search_users(name, email):
    cursor.execute(
        "SELECT * FROM users WHERE name LIKE %s AND email = %s",
        (f'%{name}%', email)
    )
    return cursor.fetchall()