Problem escaping %00 with sqlite

First, how to reproduce:

  $ rails example
  $ cd example
  $ ruby script/generate model Attachment data:binary
  $ rake db:migrate
  $ ruby script/console
  >> a = Attachment.create(:data => "\0\0%00\0\0")
  >> a.data
  => "\000\000\000\000\000"

As you can see %00 was happily replaced with \0, destroying my binary data.

I did some digging and found why this happens, but there are too many design decisions that could be involved, so I can't offer a definite fix for this. :( But here's what's going on:

There's type_cast method, that converts string data (that which came from the database, or that which came from user form) to the actual type. In case of :binary it is binary_to_string, which converts %00 and %25 to their character code equivalents. When the data comes from the database (that is for the string "\0\0%00\0\0" database is supposed to have "%00%00%2500%00%00") it makes sense. But we just used create, which went thru write_attribute, which saved our string as it is in @attributes. Now comes the actual create call, or actually attributes_with_quotes, that uses read_attribute, which converts string representation to "actual" representation with type_cast, effectively turning our %00 to \0, which then, in the actual SQL statement is transformed to %00.

The problem here is that read_attribute does not distinguish between binary data that came from the database (and that needs type_cast), and binary data that came from developer via write_attribute (which must not be type_cast'ed). This results in destruction of any binary data that is unlucky enough to have %00 in it.

Such typecasting of binary seems to be a design flaw, and it either needs to convert binary data immediately when it comes from the database (I couldn't spot the exact spot yet, though), eliminating the need to upcast binary data (inside active_record it seems that only sqlite needs binary_to_string anyway). Or there must be a "downcast" when write_attribute is used, but this would lead to serious performance penalties, as it would lead to down/upcasting such binary data multiple times.