ActiveRecord: update fails when field content inlcudes a single qoute (SQLServer)

rubyonrails-talk
1

Hello,

I received the following error when updating a field in an activerecord which contains an single quote "'"

ActiveRecord::StatementInvalid (DBI::DatabaseError: Execute     OLE error code:80040E14 in Microsoft OLE DB Provider for SQL Server       Öffnendes Anführungszeichen vor der Zeichenfolge '70dea99f72a57e4c3c0e35079cd9a4e1de03b4df WHERE [ID] = 957'.

This is primaly caused by a field content which contains: "Hugo's housewifes"

Anbyody out there who can give me a tip?

Thanks! Chris

2

what is the command you use? because it should quote it correctly automatically, actually.

3

How are you updating your record? Could you give us the actual ruby code you are using? ActiveRecord should already escape quotes in queries.

Tom

4

Hi Tom,

Background: I have a table whith user credentionals. Because I want to use "act_as_authenticated" I create the method "encrypt_all" to encrypt all the logins. The update went well if there are no single quotes in the field "firma1" The database is SQL-Server 2000

the following code raises the exception:

def encrypt_all     @users = User.find(:all)     @users.each {|item|       item.password = item.login       item.encrypt_password       item.update       }     render :nothing => true   end

Stacktrace:

   [4;35;1mUser Update (0.000000) [0m [0mDBI::DatabaseError: Execute OLE error code:80040E14 in Microsoft OLE DB Provider for SQL Server Öffnendes Anführungszeichen vor der Zeichenfolge '865afaaaf8af8fff2b887e443a63759d7753fb93 WHERE [ID] = 957'. HRESULT error code:0x80020009 Ausnahmefehler aufgetreten.: UPDATE partner SET [Ort] = 'Alt-Puppy ', [Firma2] = NULL, [Eintritt] = NULL, [salt] = '6ca32eca729295d97c88e447885eca6172248ea3', [Firma1] = 'Bäller''s Corner ', [modifiedby] = NULL, [Country] = 'D ', [lastmodified] = NULL, [Niederlassung_ID] = 1, [created] = '20070608 14:19:42', [RGPartnernr] = 100082, [extid] = NULL, [Active] = 1, [Postfach] = NULL, [createdby] = NULL, [Strasse] = 'Wulner Weg 77 ', [login] = 'DE-97727 ', [PLZ] = '29727 ', [email] = NULL, [crypted_password] = '865afaaaf8af8fff2b887e443a63759d7753fb93' WHERE [ID] = 957 [0m

ActiveRecord::StatementInvalid (DBI::DatabaseError: Execute     OLE error code:80040E14 in Microsoft OLE DB Provider for SQL Server       Öffnendes Anführungszeichen vor der Zeichenfolge '865afaaaf8af8fff2b887e443a63759d7753fb93 WHERE [ID] = 957'.     HRESULT error code:0x80020009       Ausnahmefehler aufgetreten.: UPDATE partner SET [Ort] = 'Alt- Puppy ', [Firma2] = NULL, [Eintritt] = NULL, [salt] = '6ca32eca729295d97c88e447885eca6172248ea3', [Firma1] = 'Bäller''s Corner ', [modifiedby] = NULL, [Country] = 'D ', [lastmodified] = NULL, [Niederlassung_ID] = 1, [created] = '20070608 14:19:42', [RGPartnernr] = 100082, [extid] = NULL, [Active] = 1, [Postfach] = NULL, [createdby] = NULL, [Strasse] = 'Wulner Weg 77 ', [login] = 'DE-97727 ', [PLZ] = '29727 ', [email] = NULL, [crypted_password] = '865afaaaf8af8fff2b887e443a63759d7753fb93' WHERE [ID] = 957):

    q:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.3/lib/ active_record/connection_adapters/abstract_adapter.rb:128:in `log'     q:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.3/lib/ active_record/connection_adapters/sqlserver_adapter.rb:318:in `execute'     q:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.3/lib/ active_record/connection_adapters/sqlserver_adapter.rb:301:in `update'     q:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.3/lib/ active_record/base.rb:1796:in `update_without_lock'     q:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.3/lib/ active_record/locking/optimistic.rb:60:in `update_without_callbacks'     q:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.3/lib/ active_record/callbacks.rb:267:in `update_without_timestamps'     q:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.3/lib/ active_record/timestamp.rb:48:in `update'     /app/controllers/account_controller.rb:17:in `encrypt_all'     /app/controllers/account_controller.rb:14:in `each'     /app/controllers/account_controller.rb:14:in `encrypt_all'     q:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.13.3/lib/ action_controller/base.rb:1095:in `send'     q:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.13.3/lib/ action_controller/base.rb:1095:in `perform_action_without_filters'     q:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.13.3/lib/ action_controller/filters.rb:632:in `call_filter'     q:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.13.3/lib/ action_controller/filters.rb:619:in `perform_action_without_benchmark'     q:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.13.3/lib/ action_controller/benchmarking.rb:66:in `perform_action_without_rescue'     q:/ruby/lib/ruby/1.8/benchmark.rb:293:in `measure'     q:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.13.3/lib/ action_controller/benchmarking.rb:66:in `perform_action_without_rescue'     q:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.13.3/lib/ action_controller/rescue.rb:83:in `perform_action'     q:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.13.3/lib/ action_controller/base.rb:430:in `send'     q:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.13.3/lib/ action_controller/base.rb:430:in `process_without_filters'     q:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.13.3/lib/ action_controller/filters.rb:624:in `process_without_session_management_support'     q:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.13.3/lib/ action_controller/session_management.rb:114:in `process'     q:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.13.3/lib/ action_controller/base.rb:330:in `process'     q:/ruby/lib/ruby/gems/1.8/gems/rails-1.2.3/lib/dispatcher.rb:41:in `dispatch'     q:/ruby/lib/ruby/gems/1.8/gems/mongrel-1.0.1-mswin32/lib/mongrel/ rails.rb:78:in `process'     q:/ruby/lib/ruby/1.8/thread.rb:135:in `synchronize'     q:/ruby/lib/ruby/gems/1.8/gems/mongrel-1.0.1-mswin32/lib/mongrel/ rails.rb:76:in `process'     q:/ruby/lib/ruby/gems/1.8/gems/mongrel-1.0.1-mswin32/lib/ mongrel.rb:618:in `process_client'     q:/ruby/lib/ruby/gems/1.8/gems/mongrel-1.0.1-mswin32/lib/ mongrel.rb:617:in `each'     q:/ruby/lib/ruby/gems/1.8/gems/mongrel-1.0.1-mswin32/lib/ mongrel.rb:617:in `process_client'     q:/ruby/lib/ruby/gems/1.8/gems/mongrel-1.0.1-mswin32/lib/ mongrel.rb:736:in `run'     q:/ruby/lib/ruby/gems/1.8/gems/mongrel-1.0.1-mswin32/lib/ mongrel.rb:736:in `initialize'     q:/ruby/lib/ruby/gems/1.8/gems/mongrel-1.0.1-mswin32/lib/ mongrel.rb:736:in `new'     q:/ruby/lib/ruby/gems/1.8/gems/mongrel-1.0.1-mswin32/lib/ mongrel.rb:736:in `run'     q:/ruby/lib/ruby/gems/1.8/gems/mongrel-1.0.1-mswin32/lib/ mongrel.rb:720:in `initialize'     q:/ruby/lib/ruby/gems/1.8/gems/mongrel-1.0.1-mswin32/lib/ mongrel.rb:720:in `new'     q:/ruby/lib/ruby/gems/1.8/gems/mongrel-1.0.1-mswin32/lib/ mongrel.rb:720:in `run'     q:/ruby/lib/ruby/gems/1.8/gems/mongrel-1.0.1-mswin32/lib/mongrel/ configurator.rb:271:in `run'     q:/ruby/lib/ruby/gems/1.8/gems/mongrel-1.0.1-mswin32/lib/mongrel/ configurator.rb:270:in `each'     q:/ruby/lib/ruby/gems/1.8/gems/mongrel-1.0.1-mswin32/lib/mongrel/ configurator.rb:270:in `run'     q:/ruby/lib/ruby/gems/1.8/gems/mongrel-1.0.1-mswin32/bin/ mongrel_rails:127:in `run'     q:/ruby/lib/ruby/gems/1.8/gems/mongrel-1.0.1-mswin32/lib/mongrel/ command.rb:211:in `run'     q:/ruby/lib/ruby/gems/1.8/gems/mongrel-1.0.1-mswin32/bin/ mongrel_rails:243     q:/ruby/lib/ruby/gems/1.8/gems/activesupport-1.4.2/lib/ active_support/dependencies.rb:488:in `load'     q:/ruby/lib/ruby/gems/1.8/gems/activesupport-1.4.2/lib/ active_support/dependencies.rb:488:in `load'     q:/ruby/lib/ruby/gems/1.8/gems/activesupport-1.4.2/lib/ active_support/dependencies.rb:342:in `new_constants_in'     q:/ruby/lib/ruby/gems/1.8/gems/activesupport-1.4.2/lib/ active_support/dependencies.rb:488:in `load'     q:/ruby/lib/ruby/gems/1.8/gems/rails-1.2.3/lib/commands/servers/ mongrel.rb:60     q:/ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:27:in `gem_original_require'     q:/ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:27:in `require'     q:/ruby/lib/ruby/gems/1.8/gems/activesupport-1.4.2/lib/ active_support/dependencies.rb:495:in `require'     q:/ruby/lib/ruby/gems/1.8/gems/activesupport-1.4.2/lib/ active_support/dependencies.rb:342:in `new_constants_in'     q:/ruby/lib/ruby/gems/1.8/gems/activesupport-1.4.2/lib/ active_support/dependencies.rb:495:in `require'     q:/ruby/lib/ruby/gems/1.8/gems/rails-1.2.3/lib/commands/server.rb: 39     q:/ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:27:in `gem_original_require'     q:/ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:27:in `require'     ./script/server:3     -e:4:in `load'     -e:4

Rendering q:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.13.3/lib/ action_controller/templates/rescues/layout.rhtml (500 Internal Error)

5

Strange. From a quick glance, it looks to me as though the quote in Müller's Corner has been correctly escaped. Using SQL Query Analyzer or similar, can you update using something like:

UPDATE partner SET [Firma1] = 'Müller''s Corner' WHERE [ID] = 957

If that fails, then I'm at a little bit of a loss.

Tom

6

Hi Tom,

maybe the problem lies in the DBI:ADO driver?

Chris

7

Hi Tom,

the update executed in the SQL Query Analyser works .... any additional ideas?

Chris

8

Only idea I have is to try using the ODBC adapter instead. I've had other strange problems using DBI:ADO and use ODBC exclusively myself.

Tom