Bug #98237 PreparedStatement.setObject(i, "false", Types.BOOLEAN) always sets true or 1

• Submitted: 15 Jan 2020 12:10
• Modified: 21 Apr 2020 20:48
• Reporter: Bjoern Sundin
• Status: Closed
• Category: Connector / J
• Severity: S2 (Serious)
• Version: 8.0.19, 5.1.48
• OS: Any
• CPU Architecture: Any

[15 Jan 2020 12:10] Bjoern Sundin

Description:
Pasing the string "false" and Types.BOOLEAN to PreparedStatement.setObject will not set an TINYINT to 0 but always to 1. 

The cause is in the logic: "true".equalsIgnoreCase((String) parameterObj) || !"0".equalsIgnoreCase((String)

Code snippet from PreparedStatement.setObject:
case Types.BOOLEAN:

    if (parameterObj instanceof Boolean) {
        setBoolean(parameterIndex, ((Boolean) parameterObj).booleanValue());

        break;
    } else if (parameterObj instanceof String) {
        setBoolean(parameterIndex, "true".equalsIgnoreCase((String) parameterObj) || !"0".equalsIgnoreCase((String) parameterObj));

        break;
    } else if (parameterObj instanceof Number) {
        int intValue = ((Number) parameterObj).intValue();

        setBoolean(parameterIndex, intValue != 0);

        break;
    } else {
        throw SQLError.createSQLException("No conversion from " + parameterObj.getClass().getName() + " to Types.BOOLEAN possible.",
                SQLError.SQL_STATE_ILLEGAL_ARGUMENT, getExceptionInterceptor());
    }

How to repeat:
Call PreparedStatement.setObject(i, "false", Types.BOOLEAN)

or

String parameterObj = "false";
boolean result = "true".equalsIgnoreCase((String) parameterObj) || !"0".equalsIgnoreCase((String) parameterObj);

Suggested fix:
"true".equalsIgnoreCase((String) parameterObj) || !("false".equalsIgnoreCase((String) parameterObj) || "0".equalsIgnoreCase((String) parameterObj))

[18 Feb 2020 8:35] Alexander Soklakov

Hi Bjoern,

Thanks for finding this! Verified as described in both c/J 5.1 and 8.0.

[21 Apr 2020 20:48] Daniel So

Posted by developer:
 
Added the following entry to the Connector/J 8.0.21 and 5.1.49 changelogs: 

"When trying to set an integer parameter (say, i) for a PreparedStatement using the method PreparedStatement.setObject(i, "false", Types.BOOLEAN), i was set to 1 instead of 0."

[23 Apr 2020 20:15] Daniel So

Posted by developer:
 
Corrected the changelog entry to the following: 

"When trying to set a parameter for a PreparedStatement using the method PreparedStatement.setObject(parameterIndex, "false", Types.BOOLEAN), the value was set to true instead of false."