# Use Parameterized Query
# Description
Replaces java.sql.Statement (opens new window) with java.sql.PreparedStatement (opens new window) (aka parameterized queries) if the SQL query is constructed by concatenating string literals with user defined expressions (e.g. variables, method invocations, user input, etc).
Parameterized queries enforce a distinction between the SQL code and the data passed through parameters.
# Benefits
Prevents SQL injections.
# Tags
# Code Changes
# Using Statement execute
Pre
String departmentId = "40 OR '1'='1";
String query = "SELECT first_name FROM employee WHERE department_id ='" + departmentId + "' ORDER BY last_name";
Statement statement = connection.createStatement();
statement.execute(query);
ResultSet resultSet = statement.getResultSet();
Post
String departmentId = "40 OR '1'='1";
String query = "SELECT first_name FROM employee WHERE department_id = ?" + " ORDER BY last_name";
PreparedStatement statement = connection.prepareStatement(query);
statement.setString(1, departmentId);
ResultSet resultSet = statement.executeQuery();
# Using Statement executeQuery
Pre
String departmentId = "40 OR '1'='1";
String query = "SELECT first_name FROM employee WHERE department_id ='" + departmentId + "' ORDER BY last_name";
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(query);
Post
String departmentId = "40 OR '1'='1";
String query = "SELECT first_name FROM employee WHERE department_id = ?" + " ORDER BY last_name";
PreparedStatement statement = connection.prepareStatement(query);
statement.setString(1, departmentId);
ResultSet resultSet = statement.executeQuery();
# Multiple Concatenation Lines
Pre
String departmentId = "40 OR '1'='1";
int id = 10;
String query = "SELECT first_name FROM employee WHERE";
query += " id > '" + id + "'";
query += " AND department_id ='" + departmentId + "'";
query += " ORDER BY last_name";
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(query);
Post
String departmentId = "40 OR '1'='1";
int id = 10;
String query = "SELECT first_name FROM employee WHERE";
query += " id > ?" + "";
query += " AND department_id = ?" + "";
query += " ORDER BY last_name";
PreparedStatement statement = connection.prepareStatement(query);
statement.setInt(1, id);
statement.setString(2, departmentId);
ResultSet resultSet = statement.executeQuery();
Use a Java Refactoring Tool
No license required
You can review this refactoring on your code without a license by installing jSparrow to your Eclipse IDE. Install the plug-in from Eclipse IDE: Eclipse Marketplace.
System-wide Refactoring
Do you want to automate this refactoring (and many more) to your system-wide code? The automatic application of this system-wide refactoring can be unlocked by acquiring your jSparrow license.
# Properties
| Property | Value |
|---|---|
| Rule ID | UseParameterizedQuery |
| First seen in jSparrow version | 3.16.0 |
| Minimum Java version | 1.1 |
| Remediation cost | 20 min |
| Links |