Atomic Spin :: Elements of Software

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.

  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