Spring NamedParameterJdbcTemplate does not support backticks for quoted identifiers

Affects: 6.1.2

When the table name contains the '&' character, the table name after '&' will be recognized as a parameter. Example:

    @Test
    void test() {
        NamedParameterJdbcTemplate ops = new NamedParameterJdbcTemplate(new JdbcTemplate());

        SqlParameterSource sqlParameterSource = new MapSqlParameterSource("id", 1);
        ops.query("select * from `tb&user` where id = :id", sqlParameterSource, new ColumnMapRowMapper());
    }

The result of running the code is:

org.springframework.dao.InvalidDataAccessApiUsageException: No value supplied for the SQL parameter 'user`': No value registered for key 'user`'

    at org.springframework.jdbc.core.namedparam.NamedParameterUtils.buildValueArray(NamedParameterUtils.java:379)
    at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.getPreparedStatementCreator(NamedParameterJdbcTemplate.java:472)
    at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.getPreparedStatementCreator(NamedParameterJdbcTemplate.java:446)
    at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.query(NamedParameterJdbcTemplate.java:218)
    at org.example.demo.jdbc.NamedParameterJdbcTemplateTest.test(NamedParameterJdbcTemplateTest.java:20)
    at java.base/java.lang.reflect.Method.invoke(Method.java:580)
    at java.base/java.util.ArrayList.forEach(ArrayList.java:1596)
    at java.base/java.util.ArrayList.forEach(ArrayList.java:1596)

Comment From: jhoeller

NamedParameterUtils does not detect backticks for quoting there, just standard single and double quotes. I'm therefore turning this into an enhancement request for processing backticks as quotes as well.

For the time being, can you use a different quoting symbol or possibly escape the ampersand? Are you on MySQL by any chance, given that you are using backticks to begin with (outside of standard SQL)?

Comment From: Ganledes

For the time being, can you use a different quoting symbol or possibly escape the ampersand? Are you on MySQL by any chance, given that you are using backticks to begin with (outside of standard SQL)?

@jhoeller Yes, I use MySQL. The two methods you mentioned above don't work for me. I've employed some less conventional approaches to address this issue, and it's currently functioning well.

Comment From: snicoll

The two methods you mentioned above don't work for me.

Can you share what the problem is by reworking the example above to show what the problem was? I expect using standard quotes to work. As @jhoeller mentioned, backticks aren't standard so we need a justification before handling them as it could have a side effect elsewhere.

Comment From: Ganledes

@snicoll What I mean by "not working" is not that it's technically infeasible. MySQL defaults to using backticks to quote identifiers, but it can be configured to support double quotes by setting sql_mode=ANSI_QUOTES. The official documentation describes as follows:

Treat " as an identifier quote character (like the ` quote character) and not as a string quote character. You can still use ` to quote identifiers with this mode enabled. With ANSI_QUOTES enabled, you cannot use double quotation marks to quote literal strings because they are interpreted as identifiers.

Unfortunately, altering the database configuration is a bit impractical for me. Another solution I thought of is to check if the character before '&' or ':' is a whitespace to determine if it's a named parameter. I'm not sure if this approach is viable.

Comment From: sbrannen

MySQL defaults to using backticks to quote identifiers, but it can be configured to support double quotes by setting sql_mode=ANSI_QUOTES.

@Ganledes, the code in question is NamedParameterUtils.parseSqlStatement(String) which does not take the given database into account. It only takes the given query String into account. Thus, the fact that "MySQL defaults to using backticks to quote identifiers" does not influence the algorithm in NamedParameterUtils.parseSqlStatement(String).

Comment From: sbrannen

Reopening to provide analogous support in org.springframework.r2dbc.core.NamedParameterUtils.