SQL Injection Prevention Reference

node.jsCopy// ✅ SAFE — Sequelize ORM (parameterized internally)
const user = await User.findOne({
  where: { email: req.body.email }
});

// ✅ SAFE — Sequelize with operators
const { Op } = require('sequelize');
const users = await User.findAll({
  where: {
    name: { [Op.iLike]: `%${req.query.search}%` }
  }
});

// ✅ SAFE — raw replacement (Sequelize.bind)
const results = await sequelize.query(
  'SELECT * FROM users WHERE id = $1',
  { bind: [req.params.id], type: sequelize.QueryTypes.SELECT }
);

node.jsCopy// ✅ SAFE — pg module with parameterized queries
const { Pool } = require('pg');
const pool = new Pool();

// Positional parameters ($1, $2, ...)
const result = await pool.query(
  'SELECT * FROM users WHERE email = $1 AND active = $2',
  [req.body.email, true]
);

// ✅ SAFE — prepared statement (reuse plan)
const name = 'get-user';
const text = 'SELECT * FROM users WHERE id = $1';
const values = [req.params.id];
const result = await pool.query({ name, text, values });

node.jsCopy// ✅ SAFE — Knex query builder
const knex = require('knex')({ client: 'pg', connection });

// Simple where
const user = await knex('users')
  .where({ email: req.body.email })
  .first();

// Dynamic column (allowlist!)
const sortCols = ['name', 'email', 'created_at'];
const sortCol = sortCols.includes(req.query.sort) ? req.query.sort : 'created_at';
const users = await knex('users')
  .orderBy(sortCol, req.query.order === 'desc' ? 'desc' : 'asc')
  .limit(50);

pythonCopyimport psycopg2

conn = psycopg2.connect(dsn)

# ✅ SAFE — %s placeholders (NOT Python f-string %s)
cur = conn.cursor()
cur.execute(
    "SELECT * FROM users WHERE email = %s AND active = %s",
    (email, True)
)

# ✅ SAFE — named placeholders
cur.execute(
    "SELECT * FROM users WHERE id = %(id)s",
    {"id": user_id}
)

# ✅ SAFE —批量insert with execute_values
from psycopg2.extras import execute_values
execute_values(
    cur,
    "INSERT INTO logs (user_id, action) VALUES %s",
    [(uid, action) for uid, action in events]
)

pythonCopyfrom sqlalchemy import create_engine, text

engine = create_engine("postgresql://...")

# ✅ SAFE — SQLAlchemy text() with :param
with engine.connect() as conn:
    result = conn.execute(
        text("SELECT * FROM users WHERE email = :email"),
        {"email": email}
    )

# ✅ SAFE — ORM session query
from sqlalchemy import select
stmt = select(User).where(User.email == email)
user = session.scalars(stmt).first()

# ✅ SAFE — bulk insert
conn.execute(
    text("INSERT INTO events (type, data) VALUES (:type, :data)"),
    [{"type": t, "data": d} for t, d in events]
)

pythonCopy# ✅ SAFE — Django ORM (parameterized internally)
user = User.objects.get(email=request.POST['email'])

# ✅ SAFE — filtered queryset
users = User.objects.filter(
    name__icontains=request.GET['q'],
    is_active=True,
)

# ✅ SAFE — raw SQL with params
from django.db import connection
with connection.cursor() as cur:
    cur.execute(
        "SELECT * FROM users WHERE email = %s",
        [request.POST['email']]
    )
    rows = cur.fetchall()

# ❌ NEVER do this in Django:
# cur.execute("SELECT * FROM users WHERE email = '%s'" % email)

phpCopy<?php
$mysqli = new mysqli('localhost', $user, $pass, 'app');

// ✅ SAFE — prepared statement
$stmt = $mysqli->prepare('SELECT * FROM users WHERE email = ?');
$stmt->bind_param('s', $_POST['email']);  // 's' = string
$stmt->execute();
$result = $stmt->get_result();

