Erroneous " escape '\' " being added to generated SQL with a JpaRepository findAll(Example)

Hibernate ORM
1

We have moved to Spring Boot 3 and Hibernate 6. When using an ExampleMatcher plumbed into a repository.findAll() method, the generated SQL looks like this:

select …(snipped huge statement)… and lower(ed1_0.name) like ? escape '' <----(There’s a slash here that won’t render on the page)

And we are seeing an exception like so:
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘’'’ at line 1

I am able to get this error with the following combinations:
Original:
DB 10.5.8-MariaDB
MySQL Connector 8.3
Custom MariaDB Dialect

Experimental - 1:
DB 10.5.8-MariaDB
MariaDB Client 3.3.3
No Custom Dialect

Experimental - 2:
DB 10.5.8-MariaDB
MariaDB Client 3.3.3
Custom Dialect with isNoBackslashEscapesEnabled set to true

I assume that some combination of my config and drivers are leading to this, but, ultimately, I need to get that escape statement out of there. What’s the best way to accomplish this?

2

Hi, since Hibernate 6.1.7 we always add an explicit escape '\' to like predicates to ensure consistency of escaped characters across all supported dbs (see [HHH-15736] - Hibernate JIRA).

We’re testing with MariaDB’s jdbc driver version 3.3.1 and we have no problems with explicit escape syntax in like predicates, so it’s strange to hear you’re getting this errors. Could you please check what the SQL_MODE system variable on your db contains? You can just paste us the output of this query:

SELECT @@SQL_MODE, @@GLOBAL.SQL_MODE;

