Bug #96053 mysqldump generates invalid insert statements with varbinary columns
Submitted: 2 Jul 2019 7:17 Modified: 13 Apr 2020 16:44
Reporter: Herman Lee Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: mysqldump Command-line Client Severity:S2 (Serious)
Version:8.0.13, 8.0.16, 5.7.26 OS:Any
Assigned to: CPU Architecture:Any
Tags: mysqldump

[2 Jul 2019 7:17] Herman Lee
In 8.0.13, varbinary columns are now tagged with _binary in the mysqldump output. However, the memory allocated for the insert statement (default size of 1024) does not take the length of the '_binary ' string into account. The resulting pointer arithmetic causes mysqldump to reference past the initial buffer size of 1024 before it is realloc'ed, resulting in invalid insert statements being generated in the mysqldump output. No error is reported, but the mysqldump output is corrupted.

How to repeat:
On the mysqld server:

create table t1 (pk int, v1 varchar(255), v2 varchar(255), v3 varchar(255), v4 varchar(128), v5 varchar(64), v6 varchar(16), v7 varchar(16), v8 varchar (8), v9 varbinary(32), v10 varbinary(32));

select '12345678901234567890123456789012345678901234567890' into @s;

select concat(@s, @s, @s, @s, @s) into @s1;

insert into t1 values (1, @s1, @s1, @s1, @s1, @s1, @s1, @s1, @s1, '', 'NULL');

Then generate a mysqldump:

mysqldump --no-defaults -P <port> -h -u root test t1

The resulting insert statement is invalid:

INSERT INTO `t1` VALUES (1,'1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890','1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890','1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890','12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678','1234567890123456789012345678901234567890123456789012345678901234','1234567890123456','1234567890123456','12345678','',_binary 'NUL;

Suggested fix:
Include the length of the '_binary ' string in the realloc() call.
[2 Jul 2019 7:26] MySQL Verification Team
Hello Herman Lee,

Thank you for the report and test case.
Verified as described with 8.0.16 build.

[13 Apr 2020 16:44] Paul Dubois
Posted by developer:
Fixed in 5.7.31, 8.0.21.

Calculations by mysqldump for the length of INSERT statements did not
take into account the _binary character set introducer used for
VARBINARY strings.