[Dbix-class] Inserting binary data without corrupting

[Peter Rabbitson]

On 02/26/2015 11:38 PM, Sam Kington wrote:
> 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?
>

Interesting. This is one way of handling it, yes (btw `unpack 'H*', 
$binary data` would be much faster). If this definitely fixes things on 
a combination of a DBD and RDBMS version (you never said what you are 
using) - I can look into making this implicitly handled by the driver 
itself.