SQL injection from formatted SQL strings in JPA EntityManager queries

// SECURE: Parameterized JPA queries with validation
import javax.persistence.*;
import javax.persistence.criteria.*;
import java.util.*;

@Repository
public class ReportRepository {

    @PersistenceContext
    private EntityManager entityManager;

    // Whitelisted values for security
    private static final Set<String> VALID_TABLES = Set.of("users", "products", "orders");
    private static final Map<String, Set<String>> VALID_COLUMNS = Map.of(
        "users", Set.of("id", "name", "email", "role", "created_date"),
        "products", Set.of("id", "name", "price", "category", "stock"),
        "orders", Set.of("id", "user_id", "total", "status", "order_date")
    );
    private static final Set<String> VALID_SORT_FIELDS = Set.of("name", "email", "role", "createdDate");
    private static final Set<String> VALID_SORT_DIRECTIONS = Set.of("ASC", "DESC");

    public List<Object[]> generateCustomReport(String table, List<String> columns,
                                             Map<String, String> filters, String groupBy) {
        // Validate table name
        if (!VALID_TABLES.contains(table)) {
            throw new IllegalArgumentException("Invalid table name");
        }

        // Validate columns
        Set<String> validColumnsForTable = VALID_COLUMNS.get(table);
        if (!validColumnsForTable.containsAll(columns)) {
            throw new IllegalArgumentException("Invalid column names");
        }

        // Validate groupBy if provided
        if (groupBy != null && !validColumnsForTable.contains(groupBy)) {
            throw new IllegalArgumentException("Invalid group by column");
        }

        // Build secure native SQL with parameters
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT ");

        // Safe to append since columns are whitelisted
        sql.append(String.join(", ", columns));
        sql.append(" FROM ").append(table); // Safe since table is whitelisted

        List<Object> parameters = new ArrayList<>();

        // Add parameterized filters
        if (!filters.isEmpty()) {
            sql.append(" WHERE ");
            boolean first = true;
            for (Map.Entry<String, String> filter : filters.entrySet()) {
                String field = filter.getKey();
                String value = filter.getValue();

                // Validate filter field
                if (!validColumnsForTable.contains(field)) {
                    throw new IllegalArgumentException("Invalid filter field: " + field);
                }

                if (!first) sql.append(" AND ");
                sql.append(field).append(" = ?"); // field is whitelisted, value is parameterized
                parameters.add(value);
                first = false;
            }
        }

        // Add grouping (safe since validated)
        if (groupBy != null && !groupBy.isEmpty()) {
            sql.append(" GROUP BY ").append(groupBy);
        }

        Query query = entityManager.createNativeQuery(sql.toString());

        // Set parameters
        for (int i = 0; i < parameters.size(); i++) {
            query.setParameter(i + 1, parameters.get(i));
        }

        return query.getResultList();
    }

    public List<User> searchUsersAdvanced(String nameFilter, String roleFilter,
                                        String sortField, String sortDirection) {
        // Validate sort parameters
        if (sortField != null && !VALID_SORT_FIELDS.contains(sortField)) {
            sortField = "name"; // default
        }
        if (sortDirection != null && !VALID_SORT_DIRECTIONS.contains(sortDirection.toUpperCase())) {
            sortDirection = "ASC"; // default
        }

        StringBuilder jpql = new StringBuilder("SELECT u FROM User u WHERE 1=1");
        List<Object> parameters = new ArrayList<>();
        int paramIndex = 1;

        if (nameFilter != null && !nameFilter.trim().isEmpty()) {
            jpql.append(" AND u.name LIKE ?").append(paramIndex);
            parameters.add("%" + nameFilter + "%");
            paramIndex++;
        }

        if (roleFilter != null && !roleFilter.trim().isEmpty()) {
            jpql.append(" AND u.role = ?").append(paramIndex);
            parameters.add(roleFilter);
            paramIndex++;
        }

        // Safe to append since validated
        if (sortField != null) {
            jpql.append(" ORDER BY u.").append(sortField).append(" ").append(sortDirection);
        }

        TypedQuery<User> query = entityManager.createQuery(jpql.toString(), User.class);

        // Set parameters
        for (int i = 0; i < parameters.size(); i++) {
            query.setParameter(i + 1, parameters.get(i));
        }

        return query.getResultList();
    }

    // Better approach: Use Criteria API for complex dynamic queries
    public List<User> searchUsersCriteria(String nameFilter, String roleFilter,
                                        String sortField, String sortDirection) {
        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        CriteriaQuery<User> query = cb.createQuery(User.class);
        Root<User> user = query.from(User.class);

        List<Predicate> predicates = new ArrayList<>();

        if (nameFilter != null && !nameFilter.trim().isEmpty()) {
            predicates.add(cb.like(user.get("name"), "%" + nameFilter + "%"));
        }

        if (roleFilter != null && !roleFilter.trim().isEmpty()) {
            predicates.add(cb.equal(user.get("role"), roleFilter));
        }

        if (!predicates.isEmpty()) {
            query.where(cb.and(predicates.toArray(new Predicate[0])));
        }

        // Add sorting with validation
        if (VALID_SORT_FIELDS.contains(sortField)) {
            if ("DESC".equalsIgnoreCase(sortDirection)) {
                query.orderBy(cb.desc(user.get(sortField)));
            } else {
                query.orderBy(cb.asc(user.get(sortField)));
            }
        }

        return entityManager.createQuery(query).getResultList();
    }
}