SONAR-3876 Backslash characters are duplicated on PostgreSQL 9.1

12 files changed, 49 insertions, 35 deletions

diff --git a/pom.xml b/pom.xml
index 3b26e406fc6..c3b410ecbd0 100644
--- a/pom.xml
+++ b/pom.xml
@@ -803,8 +803,8 @@
       <dependency>
         <groupId>postgresql</groupId>
         <artifactId>postgresql</artifactId>
-        <!-- do not upgrade to version 9.0-801.jdbc4, not compatible with Java 5 -->
-        <version>9.0-801.jdbc3</version>
+        <!-- do not upgrade to version 9.1-901.jdbc4, not compatible with Java 5 -->
+        <version>9.1-901.jdbc3</version>
       </dependency>
       <dependency>
         <groupId>net.sourceforge.jtds</groupId>
diff --git a/sonar-core/src/main/java/org/sonar/core/persistence/DefaultDatabase.java b/sonar-core/src/main/java/org/sonar/core/persistence/DefaultDatabase.java
index ae05c0b10d3..54744b10b6f 100644
--- a/sonar-core/src/main/java/org/sonar/core/persistence/DefaultDatabase.java
+++ b/sonar-core/src/main/java/org/sonar/core/persistence/DefaultDatabase.java
@@ -112,15 +112,9 @@ public class DefaultDatabase implements Database {
 
   private void initDatasource() throws Exception {// NOSONAR this exception is thrown by BasicDataSourceFactory
     // but it's correctly caught by start()
-
     LOG.info("Create JDBC datasource to url " + properties.getProperty(DatabaseProperties.PROP_URL, DEFAULT_URL));
     datasource = (BasicDataSource) BasicDataSourceFactory.createDataSource(extractCommonsDbcpProperties(properties));
-
-    String initStatement = dialect.getConnectionInitStatement(getSchema());
-    if (StringUtils.isNotBlank(initStatement)) {
-      datasource.setConnectionInitSqls(Arrays.asList(initStatement));
-    }
-
+    datasource.setConnectionInitSqls(dialect.getConnectionInitStatements(getSchema()));
     datasource.setValidationQuery(dialect.getValidationQuery());
   }
 
diff --git a/sonar-core/src/main/java/org/sonar/core/persistence/dialect/AbstractDialect.java b/sonar-core/src/main/java/org/sonar/core/persistence/dialect/AbstractDialect.java
index 0efe69d12b5..469784ca892 100644
--- a/sonar-core/src/main/java/org/sonar/core/persistence/dialect/AbstractDialect.java
+++ b/sonar-core/src/main/java/org/sonar/core/persistence/dialect/AbstractDialect.java
@@ -19,6 +19,9 @@
  */
 package org.sonar.core.persistence.dialect;
 
+import java.util.Collections;
+import java.util.List;
+
 /**
  * @since 3.2
  */
@@ -63,4 +66,8 @@ abstract class AbstractDialect implements Dialect {
   public final String getValidationQuery() {
     return validationQuery;
   }
+
+  public List<String> getConnectionInitStatements(String schema) {
+    return Collections.emptyList();
+  }
 }
diff --git a/sonar-core/src/main/java/org/sonar/core/persistence/dialect/Dialect.java b/sonar-core/src/main/java/org/sonar/core/persistence/dialect/Dialect.java
index 0994f3dcf4f..ca78184e096 100644
--- a/sonar-core/src/main/java/org/sonar/core/persistence/dialect/Dialect.java
+++ b/sonar-core/src/main/java/org/sonar/core/persistence/dialect/Dialect.java
@@ -19,6 +19,8 @@
  */
 package org.sonar.core.persistence.dialect;
 
+import java.util.List;
+
 /**
  * @since 1.12
  */
@@ -52,7 +54,7 @@ public interface Dialect {
    */
   String getDefaultDriverClassName();
 
-  String getConnectionInitStatement(String schema);
+  List<String> getConnectionInitStatements(String schema);
 
   /**
    * @since 2.14
diff --git a/sonar-core/src/main/java/org/sonar/core/persistence/dialect/H2.java b/sonar-core/src/main/java/org/sonar/core/persistence/dialect/H2.java
index 0c80c7dc445..721c3517488 100644
--- a/sonar-core/src/main/java/org/sonar/core/persistence/dialect/H2.java
+++ b/sonar-core/src/main/java/org/sonar/core/persistence/dialect/H2.java
@@ -41,7 +41,4 @@ public class H2 extends AbstractDialect {
     return StringUtils.startsWithIgnoreCase(jdbcConnectionURL, "jdbc:h2:");
   }
 
-  public String getConnectionInitStatement(String schema) {
-    return null;
-  }
 }
diff --git a/sonar-core/src/main/java/org/sonar/core/persistence/dialect/MsSql.java b/sonar-core/src/main/java/org/sonar/core/persistence/dialect/MsSql.java
index a7b30451e3f..ce93a5034f0 100644
--- a/sonar-core/src/main/java/org/sonar/core/persistence/dialect/MsSql.java
+++ b/sonar-core/src/main/java/org/sonar/core/persistence/dialect/MsSql.java
@@ -61,9 +61,5 @@ public class MsSql extends AbstractDialect {
       }
     }
   }
-
-  public String getConnectionInitStatement(String schema) {
-    return null;
-  }
 }
 
diff --git a/sonar-core/src/main/java/org/sonar/core/persistence/dialect/MySql.java b/sonar-core/src/main/java/org/sonar/core/persistence/dialect/MySql.java
index 1e0d86d73e2..ff20d9272d4 100644
--- a/sonar-core/src/main/java/org/sonar/core/persistence/dialect/MySql.java
+++ b/sonar-core/src/main/java/org/sonar/core/persistence/dialect/MySql.java
@@ -53,8 +53,4 @@ public class MySql extends AbstractDialect {
       registerColumnType(Types.BLOB, "blob");
     }
   }
-
-  public String getConnectionInitStatement(String schema) {
-    return null;
-  }
 }
diff --git a/sonar-core/src/main/java/org/sonar/core/persistence/dialect/Oracle.java b/sonar-core/src/main/java/org/sonar/core/persistence/dialect/Oracle.java
index ce21be04c52..9d66cae54d6 100644
--- a/sonar-core/src/main/java/org/sonar/core/persistence/dialect/Oracle.java
+++ b/sonar-core/src/main/java/org/sonar/core/persistence/dialect/Oracle.java
@@ -19,11 +19,13 @@
  */
 package org.sonar.core.persistence.dialect;
 
+import com.google.common.collect.Lists;
 import org.apache.commons.lang.StringUtils;
 import org.hibernate.dialect.Oracle10gDialect;
 import org.sonar.api.database.DatabaseProperties;
 
 import java.sql.Types;
+import java.util.List;
 
 /**
  * @since 1.12
@@ -58,10 +60,12 @@ public class Oracle extends AbstractDialect {
     }
   }
 
-  public String getConnectionInitStatement(String schema) {
+  @Override
+  public List<String> getConnectionInitStatements(String schema) {
+    List<String> statements = Lists.newArrayList();
     if (StringUtils.isNotBlank(schema)) {
-      return "ALTER SESSION SET CURRENT_SCHEMA = \"" + schema + "\"";
+      statements.add("ALTER SESSION SET CURRENT_SCHEMA = \"" + schema + "\"");
     }
-    return null;
+    return statements;
   }
 }
diff --git a/sonar-core/src/main/java/org/sonar/core/persistence/dialect/PostgreSql.java b/sonar-core/src/main/java/org/sonar/core/persistence/dialect/PostgreSql.java
index 0db4659e653..51719490553 100644
--- a/sonar-core/src/main/java/org/sonar/core/persistence/dialect/PostgreSql.java
+++ b/sonar-core/src/main/java/org/sonar/core/persistence/dialect/PostgreSql.java
@@ -19,10 +19,12 @@
  */
 package org.sonar.core.persistence.dialect;
 
+import com.google.common.collect.Lists;
 import org.apache.commons.lang.StringUtils;
 import org.hibernate.dialect.PostgreSQLDialect;
 
 import java.sql.Types;
+import java.util.List;
 
 /**
  * @since 1.12
@@ -55,10 +57,12 @@ public class PostgreSql extends AbstractDialect {
     }
   }
 
-  public String getConnectionInitStatement(String schema) {
+  @Override
+  public List<String> getConnectionInitStatements(String schema) {
+    List<String> statements = Lists.newArrayList("SET standard_conforming_strings=on", "SET backslash_quote=off");
     if (StringUtils.isNotBlank(schema)) {
-      return "SET SEARCH_PATH TO " + schema;
+      statements.add("SET SEARCH_PATH TO " + schema);
     }
-    return null;
+    return statements;
   }
 }
diff --git a/sonar-core/src/test/java/org/sonar/core/persistence/dialect/OracleTest.java b/sonar-core/src/test/java/org/sonar/core/persistence/dialect/OracleTest.java
index 34d1594f2c5..f39fbbc2b82 100644
--- a/sonar-core/src/test/java/org/sonar/core/persistence/dialect/OracleTest.java
+++ b/sonar-core/src/test/java/org/sonar/core/persistence/dialect/OracleTest.java
@@ -21,6 +21,8 @@ package org.sonar.core.persistence.dialect;
 
 import org.junit.Test;
 
+import java.util.List;
+
 import static org.fest.assertions.Assertions.assertThat;
 
 public class OracleTest {
@@ -38,14 +40,14 @@ public class OracleTest {
    */
   @Test
   public void shouldChangeOracleSchema() {
-    String initStatement = oracle.getConnectionInitStatement("my_schema");
+    List<String> initStatements = oracle.getConnectionInitStatements("my_schema");
 
-    assertThat(initStatement).isEqualTo("ALTER SESSION SET CURRENT_SCHEMA = \"my_schema\"");
+    assertThat(initStatements).containsExactly("ALTER SESSION SET CURRENT_SCHEMA = \"my_schema\"");
   }
 
   @Test
   public void shouldNotChangeOracleSchemaByDefault() {
-    assertThat(oracle.getConnectionInitStatement(null)).isNull();
+    assertThat(oracle.getConnectionInitStatements(null)).isEmpty();
   }
 
   @Test
diff --git a/sonar-core/src/test/java/org/sonar/core/persistence/dialect/PostgreSqlTest.java b/sonar-core/src/test/java/org/sonar/core/persistence/dialect/PostgreSqlTest.java
index dfc35ac88c4..40f067ea710 100644
--- a/sonar-core/src/test/java/org/sonar/core/persistence/dialect/PostgreSqlTest.java
+++ b/sonar-core/src/test/java/org/sonar/core/persistence/dialect/PostgreSqlTest.java
@@ -19,11 +19,12 @@
  */
 package org.sonar.core.persistence.dialect;
 
-import org.hamcrest.core.Is;
+import org.fest.assertions.Condition;
 import org.junit.Test;
 
+import java.util.List;
+
 import static org.fest.assertions.Assertions.assertThat;
-import static org.junit.Assert.assertThat;
 
 public class PostgreSqlTest {
 
@@ -37,14 +38,16 @@ public class PostgreSqlTest {
 
   @Test
   public void should_avoid_conflict_with_other_schemas() {
-    String initStatement = postgreSql.getConnectionInitStatement("my_schema");
+    List<String> initStatements = postgreSql.getConnectionInitStatements("my_schema");
 
-    assertThat(initStatement, Is.is("SET SEARCH_PATH TO my_schema"));
+    assertThat(initStatements).contains("SET SEARCH_PATH TO my_schema");
   }
 
   @Test
   public void shouldNotChangePostgreSearchPathByDefault() {
-    assertThat(postgreSql.getConnectionInitStatement(null)).isNull();
+    for (String statement : postgreSql.getConnectionInitStatements(null)) {
+      assertThat(statement).doesNotMatch("SET SEARCH_PATH .*");
+    }
   }
 
   @Test
diff --git a/sonar-server/src/main/webapp/WEB-INF/gems/gems/activerecord-jdbc-adapter-1.1.3/lib/arjdbc/postgresql/adapter.rb b/sonar-server/src/main/webapp/WEB-INF/gems/gems/activerecord-jdbc-adapter-1.1.3/lib/arjdbc/postgresql/adapter.rb
index 28a42647bc5..2f442f829cc 100644
--- a/sonar-server/src/main/webapp/WEB-INF/gems/gems/activerecord-jdbc-adapter-1.1.3/lib/arjdbc/postgresql/adapter.rb
+++ b/sonar-server/src/main/webapp/WEB-INF/gems/gems/activerecord-jdbc-adapter-1.1.3/lib/arjdbc/postgresql/adapter.rb
@@ -465,6 +465,15 @@ module ::ArJdbc
       end
     end
 
+    #sonar
+    # standard_conforming_strings is forced to true in JDBC connection pool (see org.sonar.core.persistence.dialect.PostgreSql)
+    # so double backslashing must be removed
+    def quote_string(s)
+      s.gsub(/'/, "''") # ' (for ruby-mode)
+    end
+    #/sonar
+
+
     def escape_bytea(s)
       if s
         result = ''