Prepared Statements & Input Sanitisation
1. Prepared Statements
Java offers PreparedStatement to execute parameterised queries. Queries built with Prepared Statements are less prone to exploits.
Example:
The Query we need to make:
String query = "SELECT col1, col2, col3 FROM table1 WHERE " + user_input;
Using PreparedStatement with parameterised values:
// write the query with "?" placeholder for the user_input
String query = "SELECT col1, col2, col3 FROM table1 WHERE ?";
// Create database connection
Connection conn = source.getConnection();
// Prepare a statement for the query
PreparedStatement stmt = conn.prepareStatement(query);
// set the placeholder with the actual user_input
stmt.setString(1, user_input);
// execute the query
ResultSet result = stmt.executeQuery(query);
IS IT ENOUGH? NO!
Even after using PreparedStatement or createQuery (Similar method for JPA) or anything, there still is a chance that the attacker can pass. So that brings us to this...
Edit as @phil 's pointed out, using PreparedStatement does stop the illegal values from execution. But still I highly recommend sanitising the inputs as the user may input "String" or random special characters when you were expecting "int".
2. Sanitisation
Let's say, we have two sets of columns for both tables and user can input the column name and the value as well.
Instead of unfiltered input like this:
String query = "SELECT col1, col2, col3 FROM table1 WHERE ?";
Use some filters. Filters can be anything. May be some string functions or string comparison or input variable type check or anything.
Case1:
Let's say the user can filter using the column "col1" and it is an "Integer" or "Numeric" type, we can filter the input to see if there are any special characters in it using Regex:
^[0-9]*$
Case2:
Check if the input column name is valid.
private static final Set<String> valid_column_names
= Collections.unmodifiableSet(Stream
.of("col1", "col2", "col3")
.collect(Collectors.toCollection(HashSet::new)));
boolean is_valid = false;
if (valid_column_names.contains(user_column_input)) {
is_valid = true;
}
if(!is_valid){
throw new IllegalArgumentException("Invalid Column input");
}
String query = "SELECT col1, col2, col3 FROM table1 WHERE ?";
// prepare statements and execute
Final Notes:
So, after all these preventive measures, is your dynamically generated query safe? A lot safer but you can't assure that. There are a lot of problems that makes your db prone to Injection.
