Prevent SQL injection from form-generated SQL - NO PreparedStmts

Question

Hi all,

I have a search table where user will be able to filter results with a filter of the type:

• Field [Name], Value [John], Remove Rule
• Field [Surname], Value [Blake], Remove Rule
• Field [Has Children], Value [Yes], Remove Rule
• Add Rule

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)?

Answer 1

If you add arguments to prepared statements they will automatically be 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 2

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 3

http://stackoverflow.com/questions/144550/sql-server-dynamic-where-clause/144626#144626

Build the where clause dynamically, but do it using parameter names.