SQL injection from user data in manually built SQL string in Spring

Critical Risk SQL Injection

javaspringsql-injectiondatabaseweb

What it is

SQL injection vulnerability in Spring applications where untrusted request data is concatenated into SQL strings instead of using bound parameters, allowing attackers to alter queries and manipulate database operations.

Language:

@RestController
@RequestMapping("/api")
public class UserController {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @GetMapping("/users/{id}")
    public ResponseEntity<User> getUser(@PathVariable String id) {
        // VULNERABLE: Path variable concatenation
        String sql = "SELECT * FROM users WHERE id = " + id;

        try {
            User user = jdbcTemplate.queryForObject(sql, new UserRowMapper());
            return ResponseEntity.ok(user);
        } catch (Exception e) {
            return ResponseEntity.notFound().build();
        }
    }

    @PostMapping("/users/search")
    public List<User> searchUsers(@RequestBody UserSearchRequest request) {
        // VULNERABLE: Dynamic query building
        StringBuilder sql = new StringBuilder("SELECT * FROM users WHERE 1=1");

        if (request.getName() != null && !request.getName().isEmpty()) {
            sql.append(" AND name LIKE '%").append(request.getName()).append("%'");
        }

        if (request.getDepartment() != null) {
            sql.append(" AND department = '").append(request.getDepartment()).append("'");
        }

        if (request.getMinSalary() != null) {
            sql.append(" AND salary >= ").append(request.getMinSalary());
        }

        return jdbcTemplate.query(sql.toString(), new UserRowMapper());
    }

    @GetMapping("/reports")
    public List<Map<String, Object>> getReport(@RequestParam String groupBy,
                                              @RequestParam String orderBy) {
        // VULNERABLE: Direct parameter usage in ORDER BY
        String sql = String.format(
            "SELECT %s, COUNT(*) as count FROM users GROUP BY %s ORDER BY %s",
            groupBy, groupBy, orderBy
        );

        return jdbcTemplate.queryForList(sql);
    }
}

// Malicious inputs:
// id: 1 OR 1=1 --
// name: '; DELETE FROM users WHERE '1'='1
// groupBy: department; DROP TABLE users; --

@RestController
@RequestMapping("/api")
public class UserController {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private UserRepository userRepository;

    private static final Set<String> ALLOWED_GROUP_COLUMNS =
        Set.of("department", "status", "level");
    private static final Set<String> ALLOWED_ORDER_COLUMNS =
        Set.of("department", "status", "level", "count");

    @GetMapping("/users/{id}")
    public ResponseEntity<User> getUser(@PathVariable String id) {
        try {
            // Input validation
            Long userId = Long.parseLong(id);
            if (userId <= 0) {
                return ResponseEntity.badRequest().build();
            }

            // SECURE: Parameterized query
            String sql = "SELECT * FROM users WHERE id = ?";
            User user = jdbcTemplate.queryForObject(sql, new UserRowMapper(), userId);
            return ResponseEntity.ok(user);

        } catch (NumberFormatException e) {
            return ResponseEntity.badRequest().build();
        } catch (EmptyResultDataAccessException e) {
            return ResponseEntity.notFound().build();
        }
    }

    @PostMapping("/users/search")
    public List<User> searchUsers(@RequestBody UserSearchRequest request) {
        // SECURE: Parameterized query with validation
        List<Object> params = new ArrayList<>();
        StringBuilder sql = new StringBuilder("SELECT * FROM users WHERE 1=1");

        if (request.getName() != null && !request.getName().trim().isEmpty()) {
            String name = request.getName().trim();
            if (name.length() > 100) {
                throw new IllegalArgumentException("Name too long");
            }
            sql.append(" AND name LIKE ?");
            params.add("%" + name + "%");
        }

        if (request.getDepartment() != null) {
            // Validate department against allowed values
            Set<String> validDepartments = Set.of("IT", "HR", "Finance", "Marketing");
            if (!validDepartments.contains(request.getDepartment())) {
                throw new IllegalArgumentException("Invalid department");
            }
            sql.append(" AND department = ?");
            params.add(request.getDepartment());
        }

        if (request.getMinSalary() != null) {
            if (request.getMinSalary() < 0) {
                throw new IllegalArgumentException("Salary must be non-negative");
            }
            sql.append(" AND salary >= ?");
            params.add(request.getMinSalary());
        }

        return jdbcTemplate.query(sql.toString(), new UserRowMapper(), params.toArray());
    }

