Use Query Parameterization to Prevent Injection
- November 23, 2020
- 3189 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)
Tobiloba
3 years agoI see that you save the point of interest as text in the DB but the response gotten from ChatGPT is JSON. Does this mean you convert the response into string using libraries like gson before saving it in the database?
Denis Magda
3 years agoHey, The response is a String object in the JSON format [1]. The repository takes this JSON string as is and stores to the database [2]. Presently, Spring Data auto-generates the CREATE TABLE statement on the startup and sets the "point of interest" column's type to "text" (or "varchar", don't remember). However, it's always possible to ask Spring Data to use the "json" or "jsonb" type for the column if you wish to query the JSON at the database level. Finally, Vaadin displays a list of PointsOfInterests. Those are generated using the org.json library [3]. Let me know if you have other questions. Hope this helps. [1] https://github.com/YugabyteDB-Samples/budget-journey-gpt/blob/main/src/main/java/com/yugabyte/com/TripsAdvisorService.java#L103 [2] https://github.com/YugabyteDB-Samples/budget-journey-gpt/blob/main/src/main/java/com/yugabyte/com/TripsAdvisorService.java#L74 [3] https://github.com/YugabyteDB-Samples/budget-journey-gpt/blob/main/src/main/java/com/yugabyte/com/TripsAdvisorService.java#L114