CODEWITHSUNDEEP
javasqlregexsecuritysql-injection
Markos Fragkakis
Markos Fragkakis

Reputation: 7771

Prevent SQL injection from form-generated SQL - NO PreparedStmts

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

Upvotes: 4

Views: 460

Answers (3)

Chris
Chris

Reputation: 28064

SQL Server: Dynamic where-clause

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

Upvotes: 2

Will Hartung
Will Hartung

Reputation: 118704

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

Upvotes: 5

Oskar Kjellin
Oskar Kjellin

Reputation: 21900

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

Upvotes: 2

Related Questions