15

I'm wondering if/how this is possible, if it is, I'm sure its a simple fix that I can't seem to figure out

@SqlQuery("SELECT * FROM Table WHERE column LIKE '%:thingName%'")
public Set<Things> getThings(@Bind("thingName", String thingName)

Essentially for this toy example I am trying to select a row where a column contains [any text]thingName[anyText]. When using as above, I think the quotes obscure the bound variable so it literally looks for [any text]:thingName[anyText] and not my bound variable.

Thank you in advance, Madeline

user2441922
  • 153
  • 1
  • 4
  • You should change the accepted answer to the concatenation solution. There is a way to do this without making code changes that may introduce a bug. – A.J. Brown Dec 21 '16 at 17:34

3 Answers3

24

I use concat to surround input with % signs while still using a bound variable to avoid SQL injection:

@SqlQuery("select * from atable where acolumn like concat('%',:thingName,'%')")
public Set getNames(@Bind("thingName") String thingName);
jbuhacoff
  • 1,189
  • 1
  • 13
  • 17
13

It appears to be the case that you must add the '%' percentages to the bound variable:

@SqlQuery("SELECT * FROM Table WHERE column LIKE :thingName")
public Set<Things> getThings(@Bind("thingName") String thingName); // where thingName = "%" + thingName + "%"

See also: https://groups.google.com/forum/?fromgroups#!topic/jdbi/EwUi2jAEPdk

Quote from Brian McCallister

Using the :foo binding stuff creates a prepared statement and binds in the value for name in this case. You need the % to be part of the bound value, or you need to not use bindings to a prepared statement.

  • Approach 1 (the safer and generally better one): "select … from foo where name like :name" and bind the value ("%" + name)

  • Approach 2 (which opens you up to sql injection):

"select … from foo where name like '%' " and define("name", name) (or in sql object, (@Define("name") name) -- which puts name in as a literal in your statement.

The key thing is that the % character is part of the value you are testing against, not part of the statement.

ljgw
  • 2,751
  • 1
  • 20
  • 39
  • Thank you for your reply! This is what I had done, I was hoping there was a better way out there but it seems not. - Madeline – user2441922 May 31 '13 at 22:39
  • 1
    The answer below using a concatenation operation is a better answer as it doesn't require logical changes. If you're using postgres, you can do `'%' || :term || '%' – A.J. Brown Dec 21 '16 at 17:33
0

enter image description here

This must be the case with LIKE while binding variables. As per JDBI doc(Here) using SQL query concatenation can solve the issue. It worked for me.

Gokul Pandey
  • 171
  • 1
  • 6