    @GetMapping("/reports")
    public List<Map<String, Object>> getReport(@RequestParam String groupBy,
                                              @RequestParam String orderBy) {
        // Input validation with whitelisting
        if (!ALLOWED_GROUP_COLUMNS.contains(groupBy)) {
            throw new IllegalArgumentException("Invalid groupBy column");
        }
        if (!ALLOWED_ORDER_COLUMNS.contains(orderBy)) {
            throw new IllegalArgumentException("Invalid orderBy column");
        }

        // SECURE: Validated column names (safe since whitelisted)
        String sql = String.format(
            "SELECT %s, COUNT(*) as count FROM users GROUP BY %s ORDER BY %s",
            groupBy, groupBy, orderBy
        );

        return jdbcTemplate.queryForList(sql);
    }

    // Alternative: Repository pattern with JPA
    @GetMapping("/users/by-department")
    public List<User> getUsersByDepartment(@RequestParam String department) {
        // BEST: Use repository with built-in parameterization
        return userRepository.findByDepartment(department);
    }
}

💡 Why This Fix Works

The vulnerable code concatenates user input directly into SQL strings using StringBuilder and String.format(). The fixed version uses parameterized queries with ? placeholders and implements input validation including whitelisting for dynamic column names.

Why it happens

Untrusted request data is concatenated into SQL strings instead of using bound parameters.

Root causes

Request Data Concatenation in Spring Controllers

Untrusted request data is concatenated into SQL strings instead of using bound parameters.

Preview example – JAVA

// VULNERABLE: String concatenation in Spring
@GetMapping("/users")
public List<User> getUsers(@RequestParam String department) {
    String sql = "SELECT * FROM users WHERE department = '" + department + "'";
    return jdbcTemplate.query(sql, new UserRowMapper());
}

Dynamic Query Building Without Parameters

Building dynamic SQL queries by concatenating user input without proper parameterization in Spring Data or JdbcTemplate usage.

Preview example – JAVA

// VULNERABLE: Dynamic query building
@PostMapping("/search")
public List<User> searchUsers(@RequestBody SearchRequest request) {
    StringBuilder sql = new StringBuilder("SELECT * FROM users WHERE 1=1");
    if (request.getName() != null) {
        sql.append(" AND name = '").append(request.getName()).append("'");
    }
    return jdbcTemplate.query(sql.toString(), new UserRowMapper());
}

Fixes

Use Spring JdbcTemplate with Placeholders

Replace string concatenation with parameterized queries. Use JdbcTemplate placeholders (?) and pass values as separate arguments.

View implementation – JAVA

// SECURE: JdbcTemplate with placeholders
@GetMapping("/users")
public List<User> getUsers(@RequestParam String department) {
    String sql = "SELECT * FROM users WHERE department = ?";
    return jdbcTemplate.query(sql, new UserRowMapper(), department);
}

Use PreparedStatement or JPA with Named Parameters

For JPA/Hibernate, use named or positional parameters with setParameter. Validate and type-check inputs; avoid dynamic identifiers.

View implementation – JAVA

// SECURE: JPA with named parameters
@Repository
public class UserRepository {
    @PersistenceContext
    private EntityManager entityManager;

    public List<User> findByDepartment(String department) {
        String jpql = "SELECT u FROM User u WHERE u.department = :dept";
        return entityManager.createQuery(jpql, User.class)
                .setParameter("dept", department)
                .getResultList();
    }
}

Detect This Vulnerability in Your Code

Sourcery automatically identifies sql injection from user data in manually built sql string in spring and many other security issues in your codebase.

Scan Your Code for Free Explore More Vulnerabilities