Bug #28203 mysql client + null byte
Submitted: 2 May 2007 14:50 Modified: 6 Aug 2009 23:43
Reporter: Sergei Golubchik Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S3 (Non-critical)
Version: OS:Any
Assigned to: Jim Winstead CPU Architecture:Any
Triage: Triaged: D2 (Serious)

[2 May 2007 14:50] Sergei Golubchik
Description:
From: Michalis Pappas
To: internals@lists.mysql.com
Subject: mysql client + null bytes
Date: Tue, 17 Apr 2007 13:25:31 +0300 (EEST)
List-Archive: http://lists.mysql.com/internals/34533

Bug#16859 involves the handling of null bytes by the mysql client.

The bugfix provided worked around the problem by substituting spaces for
null bytes (Commit 3452).

However, the problem persists, since:

1. The patch provided only fixed the issue for the case of semicolon
delimited queries (ie ouput produced by print_table_data() only)

2. Mysql client still handles null bytes in a different manner per output
mode.

Consider the following case:

mysql> create table table1 (col1 varchar(255));

mysql> insert into table1 values ('a\0a');

mysql> select * from table1;
+------+
| col1 |
+------+
| a a  |
+------+

mysql> select * from table1\G
*************************** 1. row ***************************
col1: a

mysql> select * from table1;
<TABLE BORDER=1><TR><TH>col1</TH></TR><TR><TD>a\0a</TD></TR></TABLE>

mysql> select * from table1;
<?xml version="1.0"?>

<resultset statement="select * from table1;"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <row>
        <field name="col1">a</field>
  </row>
</resultset>

So, in conclusion mysql client presents null bytes as follows:

table -> prints space for null
vertical -> prints up to the null byte
html -> prints \0 for null
xml -> prints up to the null byte

Note that the same behavior applies to binary columns too.

So, my questions are:

Which one of the above is the intended behaviour?

And more importantly, in which column types are null bytes legally contained?

Regards,

Michalis

How to repeat:
.
[2 May 2007 16:23] Sveta Smirnova
Thank you for the report.

Verified as described. All versions are affected.
[8 May 2007 13:35] Michalis Pappas
In case someone wanted to write a patch, how should the client represent null bytes so that it maintains a consistent behaviour?

And also, should null bytes be contained in character columns too?
[10 May 2007 16:07] Sergei Golubchik
I think in vertical mode we should do exactly the same as in the horizontal mode (that is, replace with a space). In hmtl and xml modes, I assume, we should print &#00;
[15 Jul 2009 0:04] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/78693

2908 Jim Winstead	2009-07-14
      The handling of NUL bytes in column data in the various output formats
      supported by the mysql client was inconsistent. (Bug #28203)
[15 Jul 2009 0:06] Jim Winstead
The patch replaces a NUL byte with \0 in --raw mode, and a space in all other cases.
[31 Jul 2009 1:56] Jim Winstead
Queued to 5.1-bugteam and mysql-pe trees.
[4 Aug 2009 19:52] Bugs System
Pushed into 5.4.4-alpha (revid:alik@sun.com-20090804194615-h40sa098mx4z49qg) (version source revid:kristofer.pettersson@sun.com-20090731070848-zd38c7u5xaofh8hl) (merge vers: 5.4.4-alpha) (pib:11)
[4 Aug 2009 20:45] Bugs System
Pushed into 5.1.38 (revid:davi.arnaut@sun.com-20090804204317-ggodqkik7de6nfpz) (version source revid:davi.arnaut@sun.com-20090804204317-ggodqkik7de6nfpz) (merge vers: 5.1.38) (pib:11)
[6 Aug 2009 23:43] Paul Dubois
Noted in 5.1.38, 5.4.4 changelogs.

The mysql client inconsistently handled NUL bytes in column data in
various output formats.
[12 Aug 2009 23:01] Paul Dubois
Noted in 5.4.2 changelog because next 5.4 version will be 5.4.2 and not 5.4.4.
[15 Aug 2009 2:18] Paul Dubois
Ignore previous comment about 5.4.2.
[1 Oct 2009 5:59] Bugs System
Pushed into 5.1.39-ndb-6.3.28 (revid:jonas@mysql.com-20091001055605-ap2kiaarr7p40mmv) (version source revid:jonas@mysql.com-20091001055605-ap2kiaarr7p40mmv) (merge vers: 5.1.39-ndb-6.3.28) (pib:11)
[1 Oct 2009 7:25] Bugs System
Pushed into 5.1.39-ndb-7.0.9 (revid:jonas@mysql.com-20091001072547-kv17uu06hfjhgjay) (version source revid:jonas@mysql.com-20091001071652-irejtnumzbpsbgk2) (merge vers: 5.1.39-ndb-7.0.9) (pib:11)
[1 Oct 2009 13:25] Bugs System
Pushed into 5.1.39-ndb-7.1.0 (revid:jonas@mysql.com-20091001123013-g9ob2tsyctpw6zs0) (version source revid:jonas@mysql.com-20091001123013-g9ob2tsyctpw6zs0) (merge vers: 5.1.39-ndb-7.1.0) (pib:11)
[5 Oct 2009 10:50] Bugs System
Pushed into 5.1.39-ndb-6.2.19 (revid:jonas@mysql.com-20091005103850-dwij2dojwpvf5hi6) (version source revid:jonas@mysql.com-20090930185117-bhud4ek1y0hsj1nv) (merge vers: 5.1.39-ndb-6.2.19) (pib:11)
[9 Oct 2009 1:29] Paul Dubois
The 5.4 fix has been pushed to 5.4.2.