CWE-89: SQL Injection - Java

Overview

SQL Injection occurs when an application incorporates untrusted data into a SQL query without proper validation or parameterization. Attackers can manipulate queries to bypass authentication, access unauthorized data, modify or delete records, or execute administrative operations.

Primary Defence: Use PreparedStatement with parameterized queries (JDBC), JPA/Hibernate named parameters, or query builder frameworks that automatically parameterize values.

Common Vulnerable Patterns

String Concatenation

String userId = request.getParameter("id");
String query = "SELECT * FROM users WHERE id = " + userId;
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery(query); // VULNERABLE

Why this is vulnerable:

• String concatenation incorporates user input directly into SQL queries without escaping, allowing attackers to inject SQL code (e.g., 1 OR 1=1, 1; DROP TABLE users--) that bypasses authentication or executes unauthorized database operations.

String.format()

String username = request.getParameter("username");
String query = String.format("SELECT * FROM users WHERE username = '%s'", username);
stmt.executeQuery(query); // VULNERABLE

Why this is vulnerable:

• String formatting embeds user input directly into the SQL string.
• Quotes and SQL operators can break out of the intended literal.
• Attackers can inject additional predicates or statements.

Secure Patterns

PreparedStatement (JDBC)

String userId = request.getParameter("id");
String query = "SELECT * FROM users WHERE id = ?";
PreparedStatement pstmt = connection.prepareStatement(query);
pstmt.setString(1, userId); // Parameterized value
ResultSet rs = pstmt.executeQuery(); // SAFE

Why this works:

• PreparedStatement sends the query structure and parameter values separately to the database.
• The database treats parameter values as pure data, not executable SQL, preventing injection regardless of special characters in the input.

Named Parameters (JPA/Hibernate)

String username = request.getParameter("username");
String jpql = "SELECT u FROM User u WHERE u.username = :username";
TypedQuery<User> query = entityManager.createQuery(jpql, User.class);
query.setParameter("username", username); // SAFE
List<User> users = query.getResultList();

Why this works:

• JPA named parameters (:username) are placeholders that get replaced with parameterized values by the persistence provider.
• The framework ensures the value is sent as a parameter, not concatenated into the JPQL/SQL.

Criteria API (Type-Safe)

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<User> cq = cb.createQuery(User.class);
Root<User> user = cq.from(User.class);
cq.select(user).where(cb.equal(user.get("username"), username)); // SAFE
List<User> users = entityManager.createQuery(cq).getResultList();

Why this works:

• The Criteria API uses a type-safe, object-oriented approach to query building.
• The cb.equal() method automatically creates a parameterized condition, ensuring the username value is treated as data, not SQL code.

Framework-Specific Guidance

Spring Data JPA

// Repository interface - completely safe
public interface UserRepository extends JpaRepository<User, Long> {
    // Method name query - SAFE
    User findByUsername(String username);

    // @Query with named parameters - SAFE
    @Query("SELECT u FROM User u WHERE u.email = :email")
    User findByEmail(@Param("email") String email);
}

Why this works:

• Spring Data JPA translates method names like findByUsername into parameterized queries automatically.
• The @Query annotation with :email creates named parameters.
• Both approaches ensure values are sent as parameters, not embedded in SQL.

MyBatis

<!-- XML mapper with parameterized query - SAFE -->
<select id="getUserById" parameterType="string" resultType="User">
    SELECT * FROM users WHERE id = #{userId}
</select>

// Java usage - SAFE
User user = sqlSession.selectOne("getUserById", userId);

Why this works:

• MyBatis uses #{userId} as a parameterized placeholder in XML mappers.
• The framework automatically creates a PreparedStatement and binds the userId parameter, preventing SQL injection through automatic parameterization.

Data Type Handling

Numeric Parameters

String idParam = request.getParameter("id");
try {
    int id = Integer.parseInt(idParam); // Validate type first
    String query = "SELECT * FROM users WHERE id = ?";
    PreparedStatement pstmt = connection.prepareStatement(query);
    pstmt.setInt(1, id); // Type-safe
    // ...
} catch (NumberFormatException e) {
    // Handle invalid input
}

IN Clauses (Multiple Values)

// Vulnerable - don't build dynamic IN clause
String ids = request.getParameter("ids"); // "1,2,3"
String query = "SELECT * FROM users WHERE id IN (" + ids + ")"; // VULNERABLE

