This project is archived and is in readonly mode.

#6606 new
kempgen

ActiveRecord does not escape NUL bytes for SQLite3

Reported by kempgen | March 22nd, 2011 @ 08:50 PM

Summary

ActiveRecord does not escape NUL (\x00) bytes in :string fields for the SQLite3 adapter.
(Rails 3.0.5)

How to reproduce

rails new foo
cd foo
rails g model Book title:string
rake db:migrate
rails c
b = Book.new( :title => "-A-\x00-B-" )
b.save

Output:

ActiveRecord::StatementInvalid: SQLite3::SQLException: unrecognized token: "'-A-": INSERT INTO "books" ("title", "created_at", "updated_at") VALUES ('-A--B-', ..., ...)
    from .../activerecord-3.0.5/lib/active_record/connection_adapters/abstract_adapter.rb:207:in `rescue in log'
    from .../activerecord-3.0.5/lib/active_record/connection_adapters/abstract_adapter.rb:199:in `log'
    from .../activerecord-3.0.5/lib/active_record/connection_adapters/sqlite_adapter.rb:135:in `execute'
    from .../activerecord-3.0.5/lib/active_record/connection_adapters/abstract/database_statements.rb:282:in `insert_sql'
    from .../activerecord-3.0.5/lib/active_record/connection_adapters/sqlite_adapter.rb:149:in `insert_sql'
    from .../activerecord-3.0.5/lib/active_record/connection_adapters/abstract/database_statements.rb:44:in `insert'
    from .../activerecord-3.0.5/lib/active_record/connection_adapters/abstract/query_cache.rb:16:in `insert'
    from .../arel-2.0.9/lib/arel/select_manager.rb:217:in `insert'
    from .../activerecord-3.0.5/lib/active_record/relation.rb:14:in `insert'
    from .../activerecord-3.0.5/lib/active_record/persistence.rb:270:in `create'
    from .../activerecord-3.0.5/lib/active_record/timestamp.rb:47:in `create'
    from .../activerecord-3.0.5/lib/active_record/callbacks.rb:281:in `block in create'
    from .../activesupport-3.0.5/lib/active_support/callbacks.rb:413:in `_run_create_callbacks'
    from .../activerecord-3.0.5/lib/active_record/callbacks.rb:281:in `create'
    from .../activerecord-3.0.5/lib/active_record/persistence.rb:246:in `create_or_update'
    from .../activerecord-3.0.5/lib/active_record/callbacks.rb:277:in `block in create_or_update'
... 3 levels...
    from .../activerecord-3.0.5/lib/active_record/validations.rb:43:in `save'
    from .../activerecord-3.0.5/lib/active_record/attribute_methods/dirty.rb:21:in `save'
    from .../activerecord-3.0.5/lib/active_record/transactions.rb:240:in `block (2 levels) in save'
    from .../activerecord-3.0.5/lib/active_record/transactions.rb:292:in `block in with_transaction_returning_status'
    from .../activerecord-3.0.5/lib/active_record/connection_adapters/abstract/database_statements.rb:139:in `transaction'
    from .../activerecord-3.0.5/lib/active_record/transactions.rb:207:in `transaction'
    from .../activerecord-3.0.5/lib/active_record/transactions.rb:290:in `with_transaction_returning_status'
    from .../activerecord-3.0.5/lib/active_record/transactions.rb:240:in `block in save'
    from .../activerecord-3.0.5/lib/active_record/transactions.rb:251:in `rollback_active_record_state!'
    from .../activerecord-3.0.5/lib/active_record/transactions.rb:239:in `save'

How to fix

SQLite does not support NUL bytes in string fields.

However, ActiveRecord should never send unescaped values to the database.

\0 bytes could be escaped as \\0 (and \ as \\) when writing and have to be unescaped when reading from the adapter.

Alternatively (Python App Engine SDK does this): "Nulls in a string (\0) are replaced with the escape sequence \1\1, while \1 bytes are replaced with \1\2. ... This encoding is reversible, but ensures the strings still sort in the same order as they did previously."

The escaping/encoding can easily be done in ActiveRecord::ConnectionAdapters::SQLite3Adapter.quote() but the string will have to be unescaped/decoded somewhere.

No comments found

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile »

<h2 style="font-size: 14px">Tickets have moved to Github</h2>

The new ticket tracker is available at <a href="https://github.com/rails/rails/issues">https://github.com/rails/rails/issues</a>

People watching this ticket

Pages