The right way to prepare SQL statements with parametrized text search

Question

Suddenly I've realized that while this works in groovy just like it is expeceted:

Sql.newInstance(connectionParams).rows("SELECT FROM ITEMS WHERE id = ?", [200])

this won't work

Sql.newInstance(connectionParams).rows("SELECT FROM ITEMS WHERE name LIKE '%?%'", ["some"])

All you can get is

Failed to execute: SELECT FROM ITEMS WHERE name LIKE '%?%' because: The column index is out of range: 1, number of columns: 0.

My questions are:

• Is it intentionally implemented this way? I've never needed to have a parametrized text search, so I'm not sure where this behaviour is typical or not.
• How can I nevertheless safely parametrize statement with text search in it?

Answer 1

I believe you want to include the %'s in the parameter, like:

Sql.newInstance(connectionParams).rows("SELECT FROM ITEMS WHERE name LIKE ?", ["%some%"])