How can I inject "variables" WHERE clauses into a query?

Question

I have a query:

SELECT * FROM table_name WHERE name=? surname=? gender=?;

and I have to inject the strings into the query (I use the prepared statement) but the problem is that these 3 clauses are variable; I can have 0, 1, or also all 3 clauses active based on the input that the user do.

How can I "ignore" each of them if the user doesn't input anything for it?

Thanks to all!

EDIT: it's also good if anyone know how to setStrin() to a preparedStatement without let him to put ' ' around the string.

Either prepare all seven possible combinations (8 if no clause is also an option), or build the query dynamically. — RealSkeptic, Jan 09 '17 at 15:45
Possible duplicate of [Prepared statement with dynamic where clause](http://stackoverflow.com/questions/15405288/prepared-statement-with-dynamic-where-clause) — Arnaud, Jan 09 '17 at 15:45
Have a look on the Criteria. By the way the solution under is working. — Zorglube, Jan 09 '17 at 15:50
If you could use a `setString()` allowing it not to put quotes around the value then it would be useless to protect against injection. Anybody could have a value like `true ; drop table table_name; --`. — RealSkeptic, Jan 09 '17 at 15:58

score 3 · Accepted Answer · edited Jan 09 '17 at 15:52

3

The simplest solution is going to be to build your prepared statement dynamically, something like this:

String sql = "SELECT * FROM table_name";

if (nameInput != null || surnameInput != null || genderInput != null)
{
    sql += " WHERE ";
}

if (nameInput != null)
{
    sql += "name=? ";
}

I think you get the idea from there for the other inputs.

You can then pass the final value of sql into your prepare call along with the input values.

edited Jan 09 '17 at 15:52

RealSkeptic

33,993
7
53
79

answered Jan 09 '17 at 15:46

Brian Driscoll

19,373
3
46
65

Sorry, I saw the [mysql] tag and reflexively coded my answer in PHP. I'm hoping my attempt at Java is okay (at least in the sense that you can grok it). – Brian Driscoll Jan 09 '17 at 15:48
but in this way is injectable! I want to prevent it (that's why I use preparedStatement with the setString() – Claudio Jan 09 '17 at 15:54
1

@ClaudioCiociola It's not injectable because you build the *query skeleton* dynamically, but you add the user's values only to the prepared statement. – RealSkeptic Jan 09 '17 at 15:55
1

@ClaudioCiociola It's no more injectable than what you already have. – Brian Driscoll Jan 09 '17 at 15:55
2

This is not vulnerable to malitious SQL injection, you still need to prepare the statement, you are just building up the statement conditionally in java before sending it to the database. On little tip, when I do things like this I tend to add `WHERE 1 = 1` as the initial clause, so that I know any further predicates should be `AND Col=?` and don't need to check whether it is the first predicate and should start with `WHERE` rather than `AND`. – GarethD Jan 09 '17 at 15:56

John Joseph · Answer 2 · 2017-01-09T16:37:14.420

Easy to do without any complex or expensive logic, in a single line...

Assuming that your three variables are @name, @surname, and @gender.

Also assuming that a zero-length string will be provided when a filter is not required.

Then, your Select statement is simply:

    select * from table_name where (name = @name or @name = '') and (surname = @surname or @surname = '') and (gender = @gender or @gender = '')

That's all there is to it! No complex or expensive logic.

How can I inject "variables" WHERE clauses into a query?

2 Answers2