SQL injection from concatenated or formatted SQL strings in JdbcTemplate

Critical Risk SQL Injection

javaspringjdbctemplatesql-injectionspring-boot

What it is

SQL injection vulnerability in Java Spring applications using JdbcTemplate where SQL strings are built by concatenating or formatting user-controlled data instead of using parameterized queries, compromising database integrity and confidentiality.

Language:

// VULNERABLE: Spring JdbcTemplate with string concatenation
@Service
public class UserService {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public List<Map<String, Object>> searchUsersWithFilters(Map<String, String> filters) {
        StringBuilder sql = new StringBuilder("SELECT * FROM users WHERE 1=1");

        // Multiple concatenation vulnerabilities
        for (Map.Entry<String, String> filter : filters.entrySet()) {
            String column = filter.getKey();
            String value = filter.getValue();

            if ("name".equals(column)) {
                sql.append(" AND name LIKE '%").append(value).append("%'");
            } else {
                // Generic filter - highly vulnerable
                sql.append(" AND ").append(column).append(" = '").append(value).append("'");
            }
        }

        String finalQuery = sql.toString();
        System.out.println("Executing: " + finalQuery);

        return jdbcTemplate.queryForList(finalQuery);
    }
}

// SECURE: Parameterized JdbcTemplate queries with validation
@Service
public class UserService {

    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    // Whitelisted values for security
    private static final Set<String> VALID_SEARCH_COLUMNS = Set.of(
        "name", "email", "department", "age", "status"
    );

    public List<Map<String, Object>> searchUsersWithFilters(Map<String, String> filters) {
        StringBuilder sql = new StringBuilder("SELECT id, name, email, department, age, status FROM users WHERE 1=1");
        Map<String, Object> params = new HashMap<>();

        // Process filters with validation
        for (Map.Entry<String, String> filter : filters.entrySet()) {
            String column = filter.getKey();
            String value = filter.getValue();

            // Validate column name
            if (!VALID_SEARCH_COLUMNS.contains(column)) {
                throw new IllegalArgumentException("Invalid filter column: " + column);
            }

            // Validate value
            if (value == null || value.trim().isEmpty()) {
                continue;
            }

            switch (column) {
                case "name":
                    sql.append(" AND name LIKE :name");
                    params.put("name", "%" + value + "%");
                    break;
                case "age":
                    try {
                        int age = Integer.parseInt(value);
                        if (age < 0 || age > 150) {
                            throw new IllegalArgumentException("Invalid age range");
                        }
                        sql.append(" AND age = :age");
                        params.put("age", age);
                    } catch (NumberFormatException e) {
                        throw new IllegalArgumentException("Invalid age format");
                    }
                    break;
                default:
                    sql.append(" AND ").append(column).append(" = :").append(column);
                    params.put(column, value);
                    break;
            }
        }

        sql.append(" LIMIT 1000"); // Prevent excessive results

        return namedParameterJdbcTemplate.queryForList(sql.toString(), params);
    }
}

💡 Why This Fix Works

The vulnerable code uses string concatenation to build JdbcTemplate queries, allowing injection attacks. The fixed version uses NamedParameterJdbcTemplate with named parameters, comprehensive input validation with Bean Validation, whitelisting for dynamic components, and proper error handling.

Why it happens

SQL strings are built by concatenating user-controlled data and passed to JdbcTemplate methods without parameters, enabling injection.

Root causes

String Concatenation in JdbcTemplate Queries

SQL strings are built by concatenating user-controlled data and passed to JdbcTemplate methods without parameters, enabling injection.

Preview example – JAVA

// VULNERABLE: String concatenation in JdbcTemplate
@Repository
public class UserRepository {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public List<User> searchUsers(String name, String department, String sortBy) {
        // String concatenation vulnerability
        String sql = "SELECT * FROM users WHERE name LIKE '%" + name + "%' " +
                    "AND department = '" + department + "' " +
                    "ORDER BY " + sortBy;

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

Fixes

Use JdbcTemplate with Named Parameters

Use NamedParameterJdbcTemplate with named parameters (:paramName) instead of string concatenation. This provides better readability and security.

View implementation – JAVA

// SECURE: NamedParameterJdbcTemplate with parameters
@Repository
public class UserRepository {

    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    private static final Set<String> VALID_SORT_FIELDS = Set.of(
        "name", "email", "department", "created_date"
    );

    public List<User> searchUsers(String name, String department, String sortBy) {
        // Validate sort field against whitelist
        if (!VALID_SORT_FIELDS.contains(sortBy)) {
            sortBy = "name";
        }

        // Use named parameters
        String sql = "SELECT * FROM users WHERE name LIKE :name " +
                    "AND department = :department " +
                    "ORDER BY " + sortBy; // Safe since validated

        Map<String, Object> params = new HashMap<>();
        params.put("name", "%" + name + "%");
        params.put("department", department);

        return namedParameterJdbcTemplate.query(sql, params, new UserRowMapper());
    }
}

Detect This Vulnerability in Your Code

Sourcery automatically identifies sql injection from concatenated or formatted sql strings in jdbctemplate and many other security issues in your codebase.

Scan Your Code for Free Explore More Vulnerabilities