jruby/activerecord-jdbc-adapter — pull request #250 by GUI (2012-10-23 03:45:04+00:00)
Title: Fix PostgreSQL backslash escaping to account for standard_conforming_strings

activerecord-jdbcpostgresql-adapter is incorrectly quoting strings containing backslashes in Postgres 9.1+ (or any server with PostgreSQL's `standard_conforming_strings` setting enabled by default). This is also reported in #247, with another possible solution in pull request #248. The reason for this alternative patch is to try to better match ActiveRecord's default PostgreSQL adapter, and to not require that `standard_conforming_strings` be turned off (this patch should work regardless of the setting).

Basically, ActiveRecord's default PostgreSQL adapter explicitly enables `standard_conforming_strings` (if available) for all connections (versus disabling it in #248). The default adapter then quotes backslashes differently depending on the connection's current setting (this is handled deeper within the pg gem's C hooks). This pull request replicates the behavior of both enabling `standard_conforming_strings` by default, and quoting strings appropriately based on the current connection's `standard_conforming_strings` setting.

So this pull request:

- Enables `standard_conforming_strings` by default (like default ActiveRecord adapter).
- Implements postgres specific `quote_string` method to account for the connection's current `standard_conforming_strings` setting.
- Semi-related: The existing `supports_standard_conforming_strings?` method was broken, so I fixed that.

Tests should be included.

For even more detail of how default ActiveRecord makes this work with the `pg` gem versus what the jdbc adapter is doing: ActiveRecord's default PostgreSQL adapter defers [quote_string](https://github.com/rails/rails/blob/v3.2.8/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb#L472) to PG's [escape_string](http://deveiate.org/code/pg/PG/Connection.html#method-i-escape_string) method, with in turn uses libpq's [PQescapeStringConn](http://doxygen.postgresql.org/fe-exec_8c.html#a180fd1cd78e0cf999bb0265a31cd89d8). PQescapeStringConn takes into account the current connection's `standard_conforming_strings` setting when quoting. When using the default ActiveRecord adapter with the `pg` gem, note the different behavior of the `quote_string` method based on the current settings:

```
irb(main):003:0> ActiveRecord::Base.connection.quote_string("testing \\w backslash")
=> "testing \\w backslash"

irb(main):004:0> ActiveRecord::Base.connection.execute("SET standard_conforming_strings=off")

irb(main):005:0> ActiveRecord::Base.connection.quote_string("testing \\w backslash")
=> "testing \\\\w backslash"
```

However, activerecord-jdbcpostgresql-adapter currently defers quoting to ActiveRecord's generic [quote_string](https://github.com/rails/rails/blob/v3.2.8/activerecord/lib/active_record/connection_adapters/abstract/quoting.rb#L78) method which will always quote backslashes, and doesn't take into account the server's `standard_conforming_strings` setting.