SQL Injection
Passing the SQL operation as a string in the execute method presents the risk of a SQL injection, as malicious SQL code can be injected into the query string. Assume that there is a basic statement that queries a table, where the query is dependent on a value:
ResultSet resultSet = statement.executeQuery(
“select * from compositions where title like ‘” + value + “‘”);
A user may try to exploit this code by submitting a malicious value, such as the following:
String value = “SomeValue’; drop table compositions;”;
The above query would be equivalent to the following statement, where the SELECT operation would be executed, followed by dropping the compositions table:
ResultSet resultSet2 = statement.executeQuery(
“select * from compositions where title like ‘” +
“SomeValue’; drop table compositions;”);
To address this issue, one can use either a PreparedStatement object or the Statement.enquoteLiteral() method to sanitize the parameter value.
Prepared Statement
In contrast to the Statement object, which defines the SQL operation as a String parameter to the execute method, a PreparedStatement defines a SQL operation immediately at the point when the prepared statement is created with the prepare-Statement() method. In other words, the SQL operation string is passed as an argument in the prepareStatement() method call, as shown at (1). The prepared statement executes the precompiled SQL operation at (2) by calling the execute() method.
String sql = “select * from compositions where duration > 200”; // SQL operation
try (Connection connection
= DriverManager.getConnection(jdbcURL); // Get connection
PreparedStatement pStatement
= connection.prepareStatement(sql)) { // (1) Prepare statement
boolean result = pStatement.execute(); // (2) Execute
System.out.println(result);
} catch (SQLException e) {
e.printStackTrace();
}
Substitutional Parameterization
Consider the following code:
String sql = “select * from compositions where duration > ?”; // (1) SQL operation
// with 1 marker parameter.
PreparedStatement pStatement
= connection.prepareStatement(sql); // (2) A prepared statement
The SQL operation at (1) now contains a marker parameter (also known as bind parameter or bind variable). Prepared statements use positional notation where each question mark (?) indicates the position of a marker parameter starting with 1 from left to right. This could be any SQL operation, such as an INSERT, UPDATE, or DELETE statement with appropriate marker parameters. The SQL operation with marker parameters is passed as an argument to the prepareStatement() method to precom-pile this SQL operation into a prepared statement, as shown at (2).
However, before a prepared statement can be executed, all its parameters must be set—that is, all marker parameters must be substituted with a value. One can use the set methods of the PreparedStatement interface that are provided for all standard JDBC types to set the value of a marker parameter (see below).
pStatement.setInt(1,200); // Sets the value of the marker parameter
// at position 1 to 200.
Alternatively, the method setObject() can be used to supply a value and explicitly indicate the desired SQL type:
pStatement.setObject(1, 200, Types.INTEGER);
There is no requirement that parameters should be set in any specific order, as long as all parameters are set before executing the statement. Note that the position counter in JDBC is not 0-based as in the rest of the Java language, but starts at 1, which is typical in the database world. The driver converts the value of the Java data type in the method call to the corresponding value of the SQL data type. All parameter-setting methods throw a SQLException if the parameter index does not correspond to a marker parameter, or if it is called on a prepared statement that is already closed.
The PreparedStatement interface is a subinterface of the Statement interface. Once a prepared statement has been obtained with its precompiled SQL operation and values for its marker parameters have been supplied, the execute methods of the PreparedStatement interface can be used to execute the SQL operation. These execute methods are analogous to the corresponding methods in the Statement interface. Note that since the SQL operation to execute is already precompiled with the prepared statement, the execute methods of the PreparedStatement interface do not take any parameter. Specifying an argument will throw a SQLException, as will any marker parameter that is not substituted with a value.
boolean execute()
ResultSet executeQuery()
int executeUpdate()
