Java 17 Recipes

12-7. Guarding Against SQL Injection
 Problem
Your application performs database tasks. To reduce the chances of a SQL injection 
attack, you need to ensure that no unfiltered strings of text are being appended to SQL 
statements and executed against the database.
Tip although prepared statements are the solution to this recipe, they can be 
used more than protecting against sQL injection. they also provide a way to 
centralize and better control the sQL used in an application. instead of creating 
multiple, possibly different, versions of the same query, for example, you can 
create the query once as a prepared statement and invoke it from many different 
places throughout your code. any change to the query logic needs to happen only 
when you prepare the statement.
 Solution
Use PreparedStatement objects for performing the database tasks. They send a 
precompiled SQL statement to the DBMS rather than a string. The following code 
demonstrates how to perform a database query and a database update using a
java.sql.PreparedStatement object.
In the following code example, PreparedStatement queries a database for a given 
record. Assume that the a String[] of recipe numbers is passed to this code as a 
variable.
Chapter 12 Working With Databases

---

449
private static void queryDbRecipe(String[] recipeNumbers) {
String sql = "SELECT ID, RECIPE_NUMBER, RECIPE_NAME, DESCRIPTION "
+ "FROM RECIPES "
+ "WHERE RECIPE_NUMBER = ?";
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
for (String recipeNumber : recipeNumbers) {
pstmt.setString(1, recipeNumber);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
System.out.println(rs.getString(2) + ": " + rs.getString(3)
+ " - " + rs.getString(4));
}
}
} catch (SQLException ex) {
ex.printStackTrace();
}
}
The next example demonstrates a PreparedStatement object that inserts a record 
into the database. Assume that the recipe number, title, and description are passed to 
this code as variables. The ID column is declared NOT NULL and AUTO-INCREMENT, so 
it is also possible to assign NULL to the column to generate sequence numbers.
private static void insertRecord(String recipeNumber,
String title,
String description) {
String sql = "INSERT INTO RECIPES VALUES(" +
"null, ?,?,?)";
try(PreparedStatement pstmt = conn.prepareStatement(sql);) {
pstmt.setString(1, recipeNumber);
pstmt.setString(2, title);
pstmt.setString(3, description);
pstmt.executeUpdate();
System.out.println("Record successfully inserted.");
Chapter 12 Working With Databases

---

450
} catch (SQLException ex){
ex.printStackTrace();
}
}
In this last example, a PreparedStatement object deletes a record from the database. 
Again, assume that the recipeNumber string is passed to this code as a variable.
private static void deleteRecord(String recipeNumber) {
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();
}
}
The main method is:
public static void main(String[] args) {
try {
CreateConnection createConn = new CreateConnection();
conn = createConn.getConnection();
String[] recipeArr = new String[1];
recipeArr[0] ="12-1";
queryDbRecipe(recipeArr);
insertRecord(
"12-6",
"Simplifying and Adding Security with Prepared 
Statements",
"Working with Prepared Statements");
recipeArr[0] ="12-6";
queryDbRecipe(recipeArr);
deleteRecord("12-6");
Chapter 12 Working With Databases

---

451
} catch (java.sql.SQLException ex) {
System.out.println(ex);
} finally {
if (conn != null) {
try {
conn.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
}
The following is the output.
12-1: Installing MySQL - Downloading and installation of a MySQL Database
Record successfully inserted.
12-6: Simplifying and Adding Security with Prepared Statements - Working 
with Prepared Statements
Recipe 12-6 successfully deleted.
As you can see, a PreparedStatement object 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.