SQL injection vulnerability in Spring Framework applications using JdbcTemplate where user input is directly concatenated into SQL queries without proper parameterization.
@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);
}
}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.
Using string concatenation or StringBuilder to build SQL queries with user input when using Spring's JdbcTemplate.
Sourcery automatically identifies spring framework sql injection and many other security issues in your codebase.