Recommended: Spring Data JPA (Simplest)

List<Integer> userIds = Arrays.asList(1, 2, 3, 4, 5);

// Spring Data JPA automatically handles IN clauses - SAFE
List<User> users = userRepository.findByIdIn(userIds);

// Or with @Query annotation - SAFE
@Query("SELECT u FROM User u WHERE u.id IN :ids")
List<User> findByIds(@Param("ids") List<Integer> ids);

Why this works:

• Spring Data JPA recognizes collection-type parameters and automatically generates parameterized IN clauses.
• Each value in the list becomes a separate parameter (@p0, @p1, etc.), maintaining injection protection for multiple values.

Recommended: JPA/Hibernate with Named Parameters (Simple)

List<Integer> userIds = Arrays.asList(1, 2, 3, 4, 5);

// JPA automatically expands collections - SAFE
String jpql = "SELECT u FROM User u WHERE u.id IN :userIds";
TypedQuery<User> query = entityManager.createQuery(jpql, User.class);
query.setParameter("userIds", userIds);
List<User> users = query.getResultList();

// Generated SQL: SELECT * FROM users WHERE id IN (?, ?, ?, ?, ?)

Why this works:

• JPA automatically expands collection parameters into multiple placeholders.
• When you pass a List to setParameter(), JPA creates individual parameterized values for each element, generating safe SQL with proper parameter binding.

JDBC PreparedStatement (More Manual)

// Build placeholders dynamically, but set each parameter safely
List<Integer> ids = parseIds(request.getParameter("ids"));
String placeholders = String.join(",", Collections.nCopies(ids.size(), "?"));
String query = "SELECT * FROM users WHERE id IN (" + placeholders + ")";
PreparedStatement pstmt = connection.prepareStatement(query);
for (int i = 0; i < ids.size(); i++) {
    pstmt.setInt(i + 1, ids.get(i));
}
ResultSet rs = pstmt.executeQuery(); // SAFE - each value is parameterized

Why this works:

• While the placeholder string is concatenated (e.g., "?, ?, ?"), the actual values are bound using setInt(), making each one a proper parameter.
• Only the query structure is dynamic, not the data, preventing injection.

Migration Strategy

1. Identify all SQL construction: Search for Statement, createQuery, string concatenation with SQL keywords
2. Replace with PreparedStatement: Change Statement to PreparedStatement
3. Parameterize inputs: Replace concatenated values with ? placeholders
4. Set parameters: Use setString(), setInt(), etc. for each placeholder
5. Test thoroughly: Verify functionality and try injection payloads

Database Permission Hardening (Defense in Depth)

Even with parameterized queries, restrict database permissions to limit SQL injection impact:

MySQL/MariaDB Permission Examples

Create restricted application user:

-- Create user with limited permissions

CREATE USER 'app_user'@'app_server_ip' IDENTIFIED BY 'strong_password';

-- Grant specific table access only

GRANT SELECT, INSERT, UPDATE ON myapp.users TO 'app_user'@'app_server_ip';
GRANT SELECT, INSERT, UPDATE ON myapp.orders TO 'app_user'@'app_server_ip';
GRANT SELECT ON myapp.products TO 'app_user'@'app_server_ip';  -- Read-only

-- Explicitly deny dangerous operations

REVOKE FILE, PROCESS, SUPER, SHUTDOWN, CREATE USER ON *.* FROM 'app_user'@'app_server_ip';
REVOKE DROP, CREATE, ALTER, TRUNCATE ON myapp.* FROM 'app_user'@'app_server_ip';

-- Flush privileges

FLUSH PRIVILEGES;

What this prevents in SQLi attacks:

• '; DROP TABLE users; -- - No DROP permission
• ' UNION SELECT * FROM admin_users -- - No access to admin tables
• '; LOAD DATA INFILE '/etc/passwd' -- - No FILE permission
• '; EXEC xp_cmdshell 'rm -rf /' -- - No system command execution

Still possible (use prepared statements!):

• ✅ Data exfiltration from permitted tables
• ✅ Unauthorized data modification within granted permissions

Key principle: Least privilege reduces blast radius but does NOT eliminate SQL injection risk. Always use prepared statements.

Java connection string with restricted user:

