PostgreSQL Adapter incorrectly escaping backslashes #247

Closed
pjungwir opened this Issue Oct 10, 2012 · 5 comments

Comments

Projects
None yet
4 participants

I'm getting the following behavior on Rails 3.2.8 under JRuby 1.6.7.2 with activerecord-jdbcpostgresql-adapter 1.2.2 (connecting to a Postgres 9.1.4 database):

> m = Model.first
> m.name = 'Paul \u00f6 J'    # note the single quotes
 => "Paul \\u00f6 J"
> m.save!
 (1.0ms) UPDATE "models" SET "name" = 'Paul \\u00f6 J', "updated_at" = '2012-10-10 21:06:36.429000' WHERE "users"."id" = 2
> m.reload
> m.name
 => "Paul \\\\u00f6 J"

This looks like an escaping error either in ActiveRecord or the database adapter. Note that it happens on UPDATE, not on SELECT.

Changed src/java/arjdbc/jdbc/RubyJdbcConnection.java to try to see what's going on:

@JRubyMethod
public IRubyObject execute(final ThreadContext context, final IRubyObject sql) {
    return (IRubyObject) withConnectionAndRetry(context, new SQLBlock() {
        public Object call(Connection c) throws SQLException {
            Statement stmt = null;
            System.out.println(sql);
            String query = rubyApi.convertToRubyString(sql).getUnicodeValue();
            System.out.println(query);

The SQL string is already incorrectly escaped when it gets here. So perhaps this is an ActiveRecord problem. Or AR & this adapter should make sure to set Postgres's standard_conforming_strings variable to false:

standard_conforming_strings (boolean)

This controls whether ordinary string literals ('...') treat backslashes literally, as specified in the SQL standard.
Beginning in PostgreSQL 9.1, the default is on (prior releases defaulted to off). Applications can check this
parameter to determine how string literals will be processed. The presence of this parameter can also be taken as
an indication that the escape string syntax (E'...') is supported. Escape string syntax (Section 4.1.2.2) should be
used if an application desires backslashes to be treated as escape characters.

(from the Postgres docs)

Looks like this is a fairly new change to Postgres's default string treatment.

I've created a pull request that fixes this issue:

#248

Thank God! I've been tearing my hear out for two days over this, and I've finally narrowed it down to PG. (Using Rails 3.2.8 and PG JDBC adapter). This has made it impossible for me to store a serialized regexp in AR.

yah, this is a nasty one - @nicksieger could we get a gem release?

Owner

kares commented Jan 13, 2013

this had been fixed already , see #248 and #250 - also 1.2.5 is out although with a few issues, next release coming ...

@kares kares closed this Jan 13, 2013

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment