SQL Injection Prevention Guide: Complete Tutorial

// Bad Example - Don't do this
public User findUser(String username) {
    // Vulnerable to SQL injection
    String sql = "SELECT * FROM users WHERE username = '" + 
        username + "'";
    Statement stmt = connection.createStatement();
    ResultSet rs = stmt.executeQuery(sql);
    return mapResultToUser(rs);
}

// Good Example - Use PreparedStatement
public User findUserSafely(String username) {
    String sql = "SELECT * FROM users WHERE username = ?";
    
    try (PreparedStatement stmt = 
            connection.prepareStatement(sql)) {
        stmt.setString(1, username);
        ResultSet rs = stmt.executeQuery();
        return mapResultToUser(rs);
    }
}

// Batch Operations Example
public void batchInsertUsers(List users) {
    String sql = "INSERT INTO users (username, email) " +
        "VALUES (?, ?)";
    
    try (PreparedStatement stmt = 
            connection.prepareStatement(sql)) {
        for (User user : users) {
            stmt.setString(1, user.getUsername());
            stmt.setString(2, user.getEmail());
            stmt.addBatch();
        }
        stmt.executeBatch();
    }
}

@Repository
public class UserRepository {
    @PersistenceContext
    private EntityManager em;
    
    // Bad Example - Don't do this
    public List findByRoleBad(String role) {
        // Vulnerable to SQL injection
        String jpql = "SELECT u FROM User u WHERE u.role = '" + 
            role + "'";
        return em.createQuery(jpql, User.class)
            .getResultList();
    }
    
    // Good Example - Use Parameter Binding
    public List findByRole(String role) {
        String jpql = "SELECT u FROM User u WHERE u.role = :role";
        return em.createQuery(jpql, User.class)
            .setParameter("role", role)
            .getResultList();
    }
    
    // Using Criteria API (Type-safe)
    public List findByRoleCriteria(String role) {
        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery query = cb.createQuery(User.class);
        Root root = query.from(User.class);
        
        query.where(cb.equal(root.get("role"), role));
        return em.createQuery(query).getResultList();
    }
}

public class SqlInputValidator {
    // Whitelist pattern for simple identifiers
    private static final Pattern IDENTIFIER_PATTERN = 
        Pattern.compile("^[a-zA-Z0-9_]{1,64}$");
    
    // Whitelist pattern for numbers
    private static final Pattern NUMBER_PATTERN = 
        Pattern.compile("^\\d{1,10}$");
    
    public boolean isValidIdentifier(String input) {
        return input != null && 
            IDENTIFIER_PATTERN.matcher(input).matches();
    }
    
    public boolean isValidNumber(String input) {
        return input != null && 
            NUMBER_PATTERN.matcher(input).matches();
    }
    
    // Validate SQL operators
    public boolean isValidOperator(String operator) {
        Set validOperators = 
            Set.of("=", "<", ">", "<=", ">=", "<>");
        return validOperators.contains(operator);
    }
    
    // Validate order by clause
    public boolean isValidOrderBy(String column) {
        Set validColumns = 
            Set.of("id", "username", "email", "created_at");
        return validColumns.contains(column.toLowerCase());
    }
}

@Service
public class UserService {
    private final JdbcTemplate jdbcTemplate;
    private final SqlInputValidator validator;
    
    // Using JdbcTemplate (Spring)
    public List findUsers(String role, int limit) {
        if (!validator.isValidIdentifier(role)) {
            throw new IllegalArgumentException(
                "Invalid role parameter");
        }
        
        String sql = "SELECT * FROM users WHERE role = ? " +
            "LIMIT ?";
        return jdbcTemplate.query(sql,
            new Object[]{role, limit},
            new UserRowMapper());
    }
    
    // Dynamic query building (safe way)
    public List searchUsers(UserSearchCriteria criteria) {
        StringBuilder sql = new StringBuilder(
            "SELECT * FROM users WHERE 1=1");
        List