# Use Parameterized Query

# Properties

Property Value
Rule ID UseParameterizedQuery
First seen in jSparrow version 3.16.0
Minimum Java version 1.1
Remediation cost 10 min
Links Prepared Statements (with Parameterized Queries)

# Description

Replaces java.sql.Statement with java.sql.PreparedStatement (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.

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

Automatic Application of This Rule

The automatic application of this rule is supported in the following jSparrow version:

# Tags

1
default
You & jSparrow
default

Hey there! May I help you? 😊