String url = "jdbc:mysql://localhost:3306/myapp";
String username = "app_user";  // Restricted user, not root!
String password = System.getenv("DB_PASSWORD");

Connection conn = DriverManager.getConnection(url, username, password);

Verification

After implementing the recommended secure patterns, verify the fix through multiple approaches:

• Manual testing: Submit malicious payloads relevant to this vulnerability and confirm they're handled safely without executing unintended operations
• Code review: Confirm all instances use the secure pattern (parameterized queries, safe APIs, proper encoding) with no string concatenation or unsafe operations
• Static analysis: Use security scanners to verify no new vulnerabilities exist and the original finding is resolved
• Regression testing: Ensure legitimate user inputs and application workflows continue to function correctly
• Edge case validation: Test with special characters, boundary conditions, and unusual inputs to verify proper handling
• Framework verification: If using a framework or library, confirm the recommended APIs are used correctly according to documentation
• Authentication/session testing: Verify security controls remain effective and cannot be bypassed (if applicable to the vulnerability type)
• Rescan: Run the security scanner again to confirm the finding is resolved and no new issues were introduced

Remediation Steps

Locate the Finding

Review the data flow in the security scan report:

• Source: Where user input enters (request.getParameter(), HttpServletRequest, @PathVariable, @RequestParam, @RequestBody)
• Sink: The SQL execution call (executeQuery(), executeUpdate(), createQuery(), createNativeQuery(), selectOne())
• Note any frames between source and sink

Understand the Data Flow

Trace how data moves from source to sink:

• Identify if any validation or sanitization exists
• Look for string concatenation or String.format() in SQL queries
• Check if PreparedStatement or parameterized queries are used
• Verify if JPA/Hibernate query methods are used with parameters

Identify the Pattern

Match the code to vulnerable patterns:

• String concatenation → Pattern: "SELECT * FROM users WHERE id = " + userId
• String.format() → Pattern: String.format("SELECT * FROM users WHERE name = '%s'", name)
• Statement instead of PreparedStatement → Pattern: Statement stmt = connection.createStatement()
• MyBatis ${} substitution → Pattern: SELECT * FROM users WHERE email = '${email}'

Apply the Fix

Choose the appropriate remediation approach based on your architecture:

Option 1: Use Spring Data JPA repository methods (Recommended - automatic parameterization)

• Define repository interfaces extending JpaRepository
• Use method name queries like findByEmail(), findByUsername()
• Use @Query annotation with named parameters (:paramName)
• Spring Data automatically generates parameterized queries from method signatures

Option 2: Use JPQL or Criteria API with parameters (Type-safe JPA approach)

• Use named parameters with :parameterName syntax in JPQL
• Call setParameter() to bind values safely
• Use Criteria API for compile-time type safety
• Avoid string concatenation in JPQL/HQL queries

Option 3: Use JDBC PreparedStatement (Direct JDBC access)

• Replace Statement with PreparedStatement
• Use ? placeholders for all user-controlled values
• Call setString(), setInt(), etc. for each placeholder
• Avoid concatenating user input into SQL strings

Option 4: For dynamic table/column names, use allowlist validation (Special cases)

• Parameterized queries cannot be used for identifiers (table/column names)
• Create allowlist of valid table/column names
• Validate user input against allowlist before using in query
• Consider redesigning to avoid user-controlled identifiers

See the Secure Patterns section for detailed implementation examples of each approach.

Verify the Fix

Test and confirm:

• Test with malicious inputs: ' OR '1'='1, admin'--, 1'; DROP TABLE users--
• Run JUnit tests to verify parameterization
• Enable Hibernate SQL logging to inspect generated queries
• Rescan with the security scanner to confirm finding is resolved
• Verify business functionality still works

Check for Similar Issues

Search for related vulnerabilities:

• Search codebase for: Statement, createQuery, createNativeQuery, string concatenation with SQL keywords
• Review other controllers/services that accept user input and execute SQL
• Check for similar patterns in the same module or related functionality
• Look for MyBatis mappers using ${} instead of #{}

Additional Resources

• CWE-89: SQL Injection
• Hibernate Query Language (HQL)
• JDBC PreparedStatement Documentation
• JPA Query Documentation
• MyBatis Dynamic SQL
• OWASP SQL Injection Prevention Cheat Sheet
• Spring Data JPA Reference