Bug #92413 Too Many quotes on binary field using updateRow - preparedStatement
Submitted: 13 Sep 2018 17:30 Modified: 9 Oct 2018 22:34
Reporter: Michael Salvagna Email Updates:
Status: Not a Bug Impact on me:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version:8.0.12 OS:Any
Assigned to: Filipe Silva CPU Architecture:Any
Tags: Binary updateRow quotes

[13 Sep 2018 17:30] Michael Salvagna
Description:
when updating a row in the result set where result set is from a select with a binary field in the where condition, the updateRow operation fails because the binary field is "extra quoted".

example:  where `myTable`.`myBinaryField` <=> 'x'ABC123ABC123ABC1''

How to repeat:
Initial prepared statement select includes a where condition ... where myBinaryField = ?

and table definition has myBinaryField defined as binary(16).  Add another column to be used as the update field for test purposes - say myUpdateField INTEGER.  

execute the select query.  Then access the result set for updating.
statement = aConnection.prepareStatement(SELECT_BY_ID_STATEMENT,
			            ResultSet.TYPE_SCROLL_SENSITIVE, 
			            ResultSet.CONCUR_UPDATABLE);

ResultSet resultSet = statement.executeQuery();

resultSet.first();
resultSet.updateInt(2, 10);
resultSet.updateRow();  <-- error should happen here

Suggested fix:
It seems the clientPreparedStatement is cached, with the binary field in question already escaped as x'ABC123ABC123ABC1'.

Thus when UpdatableResultSet.refreshRow gets to do it's thing, it checks if the field needs to be quoted:

if (this.getMetadata().getFields()[index].getValueNeedsQuoting()) {
                this.refresher.setBytesNoEscape(1, dataFrom);
            } else {
                this.refresher.setBytesNoEscapeNoQuotes(1, dataFrom);
            }

getValueNeedsQuoting does not include BINARY as one of the types that does not need quoting (I guess because usually it DOES need quoting), but in the case of updateRow, having already been "quoted", this will cause the field value to be double quoted.

possible fix ideas:
don't store the binary field as a quoted value in the cached prepared statement (and then remember to binary quote it rather than the usual text quote later on) 
OR 
alter the MySQLTypes test to include BINARY (and probably other) types when determining when to add quotes for an updateRow scenario - as in taking into account the values have been "pre-quoted"
[27 Sep 2018 10:59] Filipe Silva
Hi Michael,

Thank you for your interest in Connector/J and for taking the time to report this issue.

I wasn't able to reproduce this behavior. Can you please provide a simple self-contained test case we could use?

Please also provide some more info about your environment that may be relevant, such as Connection string properties you may be using, mid-layer frameworks, client operating system and Java version, MySQL server version...

Thanks,
[9 Oct 2018 16:05] Michael Salvagna
We just upgraded our DB engine to MySQL 5.6.40 and the issue is gone.  We were previously on MySQL 5.6.34.

If anyone has this similar issue and are on MySQL 5.6.34, try upgrading to at least 5.6.40.

Cheers!
[9 Oct 2018 22:34] Filipe Silva
Hi Michael,

Good to know it works for your.

Thanks,
[26 Feb 2020 17:42] Rahul Phadnis
We are still seeing this with mysql connector j version: 8.0.19. The refresher sql in UpdatableResultSet is double escaping binary data.

For example:
SELECT <field names>
FROM <table name>
WHERE uuid <=> 'x''B949BC73FF074B6299C7A42AC4F496A2'''

Here uuid is BINARY(16) column.

Please note that this works for version: 5.1.44