MySQL Bugs: #5658: displaying a varchar containing char(0) truncate rest of data

Bug #5658	displaying a varchar containing char(0) truncate rest of data
Submitted:	20 Sep 2004 5:03	Modified:	1 Dec 2004 14:24
Reporter:	Sorin Florea	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Query Browser	Severity:	S2 (Serious)
Version:	1.0.7 beta	OS:	Windows (Windows 2000 Professional)
Assigned to:	Michael G. Zinner	CPU Architecture:	Any

Description:
Trying to view or export an encrypted varchar results in string being truncated.
Found out that the ascii character 0 is NULL and rest of string is ignored when displayed.
Data cannot be copied with full integrity.

How to repeat:
Try the following queries:

select char(72,0,72);
select length(char(72,0,72));

The ascii value 72 stands for 'H' and 0 truncates the rest of the string (null terninated string)
The second query's is 3 so it seems that the data is consistent inside the database but when viewed or exported, strings are truncated in this way.

Try :
create table t(a varchar(10));

then

set @val=char(72,0,72);
insert into t values(@val);
set @val=(select a from t limit 1);
insert into t values(@val);

and

select a from t;
select length(a) from t;

As you can see, both strings are the same but you can't view them right and when exported (say in HTML), only 'H' will appear.

Suggested fix:
every non-viewable character from the a varchar or any string should be encoded in the source.

ex:
&#72;&#0;&#72;

but &#0 doesnt exist...

This is problematic because a 0 terminates a string in C and we use C libraries. We have introduced a special handling for BLOB fields that ensures that "0" characters are treated correctly. Therefore we do not allow to displaying the text, you will have to use the Hex-viewer or the Image viewer to display the content.

Would it be possible to use a TINYBLOB in your case?

I haven't tried to see if I can deal with this type of data externally from mysql (with php). 

With tinyblob, it works fine in the mysql client. In QueryBrowser, nothing appears.

i have set up a table test with 3 fields:
password1 varchar(45), password2 varchar(45), password3 tinyblob

in password, i put in cleartext strings
in password2, i put in aes_encrypt(password)
in password3, i do the same as above

in the mysql client, i can see all fields with their content, but in query browser, only password and password 2 will show up. Password2 has the same problem as in my original post.
as for password3 (the tinyblob), i can't see anything (with the viewer).

i tried putting a problem string in the tiny blob filed:
update test set password3=concat('G',char(0),'4');
and it does show up in the text viewer as 'G', nothing else, and for the binary viewer, nothing there.

What is weird is that i checked to see why I can only see something in the text viewer (even if its not the right string) only with the 'G\04' string and not with anything else, so i checked to see if there are any other strings starting with a viewable character, and there are. I guess it's because they have undisplayable characters like «%Áø—\/³K?éµ²ûÈ.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html