Prevent SQL injection from form-generated SQL - NO PreparedStmts

Question

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

I don't understand why prepared statements are out of the question. — John Douthat
– John Douthat, Commented May 17, 2010 at 20:32
Because of the 10 fields that are filterable, I don't know how many the user is going to filter. So, I may have only filtering in the name (WHERE NAME = 'XXX') or filtering in more fields (WHERE NAME = 'XXX' AND SURNAME='YYY' AND .....). Each possible combination of filters would require a different prepared statement. That's why prepared statements are out of the question here. — Markos Fragkakis
– Markos Fragkakis, Commented May 17, 2010 at 20:48
Dynamically construct and bind the prepared statement then. Having a variable quantity of parameters has never been an obstacle, and is actually easier than having to deal with query building by string concatenation, which I assume you were doing before. — Lotus Notes
– Lotus Notes, Commented May 17, 2010 at 23:48

Will Hartung · Accepted Answer · 2010-05-17 21:13:51Z

5

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

answered May 17, 2010 at 21:13

Will Hartung

119k20 gold badges134 silver badges209 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Sean Patrick Floyd Over a year ago

sounds good, but I'd always use StringBuilder instead of String concatenation. That's the kind of stuff StringBuilder was made for.

pkaeding Over a year ago

This is definitely the best approach. Whitelist the column names, and then iterate through the input to set the parameters on the prepared statement.

Jess Over a year ago

I like your binds List<String> too. Good idea.

Oskar Kjellin · Accepted Answer · 2010-05-17 20:33:46Z

2

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

answered May 17, 2010 at 20:33

Oskar Kjellin

22k10 gold badges57 silver badges95 bronze badges

2 Comments

Markos Fragkakis Over a year ago

This query assumes that the records are filtered by userId. What if they are filtered by name? Or by name and surname? Or by name and email? Or by email? Each of these would require a different prepared statement. And I have 10 fields the user may (or may not) filter by (and their combinations). That;s why prepared statements are out of the question here.

Markos Fragkakis Over a year ago

You were right, I understood as soon as I saw the answer. The other answer was a bit clearer, so I marked that one. (+1)

Community · Accepted Answer · 2017-05-23 10:27:38Z

2

SQL Server: Dynamic where-clause

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

edited May 23, 2017 at 10:27

CommunityBot

11 silver badge

answered May 17, 2010 at 21:15

Chris

28.1k26 gold badges132 silver badges227 bronze badges

1 Comment

Markos Fragkakis Over a year ago

Thanks Chris, if only there was a way to split the answer. Will was 2 minutes faster. (+1)

Collectives™ on Stack Overflow

Prevent SQL injection from form-generated SQL - NO PreparedStmts

3 Answers 3

3 Comments

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related