Link Search Menu Expand Document

SQL Injection in Java

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 concatenating id user inputs, an attacker could manipulate the query to disclose other people’s data and even affect the integrity of the database, depending on the used database engine.

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 that has an owner id 123 or if 1 equals 1. The query statement is then terminated 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 of the database technologies available.

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( );

References

CWE - CWE-89: Improper Neutralization of Special Elements used in an SQL Command

OWASP - SQL Injection

OWASP - SQL Injection Prevention Cheat Sheet

Oracle - Java SQL Interface Connection