// ✅ SAFE — multiple types: s=string, i=integer, d=double, b=blob
$stmt = $mysqli->prepare('SELECT * FROM users WHERE id = ? AND role = ?');
$stmt->bind_param('is', $id, $role);
$stmt->execute();

// ✅ SAFE — INSERT
$stmt = $mysqli->prepare('INSERT INTO logs (user_id, action) VALUES (?, ?)');
$stmt->bind_param('is', $userId, $action);
$stmt->execute();

phpCopy// ✅ SAFE — Eloquent ORM (parameterized internally)
$user = User::where('email', $request->input('email'))->first();

// ✅ SAFE — query builder
$users = DB::table('users')
    ->where('name', 'like', '%' . $request->query('q') . '%')
    ->where('active', true)
    ->get();

// ✅ SAFE — raw with bindings
$users = DB::select(
    'SELECT * FROM users WHERE email = ? AND role = ?',
    [$email, $role]
);

// ✅ SAFE — named bindings
$users = DB::select(
    'SELECT * FROM users WHERE id = :id',
    ['id' => $id]
);

javaCopy// ✅ SAFE — Spring JdbcTemplate (parameterized internally)
@Autowired JdbcTemplate jdbc;

// Simple query
User user = jdbc.queryForObject(
    "SELECT * FROM users WHERE email = ?",
    (rs, rowNum) -> new User(rs.getString("name"), rs.getString("email")),
    email
);

// Multiple params
List<User> users = jdbc.query(
    "SELECT * FROM users WHERE role = ? AND active = ?",
    (rs, rowNum) -> mapUser(rs),
    role, true
);

// ✅ SAFE — NamedParameterJdbcTemplate
@Autowired NamedParameterJdbcTemplate namedJdbc;

Map<String, Object> params = Map.of("email", email, "status", "active");
List<User> users = namedJdbc.query(
    "SELECT * FROM users WHERE email = :email AND status = :status",
    params,
    (rs, rowNum) -> mapUser(rs)
);

javaCopy// ✅ SAFE — Spring Data JPA Repository
public interface UserRepository extends JpaRepository<User, Long> {
    User findByEmail(String email);

    @Query("SELECT u FROM User u WHERE u.name LIKE %:keyword%")
    List<User> searchByName(@Param("keyword") String keyword);
}

// ✅ SAFE — JPA Criteria API
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<User> cq = cb.createQuery(User.class);
Root<User> root = cq.from(User.class);
cq.where(cb.equal(root.get("email"), email));
List<User> users = em.createQuery(cq).getResultList();

// ✅ SAFE — Native query with positional params
@Query(value = "SELECT * FROM users WHERE email = ?1", nativeQuery = true)
User findByEmailNative(String email);

goCopyimport "gorm.io/gorm"

// ✅ SAFE — GORM (parameterized internally)
var user User
db.Where("email = ?", email).First(&user)

// ✅ SAFE — struct conditions
db.Where(&User{Email: email}).First(&user)

// ✅ SAFE — search with LIKE
db.Where("name LIKE ?", "%"+keyword+"%").Find(&users)

// ✅ SAFE — raw SQL with params
var users []User
db.Raw("SELECT * FROM users WHERE role = ? AND active = ?", role, true).Scan(&users)

// ✅ SAFE — create
db.Create(&User{Name: name, Email: email})

goCopyimport "github.com/jmoiron/sqlx"

// ✅ SAFE — sqlx positional params
var user User
err := sqlx.Get(db, &user, "SELECT * FROM users WHERE email = $1", email)

// ✅ SAFE — sqlx named params
err := sqlx.NamedGet(db, &user,
    "SELECT * FROM users WHERE email = :email",
    map[string]any{"email": email},
)

// ✅ SAFE — IN clause with sqlx.In
ids := []int64{1, 2, 3}
query, args, err := sqlx.In("SELECT * FROM users WHERE id IN (?)", ids)
rows, err := db.Queryx(query, args...)