Use Query Parameterization to Prevent Injection
- November 23, 2020
- 2417 Unique Views
- 2 min read
In the 2017 version of the OWASP Top 10 vulnerabilities, injection appeared at the top of the list as the number one vulnerability that year.
When looking at a typical SQL injection in Java, the parameters of a sequel query are naively concatenated to the static part of the query. The following is an unsafe execution of SQL in Java, which can be used by an attacker to gain more information than otherwise intended:
public void selectExample(String parameter) throws SQLException {
Connection connection = DriverManager.getConnection(DB_URL, USER, PASS);
String query = "SELECT * FROM USERS WHERE lastname = " + parameter;
Statement statement = connection.createStatement();
ResultSet result = statement.executeQuery(query);
printResult(result);
}
If the parameter in this example is something like '' OR 1=1, the result contains every single item in the table. This could be even more problematic if the database supports multiple queries and the parameter would be ''; UPDATE USERS SET lastname=''.
To prevent this in Java, we should parameterize the queries by using a prepared statement. This should be the only way to create database queries. By defining the full SQL code and passing in the parameters to the query later, the code is easier to understand. Most importantly, by distinguishing between the SQL code and the parameter data, the query can’t be hijacked by malicious input.
public void prepStatmentExample(String parameter) throws SQLException {
Connection connection = DriverManager.getConnection(DB_URL, USER, PASS);
String query = "SELECT * FROM USERS WHERE lastname = ?";
PreparedStatement statement = connection.prepareStatement(query);
statement.setString(1, parameter);
System.out.println(statement);
ResultSet result = statement.executeQuery();
printResult(result);
}
In the example above, the input binds to the type String and therefore is part of the query code. This technique prevents the parameter input from interfering with the SQL code.
This was just 1 of 10 Java security best practices. Take a look at the full 10 and the easy printable one-pager available.
Don’t Forget to Share This Post!
Java Champions & Developer Advocate and Software Engineer for Snyk. Passionate about Java, (Pure) Functional Programming, and Cybersecurity. Co-leading the Virtual JUG, NLJUG and DevSecCon community. Brian is also an Oracle Groundbreaker Ambassador and regular international speaker on mostly Java-related conferences.
Comments (2)
Stephan
3 weeks agoHey, you might consider adding these other VoxxedDays events too for 2025 @ https://events.voxxeddays.com #Thanks
Geertjan
3 weeks agoThanks, will do, Stephan!