MySQL Bugs: #101242: Cannot use ByteArrayInputStream as arguments in prepared statements anymore

Bug #101242	Cannot use ByteArrayInputStream as arguments in prepared statements anymore
Submitted:	20 Oct 2020 11:56	Modified:	12 Jan 2021 21:55
Reporter:	Martynas Puronas	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	8.0.22	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	regression

Description:
As of 8.0.22 it is no longer possible to call setObject method of PreparedStatement and pass ByteArrayInputStream, because the driver now throws java.sql.SQLException exception ("Invalid argument value: java.io.NotSerializableException"), whereas in the 8.0.21 driver setObject accepted ByteArrayInputStream argument.

This seems like a regression which I think was introduced in https://github.com/mysql/mysql-connector-j/commit/6f8287b2a393ef9b67889d2bb2385ef863a7e8df as that commit refactored the setObject method of AbstractQueryBindings class.

How to repeat:
This code works when using 8.0.21 version of the driver, but throws an exception with 8.0.22.

import java.io.ByteArrayInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class Main {
    public static void main(String[] args) throws Exception {
        Connection connection = DriverManager.getConnection(
                "jdbc:mysql://localhost/?user=root&password=my-secret-pw"
        );

        connection.createStatement().execute("CREATE DATABASE IF NOT EXISTS test");
        connection.createStatement().execute("USE test");
        connection.createStatement().execute("CREATE TABLE IF NOT EXISTS my_table (my_column blob NOT NULL)");
        connection.createStatement().execute("DELETE FROM my_table");

        PreparedStatement statement = connection.prepareStatement("INSERT INTO my_table(my_column) VALUES (?)");
        statement.setObject(1, new ByteArrayInputStream("test".getBytes()));
        statement.execute();

        ResultSet rs = connection.createStatement().executeQuery("SELECT * FROM my_table");

        rs.next();

        System.out.println("Retrieved from database: " + new String(rs.getBytes("my_column")));
    }
}

Hello Martynas,

Thank you for the report and feedback.

regards,
Umesh

I just ran into this myself with a FileInputStream. My code works with 8.0.15 but fails in 8.0.22 because of this issue.

The ticket is currently ranked as "non-critical"; however, I would consider it a higher priority since it will prevent anyone who uses BLOBs from upgrading to 8.0.22+.

Specifically, the code fails on (what is currently) line 230:

MysqlType defaultMysqlType = DEFAULT_MYSQL_TYPES.get(parameterObj.getClass());

The previous version used instanceof instead of getClass(). As currently written, this code will always fail - since InputStream is abstract, the value returned by getClass() for parameterObj can never actually be InputStream and will never produce a valid lookup from the type map.

Posted by developer:
 
Added the following entry to the Connector/J 8.0.23 changelog: 

"Using the setObject() method to set a ByteArrayInputStream instance for a PreparedStatement resulted in a SQLException. "