Search code examples
javasqldaojdbi

Is there a way to use @Bind instead of @Define when an SQL clause needs to "disappear" for a degenerate case?

Note: This example has been simplified heavily.

I started with a method like this:

private static final String QUERY_B = QUERY_A + " AND (foo = :bar) \n";

@SqlQuery(MASTER_QUERY + " AND " + QUERY_B)
public abstract Long doQueryB(@Bind("userId") String userId,
                              @Bind("bar") String bar);

The thing was, though, if bar was an empty string, I needed the entire clause to go away, e.g. as if it was just:

private static final String QUERY_B = QUERY_A + " \n";

So, I constructed a query that would simply insert "true" in that case, i.e.

private static final String QUERY_B = QUERY_A + " AND true \n";

The idea was to generate a clause, either "true" or (foo = 123), before passing the clause into the function:

private static final String QUERY_B = QUERY_A + " AND <clause> \n";

But this meant I had to use @Define, not @Bind:

@SqlQuery(MASTER_QUERY + " AND " + QUERY_B)
public abstract Long doQueryB(@Bind("userId") String userId,
                              @Define("clause") String clause);

And now this means this method is open to SQL injection.

I don't want to try to sanitize it myself (Defense Option 4).

Is there a way to go back to using @Bind, without creating an entirely separately method, i.e.

@SqlQuery(MASTER_QUERY + " AND " + QUERY_A)
public abstract Long doQueryB(@Bind("userId") String userId);

@SqlQuery(MASTER_QUERY + " AND " + QUERY_B)
public abstract Long doQueryB(@Bind("userId") String userId,
                              @Bind("bar") String bar);

...because there are several closely-related methods that would all need to be duplicated for the bar case, and on top of that, we expect more and more kinds of bars to be introduced in the future, so that a large amount of methods would be need to be created, if we went that route.

Solution

  • I have implemented a similar logic in the past by using an OR clause that checks if the parameter needs to "disappear". So, in case of an empty string, your SQL could look like this:

    private static final String QUERY_B = QUERY_A + " AND (:bar = '' OR foo = :bar) \n";

    The biggest advantage of this approach is that you can keep your method signature as is:

    @SqlQuery(MASTER_QUERY + " AND " + QUERY_B)
public abstract Long doQueryB(@Bind("userId") String userId, @Bind("bar") String bar);