SQLite binary column escaping problem with %00 #2407

bobtown opened this Issue Aug 2, 2011 · 9 comments


None yet
4 participants

bobtown commented Aug 2, 2011

(Rails 3.0.9) I cannot get back out the same binary data that I put into SQLite. If I have a table defined in my migration as

create_table :binary do |t|
  t.binary binary_column

And I do the following, I get very confused, as the data before and after a save is different:

b = Binary.new :binary_column => "\x25\x30\x30" #%00
b.binary_column == "\x25\x30\x30" # => false
b.binary_column_before_type_cast == "\x25\x30\x30" # => true

b2 = Binary.last # get b out of the database 
b2.binary_column == "\x25\x30\x30" # => false
b2.binary_column_before_type_cast == "\x25\x30\x30" # => false

I see that the SQLite string_to_binary and binary_to_string are doing things with %00 and nulls, but this is very confusing. Also, I'm not sure how to reliably get my original binary string back out of the database.

bobtown commented Aug 11, 2011

A better example uses both a "%00" and a binary null together, i.e. "%00\x00", which results in "%00%00" getting written SQLite, which results in "\x00\x00" when subsequently read back out. This seems like a destructive operation. I don't have the skillz to thoroughly debug this, but I would have thought that someone else would have run into it before storing binary data that happens to have a "%00" in it. Any ideas?

b = Binary.new :binary_column => "%00\x00"  # => "%00\x00"
b.binary_column                             # => "\x00\x00" ** This is wrong!
b.binary_column_before_type_cast            # => "%00\x00"

b2 = Binary.last                  # get b out of the database 
b2.binary_column                  # => "\x00\x00" ** Also wrong!
b2.binary_column_before_type_cast # => "%00%00" ** Also wrong!
# Where did my data ("%00\x00") go???

bobtown commented Aug 11, 2011

And I got it working by commenting out the guts of SQLiteColumn's string_to_binary and binary_to_string, basically turning it back into the default. Any problems with this? It looks like the SQLite3Adapter's quote method handles the binary insertion just fine, and the DB read brings stuff back as a Ruby string, which is perfect. Any problems with this solution?

cfeist commented Aug 26, 2011

I've noticed this problem as well and have pulled a fix from the old ticketing system. I've also added a test so this shouldn't fail in the future.



isaacsanders commented Apr 28, 2012

@bobtown Is this still an issue?

cfeist commented Apr 30, 2012

This should be fixed by in 3.2 and master by these commits:




isaacsanders commented Apr 30, 2012

Can you close the issue?

cfeist commented Apr 30, 2012

I would, but unfortunately I don't have permissions to close the issue since I didn't open it and I'm not an admin.

But if there are any admins watching, this issue can be closed.


isaacsanders commented Apr 30, 2012

Sry, didn't see that...

@arunagw arunagw closed this Apr 30, 2012

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment