Problem

When using a "LIKE" clause in a database SELECT statement the general syntax is use something like "... WHERE some_column LIKE '%my search term%';"

Therefore, when entering this into the database filter configuration and using a message attribute for the search term, it is tempting to enter something like this using selector syntax "... WHERE my_column LIKE '%${my.search.attribute}%';"

However when APIGW sees selector expressions in an SQL statement, it automatically quotes the value when expanding the statement, making the single-quotes unnecessary (and bad). Additionally, APIGW treats the % characters differently (it surrounds them with single-quotes) which causes a database syntax error at runtime:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%'my search term'%' at line 1

Resolution

The resolution for this issue is fairly simple. We just need to "wildcardize" the search term attribute prior to invoking the database filter. To do this, use a Set Attribute filter to enclose the search term with % characters. Set the name to something like wildcarded.search.term and the value to %${my.search.term}% without any quotes. Then in the SQL statement, use the wildcarded search attribute: ... WHERE my_column LIKE ${wildcarded.search.term}; again, without any quotes.

This should result in an SQL statement with proper syntax. While testing and debugging, you can see the database activity by temporarily enabling DEBUG logging for the listener, which will show something like this:

DEBUG 8/5/21, 07:36:30.683 WildcardedPreparedStatement: Running SQL statement [SELECT id, comments FROM test_sessions WHERE session_id LIKE ?;]
DEBUG 8/5/21, 07:36:30.683 WildcardedPreparedStatement.getStatement: param 1 is set to value [%1234%]