ActiveRecord: update fails when field content inlcudes a sin

farfignugen · June 18, 2007, 4:00pm

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

farfignugen · June 18, 2007, 5:05pm

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

farfignugen · June 18, 2007, 5:08pm

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

farfignugen · June 19, 2007, 9:29am

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-Ruppin
', [Firma2] = NULL, [Eintritt] = NULL, [salt] =
‘6ca32eca729295d97c88e447885eca6172248ea3’, [Firma1] = ‘Mü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-
Ruppin
‘, [Firma2] = NULL, [Eintritt] = NULL, [salt] =
‘6ca32eca729295d97c88e447885eca6172248ea3’, [Firma1] = ‘Müller’‘s
Radhaus ‘, [modifiedby] = NULL,
[Country] = ‘D ‘, [lastmodified] = NULL, [Niederlassung_ID] = 1,
[created] = ‘20070608 14:19:42’, [RGPartnernr] = 305527, [extid] =
NULL, [Active] = 1, [Postfach] = NULL, [createdby] = NULL, [Strasse] =
‘Wulkower Chaussee
3c
‘, [login] = ‘DE-305527 ‘, [PLZ] = ‘16827 ‘, [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:inexecute’
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:inupdate_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:inupdate_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:inencrypt_all’
/app/controllers/account_controller.rb:14:in each' /app/controllers/account_controller.rb:14:inencrypt_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:inperform_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:inperform_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:inmeasure’
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:inperform_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:inprocess_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:inprocess’
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:indispatch’
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:insynchronize’
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:inprocess_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:inprocess_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:ininitialize’
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:inrun’
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:innew’
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:inrun’
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:inrun’
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:inrun’
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:inload’
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:inload’
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:inrequire’
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:innew_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:ingem_original_require’
q:/ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:27:in
require' ./script/server:3 -e:4:inload’
-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)

farfignugen · June 19, 2007, 9:55am

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:inexecute’
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:inupdate_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:inupdate_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:inencrypt_all’
/app/controllers/account_controller.rb:14:in each' /app/controllers/account_controller.rb:14:inencrypt_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:inperform_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:inperform_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:inmeasure’
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:inperform_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:inprocess_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:inprocess’
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:indispatch’
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:insynchronize’
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:inprocess_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:inprocess_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:ininitialize’
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:inrun’
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:innew’
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:inrun’
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:inrun’
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:inrun’
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:inload’
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:inload’
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:inrequire’
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:innew_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:ingem_original_require’
q:/ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:27:in
require' ./script/server:3 -e:4:inload’
-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)

farfignugen · June 19, 2007, 2:06pm

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

farfignugen · June 19, 2007, 7:18pm

Hi Tom,

maybe the problem lies in the DBI:ADO driver?

Chris

farfignugen · June 20, 2007, 8:16am

Hi Tom,

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

Chris

farfignugen · June 20, 2007, 12:03pm

On 20/06/07, Chris [email protected] wrote:

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

farfignugen · June 20, 2007, 11:30am

Hi Tom,

switching to ODBC simply solved the problem!
It seems that there are some hassles with the ADO implementation
or the OLEDB driver.

Chris

Tom W. wrote:

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