Trailing Backslash Problem & Fix for Rails and SQLServer 2005

I came across a problem on project where trying to create a new record with a string attribute that ends in \ (backslash) was causing an OLE error to pop up through the DBI driver.

For example, if you had a model with a string attribute called name and you tried to do Model.create! :name => 'foo\\' you would get the following error:

 DBI::DatabaseError: Execute OLE error code: 80040E14 in Microsoft OLE DB Provider for SQL Server Incorrect syntax near '\'. HRESULT error code:0x80020009 Exception occured.: INSERT INTO completables ([project_id], [type], [description], [completion]) VALUES(2, 'Deliverable', 'bar\', 0) 

This happens with Rails 1.2.3 running on Windows Server 2003 talking to SQLServer 2005 using ruby-dbi 0.0.23 and version 0.1 of the ADO driver.

Normally SQLServer allows backslashes anywhere in a string. It uses a single quote as an escape for a single quote. But for some reason a backslash at the end of a string is treated differently.

The following bit of code put into environment.rb will replace backslashes with calls to CHAR. 92 is the ASCII value of backslash.

1 2 3 4 5 6 7 8 9 

module ActiveRecord   module ConnectionAdapters     class SQLServerAdapter       def quote_string(string)         string.gsub(/\'/, "''").gsub(/\\/, "' + CHAR + '")       end     end   end end