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

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

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

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

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)

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

Hi Tom,

maybe the problem lies in the DBI:ADO driver?

Chris

Hi Tom,

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

Chris

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