SONAR-6171 Make sure at server startup that the DB is configured to use the UTF8 charset and to be case-sensitive

5 files changed, 485 insertions, 1 deletions

diff --git a/server/sonar-server/src/main/java/org/sonar/server/db/EmbeddedDatabaseFactory.java b/server/sonar-server/src/main/java/org/sonar/server/db/EmbeddedDatabaseFactory.java
index 52c6aacbaac..25e15e503c1 100644
--- a/server/sonar-server/src/main/java/org/sonar/server/db/EmbeddedDatabaseFactory.java
+++ b/server/sonar-server/src/main/java/org/sonar/server/db/EmbeddedDatabaseFactory.java
@@ -27,6 +27,7 @@ import org.sonar.api.database.DatabaseProperties;
 import static org.apache.commons.lang.StringUtils.startsWith;
 
 public class EmbeddedDatabaseFactory implements Startable {
+  private static final String URL_PREFIX = "jdbc:h2:tcp:";
   private final Settings settings;
   private EmbeddedDatabase embeddedDatabase;
 
@@ -38,7 +39,7 @@ public class EmbeddedDatabaseFactory implements Startable {
   public void start() {
     if (embeddedDatabase == null) {
       String jdbcUrl = settings.getString(DatabaseProperties.PROP_URL);
-      if (startsWith(jdbcUrl, "jdbc:h2:tcp:")) {
+      if (startsWith(jdbcUrl, URL_PREFIX)) {
         embeddedDatabase = getEmbeddedDatabase(settings);
         embeddedDatabase.start();
       }
diff --git a/server/sonar-server/src/main/java/org/sonar/server/platform/platformlevel/PlatformLevel3.java b/server/sonar-server/src/main/java/org/sonar/server/platform/platformlevel/PlatformLevel3.java
index 2aa3f701823..6b614a41c9d 100644
--- a/server/sonar-server/src/main/java/org/sonar/server/platform/platformlevel/PlatformLevel3.java
+++ b/server/sonar-server/src/main/java/org/sonar/server/platform/platformlevel/PlatformLevel3.java
@@ -21,6 +21,7 @@ package org.sonar.server.platform.platformlevel;
 
 import org.sonar.api.utils.UriReader;
 import org.sonar.core.util.DefaultHttpDownloader;
+import org.sonar.db.CollationChecker;
 import org.sonar.server.platform.PersistentSettings;
 import org.sonar.server.platform.ServerIdGenerator;
 import org.sonar.server.startup.ServerMetadataPersister;
@@ -33,6 +34,7 @@ public class PlatformLevel3 extends PlatformLevel {
   @Override
   protected void configureLevel() {
     add(
+      CollationChecker.class,
       PersistentSettings.class,
       ServerMetadataPersister.class,
       DefaultHttpDownloader.class,
diff --git a/server/sonar-web/src/main/webapp/WEB-INF/web.xml b/server/sonar-web/src/main/webapp/WEB-INF/web.xml
index 4bcacb4effd..5810000c72a 100644
--- a/server/sonar-web/src/main/webapp/WEB-INF/web.xml
+++ b/server/sonar-web/src/main/webapp/WEB-INF/web.xml
@@ -30,6 +30,14 @@
     <param-name>jruby.rack.logging</param-name>
     <param-value>slf4j</param-value>
   </context-param>
+  <context-param>
+    <param-name>jruby.rack.error</param-name>
+    <param-value>true</param-value>
+  </context-param>
+  <context-param>
+    <param-name>jruby.rack.exception</param-name>
+    <param-value>true</param-value>
+  </context-param>
 
   <filter>
     <filter-name>ServletFilters</filter-name>
diff --git a/sonar-db/src/main/java/org/sonar/db/CollationChecker.java b/sonar-db/src/main/java/org/sonar/db/CollationChecker.java
new file mode 100644
index 00000000000..b09d9233c3b
--- /dev/null
+++ b/sonar-db/src/main/java/org/sonar/db/CollationChecker.java
@@ -0,0 +1,241 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2016 SonarSource SA
+ * mailto:contact AT sonarsource DOT com
+ *
+ * This program is free software; you can redistribute it and/or
+ * modify it under the terms of the GNU Lesser General Public
+ * License as published by the Free Software Foundation; either
+ * version 3 of the License, or (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
+ * Lesser General Public License for more details.
+ *
+ * You should have received a copy of the GNU Lesser General Public License
+ * along with this program; if not, write to the Free Software Foundation,
+ * Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
+ */
+package org.sonar.db;
+
+import com.google.common.annotations.VisibleForTesting;
+import com.google.common.base.Joiner;
+import java.sql.Connection;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.util.ArrayList;
+import java.util.List;
+import javax.annotation.CheckForNull;
+import org.apache.commons.lang.StringUtils;
+import org.picocontainer.Startable;
+import org.sonar.api.utils.MessageException;
+import org.sonar.api.utils.log.Loggers;
+import org.sonar.db.dialect.H2;
+import org.sonar.db.dialect.MsSql;
+import org.sonar.db.dialect.MySql;
+import org.sonar.db.dialect.Oracle;
+import org.sonar.db.dialect.PostgreSql;
+
+import static java.lang.String.format;
+import static org.apache.commons.lang.StringUtils.containsIgnoreCase;
+import static org.apache.commons.lang.StringUtils.endsWithIgnoreCase;
+
+/**
+ * SONAR-6171
+ * Check that database has UTF8 character set and case-sensitive collation.
+ * As obviously tables must be checked after being created, this component
+ * must not be executed at the same time as {@link DatabaseChecker}.
+ */
+public class CollationChecker implements Startable {
+
+  private static final String UTF8 = "utf8";
+  private final Database db;
+  private final StatementExecutor statementExecutor;
+
+  public CollationChecker(Database db) {
+    this(db, new StatementExecutor());
+  }
+
+  @VisibleForTesting
+  CollationChecker(Database db, StatementExecutor statementExecutor) {
+    this.db = db;
+    this.statementExecutor = statementExecutor;
+  }
+
+  @Override
+  public void start() {
+    try {
+      Loggers.get(getClass()).info("Verify database collation");
+      check();
+    } catch (SQLException e) {
+      throw new IllegalStateException(e);
+    }
+  }
+
+  @Override
+  public void stop() {
+    // nothing to do
+  }
+
+  private void check() throws SQLException {
+    try (Connection connection = db.getDataSource().getConnection()) {
+      switch (db.getDialect().getId()) {
+        case H2.ID:
+          // nothing to check
+          break;
+        case Oracle.ID:
+          checkOracle(connection);
+          break;
+        case PostgreSql.ID:
+          checkPostgreSql(connection);
+          break;
+        case MySql.ID:
+          checkMySql(connection);
+          break;
+        case MsSql.ID:
+          checkMsSql(connection);
+          break;
+        default:
+          throw new IllegalArgumentException("Database not supported: " + db.getDialect().getId());
+      }
+    }
+  }
+
+  /**
+   * Oracle does not allow to override character set on tables. Only global charset is verified.
+   */
+  private void checkOracle(Connection connection) throws SQLException {
+    String charset = selectSingleCell(connection, "select value from nls_database_parameters where parameter='NLS_CHARACTERSET'");
+    String sort = selectSingleCell(connection, "select value from nls_database_parameters where parameter='NLS_SORT'");
+    if (!containsIgnoreCase(charset, UTF8) || !"BINARY".equalsIgnoreCase(sort)) {
+      throw MessageException.of(format("Oracle must be have UTF8 charset and BINARY sort. NLS_CHARACTERSET is %s and NLS_SORT is %s.", charset, sort));
+    }
+  }
+
+  /**
+   * PostgreSQL does not support case-insensitive collations. Only character set must be verified.
+   */
+  private void checkPostgreSql(Connection connection) throws SQLException {
+    // Character set is defined globally and can be overridden on each column.
+    // This request returns all VARCHAR columns. Collation may be empty.
+    // Examples:
+    // issues | key | ''
+    // projects | name | utf8
+    List<String[]> rows = select(connection, "select table_name, column_name, collation_name " +
+      "from information_schema.columns " +
+      "where table_schema='public' " +
+      "and udt_name='varchar' " +
+      "order by table_name, column_name", 3);
+    boolean mustCheckGlobalCollation = false;
+    List<String> errors = new ArrayList<>();
+    for (String[] row : rows) {
+      if (StringUtils.isBlank(row[2])) {
+        mustCheckGlobalCollation = true;
+      } else if (!containsIgnoreCase(row[2], UTF8)) {
+        errors.add(format("%s.%s", row[0], row[1]));
+      }
+    }
+
+    if (mustCheckGlobalCollation) {
+      String charset = selectSingleCell(connection, "SELECT pg_encoding_to_char(encoding) FROM pg_database WHERE datname = current_database()");
+      if (!containsIgnoreCase(charset, UTF8)) {
+        throw MessageException.of(format("Database charset is %s. It must be UTF8.", charset));
+      }
+    }
+    if (!errors.isEmpty()) {
+      throw MessageException.of(format("Database columns [%s] must have UTF8 charset.", Joiner.on(", ").join(errors)));
+    }
+  }
+
+  /**
+   * Check VARCHAR columns
+   */
+  private void checkMySql(Connection connection) throws SQLException {
+    // All VARCHAR columns are returned. No need to check database general collation.
+    // Example of row:
+    // issues | kee | utf8 | utf8_bin
+    List<String[]> rows = select(connection,
+      "SELECT table_name, column_name, character_set_name, collation_name " +
+        "FROM INFORMATION_SCHEMA.columns " +
+        "WHERE table_schema=database() and character_set_name is not null and collation_name is not null", 4 /* columns */);
+    List<String> errors = new ArrayList<>();
+    for (String[] row : rows) {
+      if (!containsIgnoreCase(row[2], UTF8) || endsWithIgnoreCase(row[3], "_ci")) {
+        errors.add(format("%s.%s", row[0], row[1]));
+      }
+    }
+    if (!errors.isEmpty()) {
+      throw MessageException.of(format("UTF8 charset and case-sensitive collation are required for database columns [%s]", Joiner.on(", ").join(errors)));
+    }
+  }
+
+  private void checkMsSql(Connection connection) throws SQLException {
+    // All VARCHAR columns are returned. No need to check database general collation.
+    // Example of row:
+    // issues | kee | Latin1_General_CS_AS
+    List<String[]> rows = select(connection,
+      "SELECT table_name, column_name, collation_name " +
+        "FROM [INFORMATION_SCHEMA].[COLUMNS] " +
+        "WHERE collation_name is not null " +
+        "ORDER BY table_name,column_name", 3 /* columns */);
+    List<String> errors = new ArrayList<>();
+    for (String[] row : rows) {
+      if (!endsWithIgnoreCase(row[2], "_CS_AS")) {
+        errors.add(row[0] + "." + row[1]);
+      }
+    }
+    if (!errors.isEmpty()) {
+      throw MessageException.of(format("Case-sensitive and accent-sensitive charset (CS_AS) is required for database columns [%s]", Joiner.on(", ").join(errors)));
+    }
+  }
+
+  @CheckForNull
+  private String selectSingleCell(Connection connection, String sql) throws SQLException {
+    String[] cols = selectSingleRow(connection, sql, 1);
+    return cols == null ? null : cols[0];
+  }
+
+  @CheckForNull
+  private String[] selectSingleRow(Connection connection, String sql, int columns) throws SQLException {
+    List<String[]> rows = select(connection, sql, columns);
+    if (rows.isEmpty()) {
+      return null;
+    }
+    if (rows.size() == 1) {
+      return rows.get(0);
+    }
+    throw new IllegalStateException("Expecting only one result for [" + sql + "]");
+  }
+
+  private List<String[]> select(Connection connection, String sql, int columns) throws SQLException {
+    return statementExecutor.executeQuery(connection, sql, columns);
+  }
+
+  @VisibleForTesting
+  static class StatementExecutor {
+    List<String[]> executeQuery(Connection connection, String sql, int columns) throws SQLException {
+      Statement stmt = null;
+      ResultSet rs = null;
+      try {
+        stmt = connection.createStatement();
+        rs = stmt.executeQuery(sql);
+        List<String[]> result = new ArrayList<>();
+        while (rs.next()) {
+          String[] row = new String[columns];
+          for (int i = 0; i < columns; i++) {
+            row[i] = DatabaseUtils.getString(rs, i + 1);
+          }
+          result.add(row);
+        }
+        return result;
+
+      } finally {
+        DatabaseUtils.closeQuietly(rs);
+        DatabaseUtils.closeQuietly(stmt);
+      }
+    }
+  }
+
+}
diff --git a/sonar-db/src/test/java/org/sonar/db/CollationCheckerTest.java b/sonar-db/src/test/java/org/sonar/db/CollationCheckerTest.java
new file mode 100644
index 00000000000..d653ab32cdb
--- /dev/null
+++ b/sonar-db/src/test/java/org/sonar/db/CollationCheckerTest.java
@@ -0,0 +1,232 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2016 SonarSource SA
+ * mailto:contact AT sonarsource DOT com
+ *
+ * This program is free software; you can redistribute it and/or
+ * modify it under the terms of the GNU Lesser General Public
+ * License as published by the Free Software Foundation; either
+ * version 3 of the License, or (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
+ * Lesser General Public License for more details.
+ *
+ * You should have received a copy of the GNU Lesser General Public License
+ * along with this program; if not, write to the Free Software Foundation,
+ * Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
+ */
+package org.sonar.db;
+
+import java.sql.Connection;
+import java.sql.SQLException;
+import java.util.Arrays;
+import java.util.Collections;
+import java.util.List;
+import org.junit.Rule;
+import org.junit.Test;
+import org.junit.rules.ExpectedException;
+import org.mockito.Mockito;
+import org.sonar.api.utils.MessageException;
+import org.sonar.db.dialect.MsSql;
+import org.sonar.db.dialect.MySql;
+import org.sonar.db.dialect.Oracle;
+import org.sonar.db.dialect.PostgreSql;
+
+import static java.util.Arrays.asList;
+import static java.util.Collections.singletonList;
+import static org.mockito.Matchers.any;
+import static org.mockito.Matchers.anyInt;
+import static org.mockito.Matchers.anyString;
+import static org.mockito.Mockito.mock;
+import static org.mockito.Mockito.when;
+
+public class CollationCheckerTest {
+
+  private static final String TABLE_ISSUES = "issues";
+  private static final String TABLE_PROJECTS = "projects";
+  private static final String COLUMN_KEE = "kee";
+  private static final String COLUMN_NAME = "name";
+
+  @Rule
+  public ExpectedException expectedException = ExpectedException.none();
+
+  Database db = mock(Database.class, Mockito.RETURNS_MOCKS);
+  CollationChecker.StatementExecutor statementExecutor = mock(CollationChecker.StatementExecutor.class);
+  CollationChecker underTest = new CollationChecker(db, statementExecutor);
+
+  @Test
+  public void valid_oracle() throws Exception {
+    when(db.getDialect()).thenReturn(new Oracle());
+    answerSql(
+      singletonList(new String[] {"UTF8"}), singletonList(new String[] {"BINARY"}));
+
+    underTest.start();
+  }
+
+  @Test
+  public void support_oracle_al32utf8() throws Exception {
+    when(db.getDialect()).thenReturn(new Oracle());
+    answerSql(
+      singletonList(new String[] {"AL32UTF8"}), singletonList(new String[] {"BINARY"}));
+
+    underTest.start();
+  }
+
+  @Test
+  public void fail_if_oracle_is_not_utf8() throws Exception {
+    when(db.getDialect()).thenReturn(new Oracle());
+    answerSql(
+      singletonList(new String[] {"LATIN"}), singletonList(new String[] {"BINARY"}));
+
+    expectedException.expect(MessageException.class);
+    expectedException.expectMessage("Oracle must be have UTF8 charset and BINARY sort. NLS_CHARACTERSET is LATIN and NLS_SORT is BINARY.");
+
+    underTest.start();
+  }
+
+  @Test
+  public void fail_if_oracle_is_not_case_sensitive() throws Exception {
+    when(db.getDialect()).thenReturn(new Oracle());
+    answerSql(
+      singletonList(new String[] {"UTF8"}), singletonList(new String[] {"LINGUISTIC"}));
+
+    expectedException.expect(MessageException.class);
+    expectedException.expectMessage("Oracle must be have UTF8 charset and BINARY sort. NLS_CHARACTERSET is UTF8 and NLS_SORT is LINGUISTIC.");
+
+    underTest.start();
+  }
+
+  @Test
+  public void fail_if_can_not_get_oracle_charset() throws Exception {
+    when(db.getDialect()).thenReturn(new Oracle());
+    answerSql(Collections.<String[]>emptyList(), Collections.<String[]>emptyList());
+
+    expectedException.expect(MessageException.class);
+
+    underTest.start();
+  }
+
+  @Test
+  public void valid_postgresql() throws Exception {
+    when(db.getDialect()).thenReturn(new PostgreSql());
+    answerSql(asList(
+      new String[] {TABLE_ISSUES, COLUMN_KEE, "utf8"},
+      new String[] {TABLE_PROJECTS, COLUMN_NAME, "utf8"}));
+
+    underTest.start();
+  }
+
+  @Test
+  public void fail_if_postgresql_has_non_utf8_column() throws Exception {
+    when(db.getDialect()).thenReturn(new PostgreSql());
+    answerSql(asList(
+      new String[] {TABLE_ISSUES, COLUMN_KEE, "utf8"},
+      new String[] {TABLE_PROJECTS, COLUMN_KEE, "latin"},
+      new String[] {TABLE_PROJECTS, COLUMN_NAME, "latin"}));
+
+    expectedException.expect(MessageException.class);
+    expectedException.expectMessage("Database columns [projects.kee, projects.name] must have UTF8 charset.");
+
+    underTest.start();
+  }
+
+  @Test
+  public void fail_if_postgresql_has_non_utf8_db() throws Exception {
+    when(db.getDialect()).thenReturn(new PostgreSql());
+    answerSql(
+      // first request to get columns
+      asList(
+        new String[] {TABLE_ISSUES, COLUMN_KEE, "utf8"},
+        new String[] {TABLE_PROJECTS, COLUMN_NAME, "" /* unset -> uses db collation */}),
+
+      // second request to get db collation
+      Arrays.<String[]>asList(new String[] {"latin"}));
+
+    expectedException.expect(MessageException.class);
+    expectedException.expectMessage("Database charset is latin. It must be UTF8.");
+
+    underTest.start();
+  }
+
+  @Test
+  public void valid_postgresql_if_utf8_db() throws Exception {
+    when(db.getDialect()).thenReturn(new PostgreSql());
+    answerSql(
+      // first request to get columns
+      asList(
+        new String[] {TABLE_ISSUES, COLUMN_KEE, "utf8"},
+        new String[] {TABLE_PROJECTS, COLUMN_NAME, "" /* unset -> uses db collation */}),
+
+      // second request to get db collation
+      Arrays.<String[]>asList(new String[] {"utf8"}));
+
+    // no error
+    underTest.start();
+  }
+
+  @Test
+  public void valid_mysql() throws Exception {
+    when(db.getDialect()).thenReturn(new MySql());
+    answerSql(asList(
+      new String[] {TABLE_ISSUES, COLUMN_KEE, "utf8", "utf8_bin"},
+      new String[] {TABLE_PROJECTS, COLUMN_NAME, "utf8", "utf8_bin"}));
+
+    underTest.start();
+  }
+
+  @Test
+  public void fail_if_mysql_is_not_utf8_charset() throws Exception {
+    when(db.getDialect()).thenReturn(new MySql());
+    answerSql(asList(
+      new String[] {TABLE_ISSUES, COLUMN_KEE, "utf8", "utf8_bin"},
+      new String[] {TABLE_PROJECTS, COLUMN_KEE, "latin1", "utf8_bin"},
+      new String[] {TABLE_PROJECTS, COLUMN_NAME, "latin1", "utf8_bin"}));
+
+    expectedException.expect(MessageException.class);
+    expectedException.expectMessage("UTF8 charset and case-sensitive collation are required for database columns [projects.kee, projects.name]");
+
+    underTest.start();
+  }
+
+  @Test
+  public void fail_if_mysql_is_not_case_sensitive() throws Exception {
+    when(db.getDialect()).thenReturn(new MySql());
+    answerSql(asList(
+      new String[] {TABLE_ISSUES, COLUMN_KEE, "utf8", "utf8_bin"},
+      new String[] {TABLE_PROJECTS, COLUMN_NAME, "utf8", "latin1_swedish_ci"}));
+
+    expectedException.expect(MessageException.class);
+
+    underTest.start();
+  }
+
+  @Test
+  public void valid_mssql() throws Exception {
+    when(db.getDialect()).thenReturn(new MsSql());
+    answerSql(asList(
+      new String[] {TABLE_ISSUES, COLUMN_KEE, "Latin1_General_CS_AS"},
+      new String[] {TABLE_PROJECTS, COLUMN_NAME, "Latin1_General_CS_AS"}));
+
+    underTest.start();
+  }
+
+  @Test
+  public void fail_if_mssql_is_not_case_sensitive() throws Exception {
+    when(db.getDialect()).thenReturn(new MsSql());
+    answerSql(asList(
+      new String[] {TABLE_ISSUES, COLUMN_KEE, "Latin1_General_CS_AS"},
+      new String[] {TABLE_PROJECTS, COLUMN_KEE, "Latin1_General_CI_AI"},
+      new String[] {TABLE_PROJECTS, COLUMN_NAME, "Latin1_General_CI_AI"}));
+
+    expectedException.expect(MessageException.class);
+    expectedException.expectMessage("Case-sensitive and accent-sensitive charset (CS_AS) is required for database columns [projects.kee, projects.name]");
+
+    underTest.start();
+  }
+
+  private void answerSql(List<String[]> firstRequest, List<String[]>... otherRequests) throws SQLException {
+    when(statementExecutor.executeQuery(any(Connection.class), anyString(), anyInt())).thenReturn(firstRequest, otherRequests);
+  }
+}