PS: FYI you can use code blocks by enclosing text with backtick characters ` and you should be able to paste the full query / error message including backslashes, that would help in identifying a possible issue.

3
  1. Thanks for taking a look. Here’s the command and some other metadata.
Your MariaDB connection id is 314560
Server version: 10.5.8-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [<redacted>]> SELECT @@SQL_MODE, @@GLOBAL.SQL_MODE;
+-------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------+
| @@SQL_MODE                                                                                | @@GLOBAL.SQL_MODE                                                                         |
+-------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)
  1. Here’s the full SQL and error:
Wrapped by: org.springframework.dao.InvalidDataAccessResourceUsageException: JDBC exception executing SQL [select am1_0.id,am1_0.application_id,am1_0.created_by,am1_0.created_date,am1_0.extended_data_id,am1_0.last_modified_by,am1_0.last_modified_date,am1_0.name,am1_0.uuid,am1_0.version from access_management am1_0 join applications a1_0 on a1_0.id=am1_0.application_id join clients c1_0 on c1_0.id=a1_0.client_id where a1_0.uuid=? and a1_0.id=? and c1_0.uuid=? and c1_0.id=? and c1_0.client_name=? and a1_0.application_name=? and am1_0.name like ? escape '\'] [(conn=314564) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''\'' at line 1] [n/a]; SQL [n/a]
  1. This feels totally crazy, but I was able to correct this issue by using a custom MariaDB Dialect with the following. I understand this is a wild hack job, but it also solves all of my bugs and might be the accepted solution for us, for now.

In our custom MariaDB dialect, I have done this:

@Override
	public SqlAstTranslatorFactory getSqlAstTranslatorFactory() {
		return new StandardSqlAstTranslatorFactory() {
			@Override
			protected <T extends JdbcOperation> SqlAstTranslator<T> buildTranslator(
					SessionFactoryImplementor sessionFactory, Statement statement) {
				return new CommonsMySQLMariaDBSqlAstTranslator( sessionFactory, statement );
			}
		};
	}
	
	public static class CommonsMySQLMariaDBSqlAstTranslator extends MariaDBSqlAstTranslator {

		public CommonsMySQLMariaDBSqlAstTranslator(SessionFactoryImplementor sessionFactory, Statement statement) {
			super(sessionFactory, statement);
		}

		@Override
		public void visitLikePredicate(LikePredicate oldLikePredicate) {
			LikePredicate likePredicate = new LikePredicate(oldLikePredicate.getMatchExpression(), oldLikePredicate.getPattern());
			super.visitLikePredicate(likePredicate);
		}

		@Override
		protected void renderBackslashEscapedLikePattern(Expression pattern, Expression escapeCharacter,
				boolean noBackslashEscapes) {
			pattern.accept( this );
		}
	}

This causes renderBackslashEscapedLikePattern to just render like ?, and the bottom of MariaDBSqlAstTranslator.visitLikePredicate will no longer produce an escape expression.

4

Ok so it doesn’t look like NO_BACKSLASH_ESCAPES is configured, just wanted to make sure.

Using a custom dialect is fine if what you want is disabling Hibernate’s default escape expression, but I don’t think you would need to override visitLikePredicate: the way you did that will mean you always ignore explicit escape characters that might have been passed to the query, and could potentially cause problems.

Does the error persist when overriding only renderBackslashEscapedLikePattern?

Could you please provide the query that’s triggering this behavior? Even better would be a test case based on our templates that we can use to reproduce the error locally.

5

Thank you @mbladel !

The “fix” here is twofold:

  1. Intercept a LikePredicate and nullify its escape character. This prevents this code in the bottom of MariaDBSqlAstTranslator.visitLikePredicate from being executed:
if ( likePredicate.getEscapeCharacter() != null ) {
	appendSql( " escape " );
	likePredicate.getEscapeCharacter().accept( this );
}
  1. Override renderBackslashEscapedLikePattern so that it neither adds the escape expression OR the replace ('\', '\\') style expressions, which also give the same error.

A few notes:

  1. This query is being built by way of an Example/ExampleMatcher builder, and on this particular app/framework no one should be allowed to put either special characters into the DB OR define their own escape characters.

  2. This is the generated query causing the issues:
    select am1_0.id,am1_0.application_id,am1_0.created_by,am1_0.created_date,am1_0.extended_data_id,am1_0.last_modified_by,am1_0.last_modified_date,am1_0.name,am1_0.uuid,am1_0.version from access_management am1_0 join applications a1_0 on a1_0.id=am1_0.application_id join clients c1_0 on c1_0.id=a1_0.client_id where a1_0.uuid=? and a1_0.id=? and c1_0.uuid=? and c1_0.id=? and c1_0.client_name=? and a1_0.application_name=? and am1_0.name like ? escape '\'

  3. Is escape the right Syntax for MariaDB, vs escaped by ? The syntax for the SELECT statement on the Maria KB suggests otherwise.
    SELECT - MariaDB Knowledge Base

6

no one should be allowed to put either special characters into the DB OR define their own escape characters

Regardless of this, creating a new instance of LikePredicate might produce unexpected results so I would advise against it.

This is the generated query

I was not asking for the generated SQL query, but the source query (either JPQL or Criteria) to understand whether an explicit escape character is set. Since you need to override likePredicate.getEscapeCharacter(), it looks like something is setting an explicit \ as the escape character.

Is escape the right Syntax for MariaDB

Yes, see the LIKE predicate page, and we have tests verifying this works. I’m not sure why in your case it’ leading to a problem.

7

Got it, thank you for the warning!

I turned logging up to TRACE - here’s some additional data (not sure if this is what you mean by the JPQL), with some content edited for reading / secrecy:

TRACE o.h.s.r.j.i.DeferredResultSetAccess - Executing query to retrieve ResultSet : select ed1_0.id,ed1_0.application_id,ed1_0.created_by,ed1_0.created_date,ed1_0.custom_data,ed1_0.custom_schema_id,ed1_0.last_modified_by,ed1_0.last_modified_date,ed1_0.name,ed1_0.uuid,ed1_0.version from extended_data ed1_0 join applications a1_0 on a1_0.id=ed1_0.application_id join clients c1_0 on c1_0.id=a1_0.client_id where lower(ed1_0.name) like ? escape '\' and a1_0.uuid=? and c1_0.client_name=? and c1_0.uuid=? and c1_0.id=? and a1_0.application_name=? and a1_0.id=?
DEBUG o.h.SQL - select ed1_0.id,ed1_0.application_id,ed1_0.created_by,ed1_0.created_date,ed1_0.custom_data,ed1_0.custom_schema_id,ed1_0.last_modified_by,ed1_0.last_modified_date,ed1_0.name,ed1_0.uuid,ed1_0.version from extended_data ed1_0 join applications a1_0 on a1_0.id=ed1_0.application_id join clients c1_0 on c1_0.id=a1_0.client_id where lower(ed1_0.name) like ? escape '\' and a1_0.uuid=? and c1_0.client_name=? and c1_0.uuid=? and c1_0.id=? and a1_0.application_name=? and a1_0.id=?
Hibernate: select ed1_0.id,ed1_0.application_id,ed1_0.created_by,ed1_0.created_date,ed1_0.custom_data,ed1_0.custom_schema_id,ed1_0.last_modified_by,ed1_0.last_modified_date,ed1_0.name,ed1_0.uuid,ed1_0.version from extended_data ed1_0 join applications a1_0 on a1_0.id=ed1_0.application_id join clients c1_0 on c1_0.id=a1_0.client_id where lower(ed1_0.name) like ? escape '\' and a1_0.uuid=? and c1_0.client_name=? and c1_0.uuid=? and c1_0.id=? and a1_0.application_name=? and a1_0.id=?
TRACE o.h.r.j.i.ResourceRegistryStandardImpl - Registering statement [HikariProxyPreparedStatement@1834932991 wrapping ClientPreparedStatement{sql:'select ed1_0.id,ed1_0.application_id,ed1_0.created_by,ed1_0.created_date,ed1_0.custom_data,ed1_0.custom_schema_id,ed1_0.last_modified_by,ed1_0.last_modified_date,ed1_0.name,ed1_0.uuid,ed1_0.version from extended_data ed1_0 join applications a1_0 on a1_0.id=ed1_0.application_id join clients c1_0 on c1_0.id=a1_0.client_id where lower(ed1_0.name) like ? escape '\' and a1_0.uuid=? and c1_0.client_name=? and c1_0.uuid=? and c1_0.id=? and a1_0.application_name=? and a1_0.id=?', parameters:[]}]
TRACE o.h.e.j.i.JdbcCoordinatorImpl - Registering last query statement [HikariProxyPreparedStatement@1834932991 wrapping ClientPreparedStatement{sql:'select ed1_0.id,ed1_0.application_id,ed1_0.created_by,ed1_0.created_date,ed1_0.custom_data,ed1_0.custom_schema_id,ed1_0.last_modified_by,ed1_0.last_modified_date,ed1_0.name,ed1_0.uuid,ed1_0.version from extended_data ed1_0 join applications a1_0 on a1_0.id=ed1_0.application_id join clients c1_0 on c1_0.id=a1_0.client_id where lower(ed1_0.name) like ? escape '\' and a1_0.uuid=? and c1_0.client_name=? and c1_0.uuid=? and c1_0.id=? and a1_0.application_name=? and a1_0.id=?', parameters:[]}]
TRACE o.h.o.j.bind - binding parameter (1:VARCHAR) <- [%somename%]
TRACE o.h.o.j.bind - binding parameter (2:BINARY) <- [389dd8b4-e40a-42be-8152-769a916ca069]
TRACE o.h.o.j.bind - binding parameter (3:VARCHAR) <- [SomeClient]
TRACE o.h.o.j.bind - binding parameter (4:BINARY) <- [4867d14a-0061-4836-b331-1ceabc77bd7e]
TRACE o.h.o.j.bind - binding parameter (5:BIGINT) <- [20]
TRACE o.h.o.j.bind - binding parameter (6:VARCHAR) <- [SomeApp]
TRACE o.h.o.j.bind - binding parameter (7:BIGINT) <- [37]
DEBUG o.m.j.c.i.StandardClient - execute query: select ed1_0.id,ed1_0.application_id,ed1_0.created_by,ed1_0.created_date,ed1_0.custom_data,ed1_0.custom_schema_id,ed1_0.last_modified_by,ed1_0.last_modified_date,ed1_0.name,ed1_0.uuid,ed1_0.version from extended_data ed1_0 join applications a1_0 on a1_0.id=ed1_0.application_id join clients c1_0 on c1_0.id=a1_0.client_id where lower(ed1_0.name) like ? escape '\' and a1_0.uuid=? and c1_0.client_name=? and c1_0.uuid=? and c1_0.id=? and a1_0.application_name=? and a1_0.id=?
8

I’m asking if you can show the code that actually triggers the execution of the query.

Anyway, I gave you the information regarding the escape behavior in Hibernate. If you think there is a bug causing the error with MariaDB, please create a new issue in our tracker and attach a reproducer test case that demonstrates the error.

9

I run into this and was able to workaround it, see MariaDB: SQLSyntaxErrorException in escape like queries