Bug #28203 mysql client + null byte

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:
.

Thank you for the report.

Verified as described. All versions are affected.

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?

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 �

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)

The patch replaces a NUL byte with \0 in --raw mode, and a space in all other cases.

Queued to 5.1-bugteam and mysql-pe trees.

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)

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)

Noted in 5.1.38, 5.4.4 changelogs.

The mysql client inconsistently handled NUL bytes in column data in
various output formats.

Noted in 5.4.2 changelog because next 5.4 version will be 5.4.2 and not 5.4.4.

Ignore previous comment about 5.4.2.

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)

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)

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)

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)

The 5.4 fix has been pushed to 5.4.2.