[Dbix-class] Inserting binary data without corrupting (was: Re: Monkey-patching around a MySQL/UTF8 bug)

[Sam Kington]

On 26 Feb 2015, at 18:14, David Cantrell <david at cantrell.org.uk> wrote:
> On Thu, Feb 26, 2015 at 04:55:10PM +0100, Peter Rabbitson wrote:
[...]
>> Also (as noted in that RT) - this will badly 
>> break BLOB operations.
> 
> Good point. That prompted me to look over our code. We had two BLOB
> fields, one of which can be replaced with a TEXT. The other really does
> contain binary data, but it seems that it's getting corrupted anyway
> even without my monkey-patch. Yuck.

The problem appeared to be encoding issues between DBI and mysql. Delving into DBIx::Class::Storage::DBI:_dbh_execute I found that we were correctly passing a byte-encoded string from DBIx::Class, but it ended up corrupted in the database:

>   DB<11> x $sql                                                                 
> 0  'INSERT INTO customer_static_file ( bytes, customer_id, date_created, date_updated, filename, user_id) VALUES ( ?, ?, ?, ?, ?, ? )'
>   DB<12> x $bind                                                                
> 0  ARRAY(0x122bf2b8)
>    0  ARRAY(0x122bef70)
>       0  HASH(0x122780a8)
>          'dbic_colname' => 'bytes'
>          'sqlt_datatype' => 'LONGBLOB'
>       1  "?PNG\cM\cJ\cZ\cJ??Stuff🝖"

DBI appears to know that this is a binary column, but that's not helping.

The solution I arrived at after some googling was as follows:

> __PACKAGE__->load_components('FilterColumn');
> __PACKAGE__->filter_column(
>     'bytes',
>     {
>         filter_from_storage => sub { pop },
>         filter_to_storage => sub {
>             my ($resultset, $binary_data) = @_;
>             my $encoded_string = sprintf(
>                 "x'%s'",
>                 join('',
>                     map { sprintf('%02x', ord($_)) } split('', $binary_data))
>             );
>             return \$encoded_string;
>         },
>     }
> );

which uses MySQL's preferred way of encoding binary data on the command line.

Is this the best way of handling this, or is there a simpler or purer way I've overlooked?

Sam
-- 
Website: http://www.illuminated.co.uk/