Prevent SQL injection from created using SQL form - NO PreparedStmts

Question

I have a lookup table where the user will be able to filter the results using a filter like:

• Field [Name], Value [John], Delete Rule
• Field [Last Name], Value [Blake], Delete Rule
• [Has children] field, value [Yes], Delete rule
• Add Rule

Thus, the user will be able to set an arbitrary set of filters, which ultimately leads to a fully dynamic WHERE clause. In the future, I will also have to implement more complex logical expressions, such as

Where (first name = John OR first name = Nick) AND (last name = Blake or last name = Born),

Of all 10 fields that the user can or cannot filter, I do not know how many and which filters the user will set. Therefore, I cannot use a prepared statement (which assumes that at least we know the fields in the WHERE clause). That is why ready-made statements, unfortunately, are out of the question, I have to do this with the help of a simple old, generated SQL.

What measures can I take to protect my application from SQL Injection (REGEX-wise or any other way)?

Answer 1

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(); 

Answer 2

If you add arguments to prepared statements, they will be automatically escaped.

 conn = pool.getConnection( ); String selectStatement = "SELECT * FROM User WHERE userId = ? "; PreparedStatement prepStmt = con.prepareStatement(selectStatement); prepStmt.setString(1, userId); ResultSet rs = prepStmt.executeQuery(); 

Answer 3

SQL Server: where dynamic clause

Create a where clause dynamically, but do it using parameter names.