SQL injection from formatted SQL string in JDBC query

Critical Risk SQL Injection

javajdbcsql-injectionstring-formatting

What it is

SQL injection vulnerability in Java applications where SQL strings are built using String.format() or similar formatting methods with user-controlled values, bypassing prepared statement protections.

Language:

// VULNERABLE: JDBC with String.format() and MessageFormat
import java.sql.*;
import java.text.MessageFormat;
import java.util.*;

public class VulnerableDataService {
    private Connection connection;

    public List<Product> searchProducts(String category, String priceRange,
                                      String sortField, String sortDirection) {
        try {
            // String.format() vulnerability
            String query = String.format(
                "SELECT * FROM products WHERE category = '%s' " +
                "AND price BETWEEN %s " +
                "ORDER BY %s %s",
                category, priceRange, sortField, sortDirection
            );

            Statement stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery(query);
            return processProductResults(rs);

        } catch (SQLException e) {
            throw new RuntimeException("Database error", e);
        }
    }

    // Attack examples:
    // category = "electronics' OR '1'='1"
    // priceRange = "0 AND 999999; DROP TABLE products; --"
    // sortField = "name; DELETE FROM users; --"
}

// SECURE: Parameterized queries with validation
import java.sql.*;
import java.util.*;
import java.security.MessageDigest;

public class SecureDataService {
    private Connection connection;

    // Whitelisted values for security
    private static final Set<String> VALID_CATEGORIES = Set.of(
        "electronics", "clothing", "books", "home", "sports"
    );

    private static final Set<String> VALID_SORT_FIELDS = Set.of(
        "name", "price", "category", "rating", "created_date"
    );

    private static final Set<String> VALID_SORT_DIRECTIONS = Set.of("ASC", "DESC");

    public List<Product> searchProducts(String category, String minPrice, String maxPrice,
                                      String sortField, String sortDirection) {
        try {
            // Validate inputs
            if (!VALID_CATEGORIES.contains(category)) {
                throw new IllegalArgumentException("Invalid category");
            }

            if (!VALID_SORT_FIELDS.contains(sortField)) {
                sortField = "name"; // Default to safe value
            }

            if (!VALID_SORT_DIRECTIONS.contains(sortDirection.toUpperCase())) {
                sortDirection = "ASC"; // Default to safe value
            }

            double minPriceVal = validatePrice(minPrice);
            double maxPriceVal = validatePrice(maxPrice);

            // Use PreparedStatement with parameters
            String query = "SELECT id, name, category, price, rating, created_date " +
                          "FROM products WHERE category = ? AND price BETWEEN ? AND ? " +
                          "ORDER BY " + sortField + " " + sortDirection;

            try (PreparedStatement pstmt = connection.prepareStatement(query)) {
                pstmt.setString(1, category);
                pstmt.setDouble(2, minPriceVal);
                pstmt.setDouble(3, maxPriceVal);

                ResultSet rs = pstmt.executeQuery();
                return processProductResults(rs);
            }

        } catch (SQLException e) {
            throw new RuntimeException("Database error", e);
        }
    }

    private double validatePrice(String price) {
        try {
            double priceVal = Double.parseDouble(price);
            if (priceVal < 0 || priceVal > 999999) {
                throw new IllegalArgumentException("Price out of range");
            }
            return priceVal;
        } catch (NumberFormatException e) {
            throw new IllegalArgumentException("Invalid price format");
        }
    }
}

💡 Why This Fix Works

The vulnerable code uses String.format() and MessageFormat to build SQL queries with user input, allowing injection attacks. The fixed version uses PreparedStatement with parameter binding, comprehensive input validation against whitelists, and proper password hashing.

Why it happens

Using String.format() to build SQL queries with user-controlled values directly embedded in the format string, enabling injection.

Root causes

String.format() with User Input in SQL

Using String.format() to build SQL queries with user-controlled values directly embedded in the format string, enabling injection.

Preview example – JAVA

// VULNERABLE: String.format() with user input
public class UserRepository {
    public List<User> searchUsers(String name, String department, String sortBy) {
        // String.format() vulnerability
        String query = String.format(
            "SELECT * FROM users WHERE name LIKE '%%%s%%' AND department = '%s' ORDER BY %s",
            name, department, sortBy
        );

        try (Statement stmt = connection.createStatement()) {
            ResultSet rs = stmt.executeQuery(query);
            return processResults(rs);
        }
    }
}

MessageFormat with User Data

Using MessageFormat.format() to build dynamic SQL queries with user-controlled values directly inserted into the message pattern.

Preview example – JAVA

// VULNERABLE: MessageFormat with user data
import java.text.MessageFormat;

public class ReportService {
    public List<Report> generateReport(String tableName, String whereClause, String orderBy) {
        // MessageFormat vulnerability
        String pattern = "SELECT * FROM {0} WHERE {1} ORDER BY {2}";
        String query = MessageFormat.format(pattern, tableName, whereClause, orderBy);

        try (Statement stmt = connection.createStatement()) {
            ResultSet rs = stmt.executeQuery(query);
            return processResults(rs);
        }
    }
}

Fixes

Use PreparedStatement Instead of String Formatting

Replace String.format() with PreparedStatement and parameter binding. This ensures SQL structure is separated from data.

View implementation – JAVA

// SECURE: PreparedStatement instead of formatting
public class UserRepository {
    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 PreparedStatement with parameters
        String query = "SELECT * FROM users WHERE name LIKE ? AND department = ? ORDER BY " + sortBy;

        try (PreparedStatement pstmt = connection.prepareStatement(query)) {
            pstmt.setString(1, "%" + name + "%");
            pstmt.setString(2, department);

            ResultSet rs = pstmt.executeQuery();
            return processResults(rs);
        }
    }
}

Use Query Builders for Dynamic Queries

For complex dynamic queries, use SQL query builders or JPA Criteria API that provide safe query construction.

View implementation – JAVA

// SECURE: Query builder pattern
public class QueryBuilder {
    private StringBuilder query;
    private List<Object> parameters;

    public QueryBuilder() {
        this.query = new StringBuilder();
        this.parameters = new ArrayList<>();
    }

    public QueryBuilder select(String... columns) {
        query.append("SELECT ").append(String.join(", ", columns));
        return this;
    }

    public QueryBuilder from(String table) {
        // Validate table name against whitelist
        if (!VALID_TABLES.contains(table)) {
            throw new IllegalArgumentException("Invalid table name");
        }
        query.append(" FROM ").append(table);
        return this;
    }

    public QueryBuilder where(String column, Object value) {
        // Validate column name
        if (!VALID_COLUMNS.contains(column)) {
            throw new IllegalArgumentException("Invalid column name");
        }

        if (parameters.isEmpty()) {
            query.append(" WHERE ");
        } else {
            query.append(" AND ");
        }

        query.append(column).append(" = ?");
        parameters.add(value);
        return this;
    }

    public QueryBuilder orderBy(String column) {
        if (!VALID_COLUMNS.contains(column)) {
            throw new IllegalArgumentException("Invalid column name");
        }
        query.append(" ORDER BY ").append(column);
        return this;
    }

    public PreparedStatement build(Connection connection) throws SQLException {
        PreparedStatement pstmt = connection.prepareStatement(query.toString());
        for (int i = 0; i < parameters.size(); i++) {
            pstmt.setObject(i + 1, parameters.get(i));
        }
        return pstmt;
    }
}

Detect This Vulnerability in Your Code

Sourcery automatically identifies sql injection from formatted sql string in jdbc query and many other security issues in your codebase.

Scan Your Code for Free Explore More Vulnerabilities