I have a search table where user will be able to filter results with a filter of the type:
So the user will be able to set an arbitrary set of filters, which will result essentially in a completely dynamic WHERE clause. In the future I will also have to implement more complicated logical expressions, like
Where (name=John OR name=Nick) AND (surname=Blake OR surname=Bourne),
Of all 10 fields the user may or may not filter by, I don't know how many and which filters the user will set. So, I cannot use a prepared statement (which assumes that at least we know the fields in the WHERE clause). This is why prepared statements are unfortunately out of the question, I have to do it with plain old, generated SQL.
What measures can I take to protect the application from SQL Injection (REGEX-wise or any other way)?
Java, untested.
List<String> clauses = new ArrayList<String>();
List<String> binds = new ArrayList<String>();
if (request.name != null) {
binds.add(request.name);
clauses.add("NAME = ?");
}
if (request.city != null) {
binds.add(request.city);
clauses.add("CITY = ?");
}
...
String whereClause = "";
for(String clause : clauses) {
if (whereClause.length() > 0) {
whereClause = whereClause + " AND ";
}
whereClause = whereClause + clause;
}
String sql = "SELECT * FROM table WHERE " + whereClause;
PreparedStatement ps = con.prepareStatment(sql);
int col = 1;
for(String bind : binds) {
ps.setString(col++, bind);
}
ResultSet rs = ps.executeQuery();
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With