Incorrect escape syntax for postgresql #212

Closed
nanaya opened this Issue Jul 22, 2012 · 2 comments

Projects

None yet

3 participants

@nanaya
nanaya commented Jul 22, 2012

There's change in default setting in pgsql. What's previously considered WARNING is now simply result in incorrect query.

PostgreSQL 8.4:

postgres=# select '\\';
WARNING:  nonstandard use of \\ in a string literal
LINE 1: select '\\';
               ^
HINT:  Use the escape string syntax for backslashes, e.g., E'\\'.
 ?column?
----------
 \
(1 row)

PostgreSQL 9.1:

postgres=> select '\\';
 ?column?
----------
 \\
(1 row)

MRI/ C pg:

irb(main):003:0* User.where(:name => '\\')
  User Load (67.0ms)  SELECT "users".* FROM "users" WHERE "users"."name" = '\'

JRuby/jdbcpostgresql:

irb(main):006:0> User.where(:name => '\\')
  User Load (1.0ms)  SELECT "users".* FROM "users" WHERE "users"."name" = '\\'

Either use E'...' or don't do anything to \:

postgres=> select E'\\';
 ?column?
----------
 \
(1 row)
postgres=> select '\';
 ?column?
----------
 \
(1 row)

The main effect: storing data with value of \ will be stored incorrectly as \\

[edho@sakuno test1]$ rails c
Loading development environment (Rails 3.2.6)
irb(main):001:0> Welp.create(:name => '\\')
  PK and serial sequence (2.0ms)   SELECT attr.attname, seq.relname
 FROM pg_class seq,
 pg_attribute attr,
 pg_depend dep,
 pg_namespace name,
 pg_constraint cons
 WHERE seq.oid = dep.objid
 AND seq.relkind = 'S'
 AND attr.attrelid = dep.refobjid
 AND attr.attnum = dep.refobjsubid
 AND attr.attrelid = cons.conrelid
 AND attr.attnum = cons.conkey[1]
 AND cons.contype = 'p'
 AND dep.refobjid = '"welps"'::regclass

  SQL (1.0ms)  INSERT INTO "welps" ("created_at", "name", "updated_at") VALUES ('2012-07-22 20:16:51.172000', '\\', '2012-07-22 20:16:51.172000') RETURNING "id"
=> #<Welp id: 1, name: "\\", created_at: "2012-07-22 20:16:51", updated_at: "2012-07-22 20:16:51">
irb(main):002:0> Welp.first.name
  Welp Load (3.0ms)  SELECT "welps".* FROM "welps" LIMIT 1
=> "\\\\"
irb(main):003:0> puts Welp.first.name
  Welp Load (1.0ms)  SELECT "welps".* FROM "welps" LIMIT 1
\\
=> nil
irb(main):004:0>

Versions:

[edho@sakuno test1]$ grep -E ' rails|jdbc' Gemfile.lock
    activerecord-jdbc-adapter (1.2.2)
    activerecord-jdbcpostgresql-adapter (1.2.2)
      activerecord-jdbc-adapter (~> 1.2.2)
      jdbc-postgres (>= 9.0, < 9.2)
    jdbc-postgres (9.1.901)
    rails (3.2.6)
  activerecord-jdbcpostgresql-adapter
  rails (= 3.2.6)
[edho@sakuno test1]$ jruby --version
jruby 1.6.7.2 (ruby-1.8.7-p357) (2012-05-01 26e08ba) (OpenJDK Client VM 1.6.0_22) [linux-i386-java]
[edho@sakuno test1]$ cat /etc/redhat-release
Red Hat Enterprise Linux Server release 5.8 (Tikanga)
[edho@sakuno test1]$ uname -a
Linux sakuno.myconan.net 2.6.18-308.11.1.0.1.el5 #1 SMP Tue Jul 10 12:03:58 EDT 2012 i686 i686 i386 GNU/Linux
[edho@sakuno test1]$ rpm -qa|grep postgresql
postgresql91-9.1.4-3PGDG.rhel5
postgresql91-server-9.1.4-3PGDG.rhel5
postgresql91-devel-9.1.4-3PGDG.rhel5
postgresql-libs-8.1.23-5.el5_8
postgresql91-libs-9.1.4-3PGDG.rhel5
postgresql91-contrib-9.1.4-3PGDG.rhel5
@rsim
rsim commented Aug 14, 2012

Got the same issue and currently did workaround and put the following monkey patch in config\initializers\jdbc.rc

if defined?(JRUBY_VERSION)
  require 'arjdbc'

  ArJdbc::PostgreSQL.module_eval do
    def quote_string(string) #:nodoc:
      string.gsub(/'/, "''")
    end
  end
end
@kares
Member
kares commented Jan 16, 2013

fixed on master and latest release

@kares kares closed this Jan 16, 2013
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment