2.0.202 and hibernate: boolean field regression?

[Ulrich]

Hi,

congratulations for the new H2 release!!

I tested the new release and have a problem with JPA/hibernate.

When I use an entity with a boolean field and I use this field in a predicate of a query I get the exception:

org.h2.jdbc.JdbcSQLSyntaxErrorException: Values of types "BOOLEAN" and "INTEGER" are not comparable

The test runs fine with H2 1.4.200.

Attached you find the small test case.

I use the following hibernate dependency:

        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-core</artifactId>
            <version>5.4.30.Final</version>
        </dependency>

Can you please check if if's a regression or if I made a mistake?

Thanks!

Ulrich

[Ulrich]

Small update:

Not working:

   Predicate predicate = criteriaBuilder.isFalse(itemRoot.get("testFlag"));

Working:

   Predicate predicate = criteriaBuilder.equal(itemRoot.get("testFlag"), Boolean.FALSE);

[Evgenij Ryazanov]

Hello.

It looks like a bug of Hibernate ORM.

The basic Dialect class has the following method:

public String toBooleanValueString(boolean bool) {

return bool ? "1" : "0";

}

It is obliviously wrong for any database system that has BOOLEAN data type.

H2Dialect should override this method to avoid generation of invalid SQL.

Please, report this issue here:

https://hibernate.atlassian.net/browse/HHH

[jeremie...@gmail.com]

Hello,

In order to fix the CVE CVE-2021-42392, I have to upgrade to H2 v2.0.206. However, having H2 incompatible with hibernate make the upgrade impossible.,

As I agree the bug could be fixed in Hibernate, shouldn't H2 be more backward compatible ?

Moreover, when testing my application with H2 v2.0.206, I have the same bug with camunda, which doesn't use Hibernate, this issue breaks a lot of applications..

camunda fails with :

Values of types "BOOLEAN" and "INTEGER" are not comparable; SQL statement:

select RES.ID_,

      RES.REV_,

      RES.DUEDATE_,

      RES.PROCESS_INSTANCE_ID_,

      RES.EXCLUSIVE_

     from ACT_RU_JOB RES

     where (RES.RETRIES_ > 0)

      and (

           RES.DUEDATE_ is null or

           RES.DUEDATE_ <= ?

      )

      and (RES.LOCK_OWNER_ is null or RES.LOCK_EXP_TIME_ < ?)

      and RES.SUSPENSION_STATE_ = 1

      and (

    (

    RES.EXCLUSIVE_ = 1

          and not exists(

            select J2.ID_ from ACT_RU_JOB J2

            where J2.PROCESS_INSTANCE_ID_ = RES.PROCESS_INSTANCE_ID_                                           -- from the same proc. inst.

            and (J2.EXCLUSIVE_ = 1)                                                              -- also exclusive

           and (J2.LOCK_OWNER_ is not null and J2.LOCK_EXP_TIME_ >= ?)  -- in progress

            )

   )

    or

    RES.EXCLUSIVE_ = 0

   )

    LIMIT ? OFFSET ? [90110-206]

[Evgenij Ryazanov]

Hello.

This vulnerability affects only the H2 Console and it may harm you only if you run it with -webAllowOthers parameter without additional protection. You shouldn't do that with any version of H2.

https://github.com/h2database/h2database/security/advisories/GHSA-h376-j262-vhq6

It doesn't affect H2 Database in any way.

H2 since the version 2.0.204 has LEGACY mode, it this mode some (but not all) wrong commands are allowed, including equality comparisons between numeric and boolean values. To enable this mode you need to add ;MODE=LEGACY to JDBC URL.

[jeremie...@gmail.com]

Thanks for your answer.

I know about the vulnerability - I don't even use the console or H2 in production.. but my project has a policy about CVE. This vulnerability is tagged by our CI/CD system and I have to fix it or remove the dependency. I just can't say "it's not exploitable" if the vulnerability scanner tag the cve, the release will not be allowed.

[Pierre Caron]

To everyone facing the  Values of types "BOOLEAN" and "INTEGER" are not comparable error message, you can create a class that will override the  toBooleanValueString method as Evgenij suggested :

package com.myCorp;

import org.hibernate.dialect.H2Dialect;

public class H2DialectExtended extends H2Dialect {

    @Override
    public String toBooleanValueString(boolean bool) {
        return bool ? "TRUE" : "FALSE";
    }

}

And load it in your Spring Boot testing application.properties :

spring.jpa.properties.hibernate.dialect=com.myCorp.H2DialectExtended

This way, Hibernate will write :

WHERE myBooleanColumn=TRUE

instead of :

WHERE myBooleanColumn=1

Which will solve the problem as the myBooleanColumn is of type H2 Boolean.