SQL Injection in Java
Play Java Labs on this vulnerability with SecureFlag!
Vulnerable example
The following snippet contains a Java application that runs an SQL query to resolve the host supplied by the user.
String query = "SELECT account_number, account_balance FROM customer_data WHERE account_owner_id = " + request.getParameter("id");
try {
Statement statement = connection.createStatement( ... );
ResultSet res = statement.executeQuery( query );
}
Since the SQL query is built by concatenating id user inputs, an attacker could manipulate it to disclose other people’s data and even affect the integrity of the database, depending on the database engine used.
For example, by injecting 123 OR 1=1;-- in the id field, the SQL query becomes:
SELECT account_number, account_balance FROM customer_data WHERE account_owner_id = 123 OR 1=1;--
The manipulated query returns any entry in the customer_data table with an owner ID of 123 or if 1 equals 1. The query statement is then terminated by commenting out any trailing code using ;--. Since the WHERE statement is always true, the query returns all of the orders of all of the customers.
Prevention
Java provides the PreparedStatement method of the Connection object database to perform parameterized queries on most available database technologies.
String custid = request.getParameter("id");
String query = "SELECT account_number, account_balance FROM customer_data WHERE account_owner_id = ?"
PreparedStatement pstmt = connection.prepareStatement( query );
pstmt.setString( 1, custid);
ResultSet results = pstmt.executeQuery( );
Spring Data JPA
Spring Data JPA’s @Query annotation allows for secure SQL execution with named parameters, preventing SQL injection. It enables custom, complex queries on repository methods, enhancing both application security and data access flexibility in a concise, developer-friendly manner.
@Query("SELECT p FROM Product p WHERE p.category = :category")
List<Product> findByCategory(@Param("category") String category);
Spring Data JDBC
With Spring Data JDBC, you can use JdbcTemplate to execute queries. It handles resource management, executes SQL queries, and maps results to objects. It also supports parameterized queries, helping prevent SQL injection and ensuring secure database operations.
String firstName = this.jdbcTemplate.queryForObject(
"SELECT first_name FROM users WHERE id = ?",
String.class, 8);
References
CWE - CWE-89: Improper Neutralization of Special Elements used in an SQL Command