Java Reference
In-Depth Information
In this last example, a
PreparedStatement
is used to delete a record from the
database. Again, assume that the
recipeNumber
string is passed to this code as a
variable.
String sql = "DELETE FROM RECIPES WHERE " +
"RECIPE_NUMBER = ?";
try(PreparedStatement pstmt
= conn.prepareStatement(sql);) {
pstmt.setString(1, recipeNumber);
pstmt.executeUpdate();
System.out.println("Recipe " + recipeNumber + "
successfully deleted.");
} catch (SQLException ex){
ex.printStackTrace();
}
As you can see, a
PreparedStatement
is very much the same as a standard
JDBC statement object, but instead it sends precompiled SQL to the DBMS rather than
strings of text.
How It Works
While standard JDBC statements will get the job done, the harsh reality is that they can
sometimes be insecure and cumbersome to work with. For instance, bad things can oc-
cur if a dynamic SQL statement is used to query a database, and a user-accepted string
is assigned to a variable and concatenated with the intended SQL string. In most ordin-
ary cases, the user-accepted string would be concatenated, and the SQL string would be
used to query the database as expected. However, an attacker could decide to place ma-
licious code inside of the string (a.k.a. SQL Injection), which would then be inadvert-
ently sent to the database using a standard
Statement
object. The use of
Pre-
paredStatement
s prevents such malicious strings from being concatenated into a
SQL string and passed to the DBMS because they use a different approach.
Pre-
paredStatement
s use substitution variables rather than concatenation to make
SQL strings dynamic. They are also precompiled, which means that a valid SQL string
is formed prior to the SQL being sent to the DBMS. Moreover,
PreparedState-
ment
s can help your application perform better because if the same SQL has to be run
more than one time, it has to be compiled only once. After that, the substitution vari-
