Not being able to bind parameter within String in LIKE-expression

[Johan Horvius]

I'm trying to write a query used for search using JDBI.

I have tried it written as: 

    return jdbi.withHandle(h -> h.createQuery("SELECT * FROM "+ TABLE_NAME +

      " WHERE name LIKE '%" + name +  "%'")

      .mapTo(MySpecificEntity.class)

      .list());

and it works fine. However now I'm just concatenating the search query called name here (and possibly risking injections?)

I tried utilizing the .bind-api to do the same thing but was not able to as follows:

    return jdbi.withHandle(h -> h.createQuery("SELECT * FROM "+ TABLE_NAME +

      " WHERE name LIKE '%:name%'")

      .bind("name", name)

      .mapTo(MySpecificEntity.class)

      .list());

it gives me the following error org.jdbi.v3.core.statement.UnableToExecuteStatementException: Unable to execute. The query doesn't have named parameters, but provided binding 

if I remove the single quotation charecters ', then I get a syntax error instead as follows: org.jdbi.v3.core.statement.UnableToCreateStatementException: org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement

Any idea how I can express this and using the bind-api? Seems to be much cleaner and more consistent with the rest of my code using JDBI

[Johan Horvius]

I found a workaround.

Updated my query to:

    String query = "%" + name + "%";

    return jdbi.withHandle(h -> h.createQuery("SELECT * FROM "+ TABLE_NAME +

      " WHERE name LIKE :query")

      .bind("query", query)

      .mapTo(MySpecificEntity.class)

      .list());

which as I understood it should be injection-safe?

A bit annoying to have to do it like that..

[Allan Morstein]

JDBI's binds seem to follow the rules of JDBC's parameterized queries, which in turn follow the SQL rules for bind variables. Your first try wouldn't be valid in either JDBC or direct SQL. You need to make the value that you're binding in atomic (it can't be inside a string literal, for instance). To do what you were initially trying, you need to make the concatenate the wildcards to your literal in the SQL query:

    return jdbi.withHandle(h -> h.createQuery("SELECT * FROM "+ TABLE_NAME +

      " WHERE name LIKE '%' || :name || '%'")

      .bind("name", name)
      .mapTo(MySpecificEntity.class)
      .list());

I feel that I should also point out that you can use "define" to avoid concatenating the table name in:

    return jdbi.withHandle(h -> h.createQuery("SELECT * FROM <TABLE_NAME>" +
      " WHERE name LIKE '%' || :name || '%'")
       .define("TABLE_NAME", TABLE_NAME)

      .bind("name", name)
      .mapTo(MySpecificEntity.class)
      .list());

[Matthew Hall]

As you observed, Jdbi does not parse bound parameters inside quotes. This is intentional.

Even if we did parse it, JDBC wouldn't recognize the bound parameter: WHERE name LIKE '%:query%' would be parsed to WHERE name LIKE '%?%', which JDBC would just treat as a literal varchar anyway. And then you'd have another problem: bound parameters after the LIKE clause would be applied off-by-one by JDBC, because of the LIKE parameter it didn't recognize.

I agree it's a bit annoying, and I'm open to introducing API to make this more approachable. Maybe a bindLike(), bindLikePrefix(), and bindLikeSuffix() method, and matching SQL object annotations? Or some other names.

Would you care to open a bug on GitHub?

-Matt

[Matthew Hall]

On second thought, I like Allan's suggestion better.

I opened https://github.com/jdbi/jdbi/issues/969 to add examples to the docs to demonstrate LIKE clauses with bound parameters.

-Matt