Cannot put value into SQL Statement in Java Spring Boot

Question

I have private static final String SQL_SEARCH_ALL = "SELECT * FROM product WHERE name LIKE '%?%'"; SQL statement, where I need to pass a query String. When instead of ? mark I pass a string like '%cola%' it works fine.

I pass paramater using JdbcTemplate as I have used to

return jdbcTemplate.query(SQL_SEARCH_ALL, new Object[]{searchInput}, productRowMapper);

That returns zero objects.

I have problem with passing value into this statement.

The Impaler · Accepted Answer · 2021-01-04T02:14:25.087

2

JDBC parameters are not strings that are replaced anywhere you use a ? marker; that would open the query to SQL Injection. Depending on each database and each JDBC driver the allowed locations for a ? parameter marker vary.

It's safe to say a parameter can usually go where a typical literal value can go, but it's not always the case. For example, in DB2 the clause FETCH FIRST 5 ROWS ONLY does not accept a ? instead of the literal 5.

In your case, the typical solution is to use CONCAT(). For example:

private static final String SQL_SEARCH_ALL =
  "SELECT * FROM product WHERE name LIKE CONCAT('%', ?, '%')";

Of course you need to decide what do you want to happen is a null parameter is provided. Probably a COALESCE() function is in order there.

edited Jan 04 '21 at 02:14

answered Jan 04 '21 at 02:06

The Impaler

45,731
9
39
76

This might not work, because you are trying to concatenate a wildcard `?` placeholder to `%` on both sides. Instead, a single `?` placeholder should be used here. – Tim Biegeleisen Jan 04 '21 at 02:19
@TimBiegeleisen I'm not sure what you're talking about. The statement will work fine, if e.g. `ps.setString(1, "lamp");` is used, then it'll find any product where `lamp` shows up anywhere in its `name` column, and every JDBC library I know of allows a `?` in that position. – rzwitserloot Jan 04 '21 at 02:52
@rzwitserloot [This question and answers](https://stackoverflow.com/questions/2857164/cannot-use-a-like-query-in-a-jdbc-preparedstatement) do not agree with your previous comment. – Tim Biegeleisen Jan 04 '21 at 02:57
@TimBiegeleisen You have misunderstood that answer. Go try it out, it works fine. – rzwitserloot Jan 04 '21 at 13:01
@rzwitserloot I just used the Lombok library for a small Spring Boot project. Nice stuff, it helps to keep my Java code as minimal as possible. – Tim Biegeleisen Jan 04 '21 at 14:30

score 1 · Answer 2 · answered Jan 04 '21 at 02:18

The standard way of doing this in Java (or really any language) is to use a single ? placeholder and then bind the wildcard expression to that placeholder:

private static final String SQL_SEARCH_ALL = "SELECT * FROM product WHERE name LIKE ?";
String searchInput = "%cola%";
return jdbcTemplate.query(SQL_SEARCH_ALL, new Object[]{ searchInput }, productRowMapper);

Cannot put value into SQL Statement in Java Spring Boot

2 Answers2