How to get SELECT to output escaped "\0" character

Question

When I run query #1:

SELECT * FROM $TABLE INTO OUTFILE $FILE CHARACTER SET utf8 FIELDS ESCAPED BY '\\\\' TERMINATED BY '|';

There is this one field value that is outputted as:

blah\0

I am trying to get identical output without using INTO OUTFILE.

query #2 with Perl code:

$query = $db->prepare("SELECT * FROM $TABLE");
$query->execute;
open(FILE_OUT, ">$FILE");

However, the same column above is outputted as

blah

So the \0 character (ASCII NUL) is outputted differently. How do I modify query #2 to output blah\0?

The modification could be in either MySQL query or Perl. The column's Collation is utf8_general_ci. I've tried using CONVERT($column using utf8) but that still displayed blah, and I'm not sure how I would apply it to every column of every table when outputting.

Update:

This Perl code worked in escaping the NUL character.

$row =~ s/\0/\\0/g;

But there are many other MySQL special characters, is there a way to escape them all at once instead of handling them one by one?

You'll find a lot of related info in [this post](http://stackoverflow.com/questions/881194/how-do-i-escape-special-characters-in-mysql). To print NUL you can use [pack](http://perldoc.perl.org/functions/pack.html). See tutorial [perlpacktut](http://perldoc.perl.org/perlpacktut.html). Finally, you can always double backslashes with a regex for the whole string but that may not cut it. — zdim, Oct 07 '16 at 03:58

score 1 · Answer 1 · answered Oct 07 '16 at 13:06

The quote method of the database handle should be able to quote any special characters:

my $sth = $dbh->prepare("select '\0'");

$sth->execute;
while (my $row = $sth->fetch) {
        my ($col) = @$row;
        print "[$col] is quoted as ", $dbh->quote($col), "\n";
}

On my version of MySQL this prints:

[] is quoted as '\0'

If I pipe that through hexdump -C we get

00000000  5b 00 5d 20 69 73 20 71  75 6f 74 65 64 20 61 73  |[.] is quoted as|
00000010  20 27 5c 30 27 0a                                 | '\0'.|
00000016

How to get SELECT to output escaped "\0" character

1 Answers1